:Create an account for a self-managed MySQL database and configure binary logging

Scenarios and required permissions

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

1. Log on to the self-managed MySQL database.
2. 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';

3. 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'@'%';

4. Enable binary logging for the self-managed 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 self-managed MySQL database is later than 5.6. 

     2. 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.
   • 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 self-managed 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