Help Center/ Data Replication Service/ User Guide (ME-Abu Dhabi Region)/ FAQs/ Permissions Management/ How Can I Import Users and Permissions from the Source to the Destination Database?
Updated on 2023-02-02 GMT+08:00
View PDF
Share

How Can I Import Users and Permissions from the Source to the Destination Database?

  1. Log in to an ECS that can access the source database.
  2. Run the following command, enter the password as prompted, and press Enter to export the source database users to the users.sql temporary file:

    mysql -h 'host' -u 'user' -p -N $@ -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" > /tmp/users.sql

    host indicates the IP address of the source database and user indicates the username of the source database.

  3. Run the following command to export the authorization information of the users from the source database to the grants.sql file:

    mysql -h 'host' -u 'user' -p -N $@ -e "source /tmp/users.sql" > /tmp/grants.sql

    sed -i 's/$/;/g' /tmp/grants.sql

    host indicates the IP address of the source database and user indicates the username of the source database.

  4. After the preceding command has been executed successfully, open the grants.sql file. Information similar to the following is displayed: 

    -- Grants for root@%
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

-- Grants for testt@%
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'testt'@'%';

-- Grants for debian-sys-maint@localhost 
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' WITH GRANT OPTION;

-- Grants for mysql.session@localhost
GRANT SUPER ON *.* TO 'mysql.session'@'localhost'; 
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';

-- Grants for mysql.sys@localhost
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost'; 
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost'; 
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';

-- Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; 
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

Parent topic: Permissions Management