If your migration task involves incremental data migration between MySQL databases, Data Transmission Service (DTS) checks the binary logging (binlog) configurations of the source database during the precheck. This topic describes how to fix issues detected by the Source Database Binary Logging check item.

Source database binary logging status

This item checks whether binary logging is enabled in the source database. Follow these instructions to fix the issue:

Solution:

  1. Log on to the server where your user-created MySQL database is installed.
  2. Open the my.cnf file in a text editor. In a Linux system, you can run the vim command to edit the file. Edit the text file to include the following parameters.
    log_bin=mysql_bin
    binlog_format=row
    binlog_row_image=full //This parameter is required for MySQL versions later than 5.6
  3. Restart the MySQL process. In a Linux system, run the following commands:
    $mysql_dir/bin/mysqladmin -u root -p shutdown
    $mysql_dir/bin/safe_mysqld &
    Note Replace the installation directory mysql_dir of the MySQL database with the actual MySQL installation directory.
  4. Log on to the DTS console to perform another precheck.

Source database binlog format

This item checks whether the binary log (binlog) format of the source database is ROW. Follow these instructions to fix the issue:

Solution:

  1. Log on to the user-created MySQL database in a MySQL client.
  2. Run the following statement to set the binlog format of the database to ROW.
    set global binlog_format='ROW';
  3. Restart the MySQL process. In a Linux system, run the following commands:
    $mysql_dir/bin/mysqladmin -u root -p shutdown
    $mysql_dir/bin/safe_mysqld &
    Note Replace the installation directory mysql_dir of the MySQL database with the actual MySQL installation directory.
  4. Log on to the DTS console to perform another precheck.

Source database binlog files

This item checks whether binary log (binlog) files of the source database are complete. If this item fails the precheck, it indicates that some binlog files are missing. Follow these instructions to fix the issue:

Solution:

  1. In the Precheck dialog box, click the information icon Note next to Binlog Files in Source Database check item.Binary log file check failed
  2. In the View More dialog box, check the deleted binlog files displayed in the Cause of Failure section. In this example, the binlog file deleted is mysql_bin.000003.
  3. Log on to the user-created MySQL database.
  4. Run the following command on the command line to delete all binary log files before the specified log file.
    Note In this example, the specified log file is the file right after the missing binary log file mysql_bin.000003. Therefore, the binlog_filename is mysql_bin.000004.
    PURGE BINARY LOGS TO '<binlog_filename>';
    Example:
    PURGE BINARY LOGS TO 'mysql_bin.000004';
  5. Log on to the DTS console to perform another precheck.

Parameter binlog_row_image

This item checks whether the binlog_row_image parameter of the source database is set to Full. Before images or after images are not written to the binary log files if the item fails to pass the precheck. The following shows how to troubleshoot the problem.

Solution:

  1. Log on to the user-created MySQL database.
  2. Run the following command on the command line and set binlog_row_image to FULL.
    set global binlog_row_image=FULL;
  3. Log on to the DTS console to perform another precheck.