Help Center/ Data Replication Service/ FAQs/ Permissions Management/ How Can I Import Users and Permissions from the Source to the Destination Database?
Updated on 2023-07-03 GMT+08:00
View PDF

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;

  5. The information displayed in 4 shows all users of the source database and their permissions. Add the required users one by one to the RDS for MySQL database on the current cloud. For details, see the Creating an Account section in the Relational Database Service User Guide.
Parent topic: Permissions Management