how to create an account for a self-managed mysql database and configure binary logging - Data Transmission Service

If the source database for your DTS task is a self-managed MySQL database, you must create a database account and configure binary logging before you configure the task. This step is required to pass the precheck and ensure that the task runs as expected.

Scenarios and permissions

Scenario	Required permissions
The source database 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. The permissions to create databases and tables. These permissions allow DTS to create a database named `test` to advance the binary log position.
The source database for 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. The permissions to create databases and tables. These permissions allow DTS to create a database named `test` to advance the binary log position.
The source database for 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. The permissions to create databases and tables. These permissions allow DTS to create a database named `test` to advance the binary log position.

Impact

This operation requires you to restart the MySQL service. To avoid interrupting your services, perform this operation during off-peak hours.

Procedure

Log on to the self-managed MySQL database.
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 create.
- host: The host from which the account can log on to the database. To allow the account to log on from any host, use a percent sign (%).
- password: The password for the account.
For example, to create an account named dtsmigration with the password Dts123456 that can log on from any host, run the following command.
```
CREATE USER 'dtsmigration'@'%' IDENTIFIED BY 'Dts123456';
```
Grant the required permissions to the account. For more information, see Scenarios and permissions.
- Grant the account permissions on specified databases and tables.
```
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
```
- Grant the account permissions to create databases and tables.
```
GRANT CREATE ON *.* TO 'username'@'host' WITH GRANT OPTION;
```
Note
- privileges: The operation permissions to grant to the account, such as SELECT, INSERT, and UPDATE. To grant all permissions, use ALL.
- databasename: The database name. To grant permissions on all databases, use an asterisk (*).
- tablename: The table name. To grant permissions on all tables, use an asterisk (*).
- username: The account to which you want to grant permissions.
- host: The host from which the account can log on. To allow the account to log on from any host, use a percent sign (%).
- WITH GRANT OPTION: Grants the account permission to run the GRANT command. This parameter is optional.
For example, run the following command to grant the dtsmigration account all permissions on all databases and tables and allow the account to log on from any host.
```
GRANT ALL ON *.* TO 'dtsmigration'@'%';
```

Enable and configure binary logging for the self-managed MySQL database.

Linux commands

Use the vim command to modify the following parameters in the my.cnf configuration file.

log_bin=mysql_bin
binlog_format=row

# For MySQL versions earlier than 8.0, use expire_logs_days to set the retention period of binary logs. The default value is 0, which means that binary logs never expire.
# For MySQL 8.0 and later, use binlog_expire_logs_seconds to set the retention period of binary logs. The default value is 2592000 seconds (30 days).
# If you set the retention period of binary logs to less than 7 days, you can use the following parameters to set the retention period to 7 days or more.
# expire_logs_days=7
# binlog_expire_logs_seconds=604800

# Set this parameter to an integer greater than 1.
server_id=2

# If the version of the self-managed MySQL database is later than 5.6, you must set this parameter.
binlog_row_image=full

# If the self-managed database is deployed in a dual-primary cluster, enable this parameter.
# log_slave_updates=ON

After you modify the file, restart the MySQL process.
```
/etc/init.d/mysqld restart
```

Windows commands

Modify the following parameters in the my.ini configuration file.

log_bin=mysql_bin
binlog_format=row

# For MySQL versions earlier than 8.0, use expire_logs_days to set the retention period of binary logs. The default value is 0, which means that binary logs never expire.
# For MySQL 8.0 and later, use binlog_expire_logs_seconds to set the retention period of binary logs. The default value is 2592000 seconds (30 days).
# If you set the retention period of binary logs to less than 7 days, you can use the following parameters to set the retention period to 7 days or more.
# expire_logs_days=7
# binlog_expire_logs_seconds=604800

# Set this parameter to an integer greater than 1.
server_id=2

# If the version of the self-managed MySQL database is later than 5.6, you must set this parameter.
binlog_row_image=full

# If the self-managed database is deployed in a dual-primary cluster, enable this parameter.
# log_slave_updates=ON

After you modify the file, restart the MySQL service. You can restart the service in the Windows Services Manager or run the following commands:
```
net stop mysql
net start mysql
```