Before you use Data Transmission Service (DTS) to migrate, synchronize, or track data from a user-created 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 user-created MySQL database, and you want to perform incremental data migration . The SELECT permission on the required objects (databases or tables), the REPLICATION SLAVE permission, the REPLICATION CLIENT permission, and the SHOW VIEW permission
The source database of a data synchronization task is a user-created MySQL database. The SELECT permission on the required objects (databases or tables), the REPLICATION SLAVE permission, the REPLICATION CLIENT permission, and the SHOW VIEW permission
The source database of a change tracking task is a user-created MySQL database. The SELECT permission on the required objects (databases or tables), the REPLICATION SLAVE permission, the REPLICATION CLIENT permission, and the SHOW VIEW permission

Impacts

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

  1. Log on to the user-created MySQL database.
  2. Run the following command to create an account for data migration or synchronization in the user-created 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 for the account.
    For example, you can run the following command 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';
  3. Run the following command to grant permissions to the account. For more information, see Scenarios and required permissions.
    GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
    Note
    • privileges: the operations that the account is allowed to perform, such as the SELECT, INSERT, and UPDATE operations. If you set this parameter to ALL, the account is allowed to perform all operations.
    • databasename: the database name. If you set this parameter to an asterisk (*), the account can be used to perform operations on all databases.
    • tablename: the table name. If you set this parameter to an asterisk (*), the account can be used to perform operations on all tables.
    • 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: the permissions that authorize the account to use the GRANT command. This parameter is optional.

    For example, you can run the following command 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'@'%';
  4. Enable binary logging for the user-created MySQL database and set parameters.
    • For Linux systems, perform the following steps:
      1. 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 user-created MySQL database is later than 5.6.
      2. Run the following commands to restart the MySQL process:
        mysql_dir/bin/mysqladmin -u root -p shutdown
        mysql_dir/bin/safe_mysqld &
        Note You must replace mysql_dir with the actual installation directory of MySQL.
    • For Windows systems, perform the following steps:
      1. 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 user-created MySQL database is later than 5.6.
      2. 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