tencent cloud

Overview

NewDTS focuses more on the data content itself during MySQL data migration. It migrates basic tables and views only but not user permission information. This document describes how to migrate user permissions.

In this document, pt-show-grants is used to export user permissions from the source database and import them to the target database.

Preparations

• Install pt-show-grants and learn its basic operations. This document uses pt-show-grants 3.2.0 as an example.
• Authorize the migration account in the source database. The following uses the dts user as an example:

  CREATE USER 'dts'@'%' IDENTIFIED BY 'migration password'; 
  GRANT SELECT ON `mysql`.* TO 'dts'@'%';
  

• Authorize the migration account in the target database. The following uses the dts user as an example:

  CREATE USER 'dts'@'%' IDENTIFIED BY 'migration password';
  GRANT SELECT,CREATE USER,LOCK TABLES,SHOW VIEW,TRIGGER,ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TABLESPACE,CREATE TEMPORARY TABLES,CREATE VIEW,DELETE,DROP,EVENT,EXECUTE, INDEX,INSERT,PROCESS,REFERENCES,RELOAD,UPDATE,SHOW DATABASES on *.* to 'dts'@'%';
  FLUSH PRIVILEGES;
  

• During migration, we recommend you not write sessions other than the migration session into the target database. If you need to access the target database, we recommend you create an account with read-only permissions. This helps avoid the risks of double write which may cause data conflicts and task failures. The following uses the read_only user as an example:

  CREATE USER 'read_only'@'%' IDENTIFIED BY 'migration password';
  GRANT SELECT, SHOW DATABASES, SHOW VIEW on *.* to 'read_only'@'%';
  FLUSH PRIVILEGES;
  

Notes

• If the target database is TencentDB for MySQL, only the following general permissions can be imported, while high-level permissions cannot. This restriction is mainly used to avoid the business risks caused by maloperations of high-privileged users.
  SELECT, CREATE USER, LOCK TABLES, SHOW VIEW, TRIGGER, ALTER, ALTER ROUTINE, CREATE,CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW,DELETE,DROP,EVENT,EXECUTE, INDEX, INSERT, PROCESS, REFERENCES, RELOAD, UPDATE, SHOW DATABASES, REPLICATION CLIENT, and REPLICATION SLAVE.
• The SQL statements generated by pt-show-grants for the source database vary by source MySQL version. Statements generated on versions below 5.7.6 cannot be imported to target databases on 5.7.6 or above.

Directions

1. Run the following command in the source database to export the user account information.
   You can specify --ignore to filter out high-privileged accounts that cannot be imported by MySQL.

   pt-show-grants --user=root --host=<src host or ip> --port=<src port> --user=<user> --password=<password> --ignore 'mysql.sys'@'localhost','mysql.session'@'localhost','mysql.infoschema'@'localhost','root','root'@'localhost' > account.sql
   

2. Import the user account information into the target database.

   mysql -u<user> -p<password> -h<dst ip or host> -P<dst port> -e "source account.sql"
   

   Below are sample SQL statements exported from MySQL 5.7.6 or above:

   -- Grants dumped by pt-show-grants
   -- Dumped from server xxxx via TCP/IP, MySQL 8.0.22-txsql at 2021-09-01 11:28:21
   -- Grants for 'dts'@'%'
   CREATE USER IF NOT EXISTS 'dts'@'%';
   ALTER USER 'dts'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*F2XFE7135318FD1F12CDF7B027506096F223DDD46' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
   GRANT LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO `dts`@`%`;
   GRANT SELECT ON `mysql`.* TO `dts`@`%`;
   

   Below are sample SQL statements exported from MySQL below v5.7.6:

   -- Grants dumped by pt-show-grants
   -- Dumped from server xxxx via TCP/IP, MySQL 5.6.16-log at 2021-09-01 11:33:53
   -- Grants for 'dts'@'%'
   GRANT LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW VIEW ON *.* TO 'dts'@'%' IDENTIFIED BY PASSWORD '*47D7DB84D97A8D7EFD5B3CFA20A7A2433A9E86A4';
   GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'dts'@'%';
   GRANT SELECT ON `mysql`.* TO 'dts'@'%';