What Permissions Should I Have for MySQL Data Transmission?
The permissions required are determined by whether the task is a migration task or synchronization task and whether the MySQL instance is the source database instance or destination database instance.
Data Migration
| Database | Required Permissions | Sample Statement for Granting Permissions |
|---|---|---|
| Source Database | 1. Query permissions on MySQL databases. 2. Query permissions on the databases to be migrated. 3. Certain global permissions. RELOAD LOCK TABLES REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW PROCESS 4. To migrate an entire instance, the query permissions on all databases in the source instance are required. | GRANT SELECT ON mysql. TO 'Account used for migration'@'%'; GRANT SELECT ON Source database. TO 'Account used for migration'@'%'; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, PROCESS ON . TO 'Account used for migration'@'%'; |
| Destination Database | The following 23 global permissions are required: ALTER ALTER ROUTINE CREATE CREATE ROUTINE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETE DROP EVENT EXECUTE INDEX INSERT LOCK TABLES PROCESS REFERENCES RELOAD SELECT SHOW DATABASES SHOW VIEW TRIGGER UPDATE | GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON*.*TO 'Account used for migration'@'%'; |
Data Synchronization
| Database | Required Permissions |
|---|---|
| Source Database | Permissions required for forward synchronization (synchronizing data from the source database instance to the destination database instance): 1. Query permissions on MySQL databases. 2. Query permissions on the databases to be migrated. 3. Certain global permissions. RELOAD LOCK TABLES REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW PROCESS 4. To migrate an entire instance, you must have the query permissions on all databases in the instance. Permissions required for reverse synchronization (synchronizing data from the destination database instance to the source database instance) include the following 23 global permissions: ALTER ALTER ROUTINE CREATE CREATE ROUTINE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETE DROP EVENT EXECUTE INDEX INSERT LOCK TABLES PROCESS REFERENCES RELOAD SELECT SHOW DATABASE SSHOW VIEW TRIGGER UPDATE |
| Destination Database | Permissions required for forward synchronization (synchronizing data from the source database instance to the destination database instance): 1. Query permissions on MySQL databases. 2. Query permissions on the databases to be migrated. 3. Certain global permissions. RELOAD LOCK TABLES REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW PROCESS 4. To migrate an entire instance, you must have the query permissions on all databases in the instance. Permissions required for reverse synchronization (synchronizing data from the destination database instance to the source database instance) include the following 23 global permissions: ALTER ALTER ROUTINE CREATE CREATE ROUTINE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETE DROP EVENT EXECUTE INDEX INSERT LOCK TABLES PROCESS REFERENCES RELOAD SELECT SHOW DATABASES SHOW VIEW TRIGGER UPDATE |
Do I Need Special Permissions for Configuring Scheduled Tasks?
When you are configuring a migration or synchronization task, you can set it to be a scheduled task and set the time point when the task will be started based on your business requirements. Scheduled task configuration does not require special permissions. All eSurfing Cloud accounts that have passed real-name verification can be used to enable scheduled DTS tasks.
Why Does an Error Message Indicating that I Don't Have the Required Permissions Prompt When a DTS Task is Running?
To use DTS to migrate or synchronize data, the account that you use for creating the migration and synchronization task must be granted the required permissions. If the account does not have sufficient permissions, the data transmission fails. To ensure that data can be transmitted as expected, grant the required permissions to the account. For information about the permissions required for data transmission, see:
Migrating Data From a MySQL Database to Another MySQL Database.
Migrating Data From a PostgreSQL Database to Another PostgreSQL Database.
Synchronizing Data From a MySQL Database to Another MySQL Database.