Before you use Data Transmission Service (DTS) to migrate, synchronize, or track data from a self-managed MySQL database, you must create a database account and configure binary logging.
Scenarios and required permissions
Scenario | Required permission |
---|---|
The source database of a data migration task is a self-managed MySQL database, and you want to perform incremental data migration. | The SELECT permission on the objects to be migrated, the REPLICATION CLIENT, REPLICATION SLAVE, and SHOW VIEW permissions, and the permissions to create databases and tables. The permissions allow DTS to create a database named dts to record heartbeat data during migration. |
The source database of a data synchronization task is a self-managed MySQL database. | The SELECT permission on the objects to be synchronized, the REPLICATION CLIENT, REPLICATION SLAVE, and SHOW VIEW permissions, and the permissions to create databases and tables. The permissions allow DTS to create a database named dts to record heartbeat data during synchronization. |
The source database of a change tracking task is a self-managed MySQL database. | The SELECT permission on the objects for change tracking, the REPLICATION CLIENT, REPLICATION SLAVE, and SHOW VIEW permissions, and the permissions to create databases and tables. The permissions allow DTS to create a database named dts to record heartbeat data during change tracking. |
Impact
To perform the operations described in this topic, you must restart the MySQL service. We recommend that you perform the operations during off-peak hours.
Procedure
- Log on to the self-managed MySQL database.
- Execute the following statement to create an account for data migration or synchronization in the self-managed MySQL database:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
Note- username: the account to be created.
- host: the host from which the account can be used to log on to the database. If you set this parameter to a percent sign (%), the account can be used to log on to the database from all hosts.
- password: the password of the account.
For example, you can execute the following statement to create an account named dtsmigration. The password is Dts123456. The account is allowed to log on to the database from all hosts.CREATE USER 'dtsmigration'@'%' IDENTIFIED BY 'Dts123456';
- Execute the following statements to grant permissions to the account. For more information, see Scenarios and required permissions.
- Execute the following statement to grant the account the permissions on the specified databases and tables:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
- Execute the following statement to grant the account the permissions to create databases and tables:
GRANT CREATE ON *.* TO 'username'@'host' WITH GRANT OPTION;
Note- privileges: the operation permissions to be granted to the account, such as SELECT, INSERT, and UPDATE. To grant all permissions to the account, set this parameter to ALL.
- databasename: the name of the self-managed MySQL database. To grant the operation permissions of all databases to the account, set this parameter to an asterisk (*).
- tablename: the name of the table whose data you want to migrate. To grant the operation permissions of all tables to the account, set this parameter to an asterisk (*).
- username: the account to which you want to grant permissions.
- host: the host from which the account is allowed to log on to the database. If you set this parameter to a percent sign (%), the account is allowed to log on to the database from all hosts.
- WITH GRANT OPTION: grants the account the permission to run the GRANT command. This parameter is optional.
For example, you can execute the following statement to grant all permissions on databases and tables to the dtsmigration account, and allow the account to log on to the database from all hosts.
GRANT ALL ON *.* TO 'dtsmigration'@'%';
- Execute the following statement to grant the account the permissions on the specified databases and tables:
- Enable binary logging for the self-managed MySQL database and set parameters.
- For Linux systems, perform the following steps:
- Run the
vim
command and modify the following parameters in the my.cnf configuration file:log_bin=mysql_bin binlog_format=row server_id=2 // Set this parameter to an integer greater than 1. binlog_row_image=full // You must set this parameter if the version of the self-managed MySQL database is later than 5.6.
- Run the following commands to restart the MySQL process:
/etc/init.d/mysqld restart
Note You can run other commands or use other methods to restart MySQL.
- Run the
- For Windows systems, perform the following steps:
- Run the following commands to modify the parameters in the my.cnf configuration file:
log_bin=mysql_bin binlog_format=row server_id=2 // Set this parameter to an integer greater than 1. binlog_row_image=full // You must set this parameter if the version of the self-managed MySQL database is later than 5.6.
- Restart the MySQL service. Note You can restart the MySQL service by using the Windows service manager or by running the following commands:
net stop mysql net start mysql
- Run the following commands to modify the parameters in the my.cnf configuration file:
- For Linux systems, perform the following steps: