The Binlog PreCheck items fail during incremental data migration by using DTS - Data Transmission Service

Overview

When you use DTS to incrementally migrate data, click the corresponding PreCheck items to view the solution if the following PreCheck items related to the Binlog files of the source database fail.

SOURCE database binlogging: Check whether binlogging is enabled in the source database.
SOURCE database Binlog format: Check whether the Binlog format of the source database is ROW.
binlog_row_image of the source database is FULL: If the source database is MySQL 5.6 or later, binlog_row_image must be FULL.
Binlog files in source database: Check whether Binlog files in the source database are deleted.

Description

The following is the repair method when each check item fails.

SOURCE database binlogging

This item checks whether the source database has enabled binlogging. If the binlogging feature fails to be enabled in the source database, troubleshoot the issue by following the steps below.

Log on to the server where the source user-created MySQL database resides.

Modify the MySQL configuration file by referring to the following configuration: my.cnf.

Note: The default path for the my.cn f configuration file is /etc/my.cnf, the site is subject to the actual situation.

log_bin=mysql_bin
binlog_format=row
server_id=2 // is an integer greater than 1. This is to be used as an example.
binlog_row_image=full // if the source database version is MySQL 5.6 or later, you need to configure this parameter.

Run the following command to restart MySQL.
```
[$Mysql_Dir]/bin/mysqladmin -u root -p shutdown
[$Mysql_Dir]/bin/safe_mysqld&
```
Note:[$Mysql_Dir] indicates the MySQL installation path. Replace it with the actual MySQL installation directory.
Log on to the user-created MySQL database. Run the following SQL statement to confirm that the Binlog feature is enabled:
```
show variables like '%log_bin%';
```
The command output is as follows:
Run the DTS PreCheck again.

SOURCE database Binlog format

This item checks whether the Binlog mode of the source database is ROW. If the check fails, the Binlog format of the source database is not ROW, please refer to the following steps to fix.

Log on to the server where the source user-created MySQL database resides.

Modify the MySQL configuration file by referring to the following configuration: my.cnf, will binlog_format modify parameter to row.

Note: The default path for the my.cn f configuration file is /etc/my.cnf, the site is subject to the actual situation.

log_bin=mysql_bin 
binlog_format=row change the binlog format to row.
server_id=2 // is an integer greater than 1. This is to be used as an example. 
binlog_row_image=full // if the source database version is MySQL 5.6 or later, you need to configure this parameter.

Run the following command to restart MySQL.
```
[$Mysql_Dir]/bin/mysqladmin -u root -p shutdown 
[$Mysql_Dir]/bin/safe_mysqld&
```
Note:[$Mysql_Dir] indicates the MySQL installation path. Replace it with the actual MySQL installation directory.
Log on to the user-created MySQL source database. Run the following SQL statement to confirm that the Binlog mode has been changed to ROW.
```
show variables like "%binlog_format%";
```
The command output is as follows:
Run the DTS PreCheck again.

binlog_row_image of the source database is FULL

This item is only applicable to databases of MySQL 5.6 and later versions. It checks the binlog_row_image parameter FULL. If this check item fails, the Binlog file of the source database does not record the full image. In this case, troubleshoot the issue as follows:

Log on to the server where the source user-created MySQL database resides.
Modify the MySQL configuration file by referring to the following configuration: my.cnf, will binlog_row_image modify parameter to full.
Note: The default path for the my.cn f configuration file is /etc/my.cnf, the site is subject to the actual situation.
```
log_bin=mysql_bin
binlog_format=row
server_id=2 // is an integer greater than 1. This is to be used as an example. 
binlog_row_image=full // if the source database version is MySQL 5.6 or later, you need to configure this parameter.
```
Run the following command to restart MySQL.
```
[$Mysql_Dir]/bin/mysqladmin -u root -p shutdown 
[$Mysql_Dir]/bin/safe_mysqld&
```
Note:[$Mysql_Dir] indicates the MySQL installation path. Replace it with the actual MySQL installation directory.
Log on to the MySQL source database again, and run the following SQL statement to confirm binlog_row_image the parameter is FULL.
```
show variables like "%binlog_row_image%";
```
Run the DTS PreCheck again.

Binlog files in source database

This item checks whether the Binlog file of the source database is complete. If this check item fails, the Binlog file of the source database is incomplete. Follow these steps to fix the problem.

In pre-Check dialog box that appears, click confirm to the right of Binlog existence check item for the source database.
In the view details dialog box, view failure Reason to record the missing Binlog files.

Note: the lack of mysql_bin.000003 documents is used as an example. The actual situation on site shall prevail.
Determines the importance of the missing Binlog file and selects the appropriate handling method.
- If the Binlog file is not important, it can be ignored. Log on to the user-created MySQL source database and run the following SQL statement to clear the reported error Binlog and all previous Binlog files. If you are prompted that the missing Binlog file is "mysql_bin.000003", you must clear all Binlog files before "mysql_bin.000004", including "mysql_bin.000003".
```
PURGE BINARY LOGS TO '[$Binlog_Filename]';
```
  Note:[$Binlog_Filename] is the name of the first Binlog file following the error Binlog file. For example, if the Binlog file to be missing is "mysql_bin.000003", [$Binlog_Filename] is "mysql_bin.000004".
- If the Binlog file is important and cannot be ignored, contact the administrator of the user-created MySQL database to restore the Binlog file.
Run the DTS PreCheck again.

Data Transmission Service:The Binlog PreCheck items fail during incremental data migration by using DTS