MySQL binary log precheck for DTS migration-Data Transmission Service(DTS)-阿里云帮助中心

Binary log enablement

Verifies that binary logging is enabled on the source database. If this check fails, resolve the issue as follows.

Solution:

Log on to the server that hosts your self-managed MySQL database.

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

Note

The default path of my.cnf is /etc/my.cnf. Your actual path may differ.

log_bin=mysql_bin
binlog_format=row
server_id=2 # An integer greater than 1. The value is for example purposes only.
binlog_row_image=full # Required if the source database is MySQL 5.6 or later.

Restart the MySQL service:
```
/etc/init.d/mysqld restart
```
Note
Other restart methods may apply depending on your setup.

Log on to your self-managed MySQL source database and run this SQL statement to verify that binary logging is enabled.

show variables like '%log_bin%';

Expected output:

MariaDB [pro1]> show variables like '%log_bin%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| log_bin                          | ON    |
| log_bin_trust_function_creators  | OFF   |
| sql_log_bin                      | ON    |
+----------------------------------+-------+
3 rows in set (0.00 sec)

Run the DTS precheck again.

Binary log format

Verifies that the source database binary logging format is set to ROW. If this check fails, resolve the issue as follows.

Solution:

Log on to the server that hosts your self-managed MySQL database.

In the my.cnf configuration file, set the binlog_format parameter to ROW.

Note

The default path of my.cnf is /etc/my.cnf. Your actual path may differ.

log_bin=mysql_bin
binlog_format=row # Set the binary logging format to row.
server_id=2 # An integer greater than 1. The value is for example purposes only.
binlog_row_image=full # Required if the source database is MySQL 5.6 or later.

Restart the MySQL service:
```
/etc/init.d/mysqld restart
```
Note
Other restart methods may apply depending on your setup.

Log on to your self-managed MySQL source database and run this SQL statement to confirm the binary logging format is ROW.

show variables like "%binlog_format%";

Expected output:

MariaDB [(none)]> show variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

Run the DTS precheck again.

Binary log availability

Verifies that no binary log files have been deleted from the source database. A failure means the binary log sequence is incomplete. If this check fails, resolve the issue as follows.

Solution:

In the Precheck dialog box, click the Info icon next to the binary log file availability check item.
In the View More dialog box, identify the failure cause and note the missing binary log file names.
Assess the missing binary log files and choose an action.
- If the missing binary log file is not critical and its data can be discarded, log on to your self-managed MySQL source database and run this SQL statement to purge all binary log files before the specified file:
```
PURGE BINARY LOGS TO '[$Binlog_Filename]';
```
  Note
  [$Binlog_Filename] is the first binary log file to keep. For example, if mysql_bin.000003 is missing, use mysql_bin.000004. This purges all files before mysql_bin.000004.
- If the missing file is critical, contact your database administrator to restore it.
Run the DTS precheck again.

Binlog_row_image setting

This check applies only to MySQL 5.6 or later. It verifies that the source database binlog_row_image parameter is set to full. A failure means the binary log does not record full row images. If this check fails, resolve the issue as follows.

Solution:

Log on to the server that hosts your self-managed MySQL database.

In the my.cnf configuration file, set the binlog_row_image parameter to full.

Note

The default path of my.cnf is /etc/my.cnf. Your actual path may differ.

log_bin=mysql_bin
binlog_format=row
server_id=2 # An integer greater than 1. The value is for example purposes only.
binlog_row_image=full # Required if the source database is MySQL 5.6 or later.

Restart the MySQL service:
```
/etc/init.d/mysqld restart
```
Note
Other restart methods may apply depending on your setup.
Log on to the source database and run this SQL statement to verify that binlog_row_image is set to FULL.
```
show variables like "binlog_row_image";
```
Run the DTS precheck again.