Before you use DTS to migrate, synchronize, or track data from a user-created MySQL database, you must create a database account and configure binary logging.

Scenarios

  • The source database of a data migration task is a user-created MySQL database, and you want to perform incremental data migration .
  • The source database of a data synchronization task is a user-created MySQL database.
  • The source database of a change tracking task is a user-created MySQL database.

Precautions

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 in the user-created MySQL database. You can use the account for data migration and synchronization.
    CREATE USER 'username'@'host' IDENTIFIED BY 'password';
    Note
    • username: the username of 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 run the following command to create an account named dtsmigration. The password is Dts123456. The account can be used 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:
    GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
    Note
    • privileges: the operations that are granted to the account, such as SELECT, INSERT, and UPDATE. If you set this parameter to ALL, you can use the account 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 can be used to log on to the database. If you set this parameter to a percent sign (%), you can log on to the database from all hosts by using the account.
    • WITH GRANT OPTION: authorizes 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. The command authorizes the account to log on to the database from all hosts.

    GRANT ALL ON *. * TO 'dtsmigration'@'%';
  4. Enable and configure binary logging for the user-created MySQL database.
    • To enable and configure binary logging in a Linux system, follow these 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.
    • To enable and configure binary logging in a Windows system, follow these 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