All Products
Search
Document Center

Data Transmission Service:Precheck on the binary logging of the source database

Last Updated:Oct 23, 2023

Before you start incremental data migration between MySQL databases, Data Transmission Service (DTS) checks the binary logging configurations of the source database during the precheck. This topic describes what DTS checks and how to fix the issue if a check item fails the precheck.

Whether binary logging is enabled for the source database

This item checks whether binary logging is enabled for the source database. If this item fails the precheck, perform the following steps to fix the issue.

Solution:

  1. Log on to the server on which the source self-managed MySQL database is deployed.

  2. Run the vim command to modify the parameters in the my.cnf configuration file.

    Note

    The default path for the my.cnf configuration file is /etc/my.cnf. The path is subject to the actual situation.

    log_bin=mysql_bin
    binlog_format=row
    server_id=2 # Set the server ID to an integer greater than 1. This is an example only for reference. 
    binlog_row_image=full # This parameter is required if the version of the self-managed MySQL database is 5.6 or later. 
  3. Run the following command to restart MySQL:

    /etc/init.d/mysqld restart
    Note

    You can also run other commands or use other methods to restart MySQL.

  4. Log on to the source self-managed MySQL database and execute the following SQL statement to make sure that the binary logging feature is enabled:

    show variables like '%log_bin%';

    The following figure provides an example of the returned results.show

  5. Perform a DTS precheck again.

Binary logging format

This item checks whether the binary logging format of the source database is ROW. If this item fails the precheck, perform the following steps to fix the issue.

Solution:

  1. Log on to the server on which the source self-managed MySQL database is deployed.

  2. Modify the parameters in the my.cnf configuration file. Set the binlog_format parameter to ROW.

    Note

    The default path for the my.cnf configuration file is /etc/my.cnf. The path is subject to the actual situation.

    log_bin=mysql_bin
    binlog_format=row # Set the binary logging format to ROW. 
    server_id=2 # Set the server ID to an integer greater than 1. This is an example only for reference. 
    binlog_row_image=full # This parameter is required if the version of the self-managed MySQL database is 5.6 or later. 
  3. Run the following command to restart MySQL:

    /etc/init.d/mysqld restart
    Note

    You can also run other commands or use other methods to restart MySQL.

  4. Log on to the source self-managed MySQL database and execute the following SQL statement to make sure that the binary logging format is set to ROW:

    show variables like '%log_bin%';

    The following figure provides an example of the returned results.row

  5. Perform a DTS precheck again.

Binary log files in the source database

This item checks whether the binary log files of the source database are complete. If this item fails the precheck, the binary log files of the source database are incomplete. In this case, perform the following steps to fix the issue.

Solution:

  1. In the Precheck dialog box, click the Info icon to the right of the Binlog Files in Source Database check item. Binlog Files in Source Database check failure

  2. In the View More dialog box, check the missing binary log files based on the value of the Cause of Failure parameter. Solution to the Binlog Files in Source Database check failure

  3. Determine the importance of each missing binary log file and select an appropriate handling method.

    • If the binary log file is not important, it can be ignored. Log on to the source self-managed MySQL database and execute the following SQL statement to clear the error binary log file and all previous binary log files:

      PURGE BINARY LOGS TO '[$Binlog_Filename]';
      Note

      [$Binlog_Filename] specifies the name of the first binary log file following the error binary log file. For example, if the missing binary log file is mysql_bin.000003, replace [$Binlog_Filename] with mysql_bin.000004.

    • If the binary log file is important and cannot be ignored, contact the administrator of the self-managed MySQL database to restore the binary log file.

  4. Perform a DTS precheck again.

Whether the binlog_row_image parameter of the source database is set to FULL

This check item is applicable only to databases of MySQL 5.6 or later. It checks whether the binlog_row_image parameter of the source database is set to FULL. If this item fails the precheck, the binary log files of the source database do not record full images. In this case, perform the following steps to fix the issue.

Solution:

  1. Log on to the server on which the source self-managed MySQL database is deployed.

  2. Modify the parameters in the my.cnf configuration file. Set the binlog_format parameter to FULL.

    Note

    The default path for the my.cnf configuration file is /etc/my.cnf. The path is subject to the actual situation.

    log_bin=mysql_bin
    binlog_format=row
    server_id=2 # Set the server ID to an integer greater than 1. This is an example only for reference. 
    binlog_row_image=full # This parameter is required if the version of the self-managed MySQL database is 5.6 or later. 
  3. Run the following command to restart MySQL:

    /etc/init.d/mysqld restart
    Note

    You can also run other commands or use other methods to restart MySQL.

  4. Log on to the source self-managed MySQL database again and execute the following SQL statement to make sure that the binlog_row_image parameter is set to FULL:

    show variables like '%log_bin%';
  5. Perform a DTS precheck again.