Checking Database Parameters
1. Checking Whether the Binlog Feature Is Enabled for Your Source Database
| Reason for Failure | Suggestion |
|---|---|
| The binlog feature is not enabled for the source database. | Set log_bin to ON for mysqld in the configuration file. Enter binlog_format=row Then, restart mysqld. |
2. Checking the Binlog Mode of the Source Database
| Reason for Failure | Suggestion |
|---|---|
| The binlog_row_image variable of the source database is incorrectly configured. | Set log_bin to ON for mysqld in the configuration file. Enter binlog_format=row Then, restart mysqld. |
3. Checking the Binlog Type of the Source Database
| Reason for Failure | Suggestion |
|---|---|
| The binlog_row_image variable of the source database is incorrectly configured. | Set log_bin to ON for mysqld in the configuration file. Enter binlog_row_image=full Then, restart mysqld. |
4. Checking Whether the Source Database Binlog File Is Deleted by Accident
| Reason for Failure | Suggestion |
|---|---|
| The source database binlog file is deleted by accident. | If the file does not matter, you can ignore it. In this case, log in to the source MySQL database and remove the file that causes the failure and all binlog files generated before the file. If the file matters, you cannot ignore it. In this case, contact the admin of the source MySQL database to restore the file. |
5. Checking the Character Set Consistency Between the Source and Destination Databases
| Reason for Failure | Suggestion |
|---|---|
| The character sets of the source and destination databases are inconsistent. | Check whether the character sets of the source and destination databases are consistent: show global variables like "character_set_server"; Run the following command to change a server character set: set global character_set_server=' |
6. Checking the Time Zone Consistency Between the Source and Destination Databases
| Reason for Failure | Suggestion |
|---|---|
| The time zones of the source and destination databases are inconsistent. | Specify the time zone of the source database as the time zone of the destination database: set global time_zone = '<Time zone (For example, +8:00 specifies the time zone eight hours ahead of UTC time)>'; flush privileges; |
7. Checking the Consistency of sql_mode Values
| Reason for Failure | Suggestion |
|---|---|
| The sql_mode values of the source and destination databases are inconsistent. | Log in to the source database to view the value of sql_mode: show global variables like '%sql_mode%'; Log in to the destination database to specify the sql_mode value of the source database as that of the destination database: set global sql_mode=' |
8. Checking the server_id Value of the Source Database
| Reason for Failure | Suggestion |
|---|---|
| Check whether the server_id value of the source database is greater than 1. | Run the following SQL statement to change the server_id value: set global server_id=<my_id>; |
9. Checking the Consistency of lower_case_table_names Values
| Reason for Failure | Suggestion |
|---|---|
| The lower_case_table_names values of the source and destination databases are inconsistent. | Log in to your source and destination databases and check the lower_case_table_names values. Specify the lower_case_table_names value of the source database as that of the destination database for mysqld in the configuration file of the destination database. Then, restart mysqld. |
10. Checking log_slave_updates
| Reason for Failure | Suggestion |
|---|---|
| The source database serves as a standby node of your cluster and the value of log_slave_updates is not ON. In this case, primary node transactions replayed by the source database during synchronization are not synchronized to the destination database. | If the connected source database serves as a standby node of your cluster, set the log_slave_updates value of the source database to ON. |
11. Checking gtid_mode
| Reason for Failure | Suggestion |
|---|---|
| The gtid_mode values of the source and destination databases are not ON. Therefore, primary/standby failover cannot be performed on the source database during synchronization. | If the source database uses a primary/standby architecture, we recommend that you set gtid_mode to ON for both the destination databases and the primary/standby nodes of the source database. |
12. Checking the Case Sensitivity Used for the Log Package of the Destination Database
| Reason for Failure | Suggestion |
|---|---|
| The lower_case_table_names values of the source and destination databases are inconsistent. | Log in to your source and destination databases and check the lower_case_table_names values. Specify the lower_case_table_names value of the source database as that of the destination database for mysqld in the configuration file of the destination database. Then, restart mysqld. |
13. Checking the Maximum Number of Connections Configured for the Source Database
| Reason for Failure | Suggestion |
|---|---|
| The maximum number of connections configured for the source database is less than 300. | Run the following statement on the source database: set global max_connections=300; Then, re-start the precheck. |
14. Checking the Maximum Number of Connections Configured for the Destination Database
| Reason for Failure | Suggestion |
|---|---|
| The maximum number of connections configured for the destination database is less than 300. | Run the following statement on the source database: set global max_connections=300; Then, re-start the precheck. |
15. Checking the Primary Key of a Table to Be Migrated
| Reason for Failure | Suggestion |
|---|---|
| Objects to be migrated include a table without a primary key, or the primary key column of a table to be migrated is not selected for column name mapping. | (1) If a table does not have a primary key or the primary key column of a table is not selected for mapping, you can only migrate the table. If you need to synchronize the table, specify a primary key for the table or select the primary key column of the table. (2) If not all columns that consist of the composite primary key or composite unique key constraint are selected, the DTS task is invalid. In this case, either deselect the columns or select all the columns after you assess whether the operation affects your business. |
Checking User Permissions on Databases
1. Checking User Permissions on the Source Database
| Reason for Failure | Suggestion |
|---|---|
| The source database instance account used for migration does not have the permissions to query MySQL databases, does not have the permissions to query certain source databases, or does not have all the required global permissions. | To grant all the required permissions to the account, run the following statements: GRANT SELECT ON mysql.* TO 'Account used for migration'@'%';GRANT SELECT ON Source database.* TO 'Account used for migration'@'%';GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW,PROCESS ON . TO 'Account used for migration'@'%'; |
2. Checking User Permissions on a Destination Database
| Reason for Failure | Suggestion |
|---|---|
| The migration account of the destination database lacks certain global permissions. | Log in to the destination MySQL database, and run the following command to grant the required permissions to the migration account: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON . TO 'Account used for migration'@'%'; |
Checking Database Versions
1. Checking MySQL Versions
| Reason for Failure | Suggestion |
|---|---|
| The version of the source database is invalid. The major version cannot be earlier than 5.6. |
The version of the destination database is invalid. The major version cannot be earlier than 5.6.
The versions of the source database and the destination database are invalid. The major version of the source database is later than the major version of the destination database. | Make sure that the MySQL versions of the source database and the destination database are within the valid range and that the MySQL version of the destination database is not earlier than that of the source database. For two-way synchronization, the major versions of the source database and the destination database must be consistent. |
Checking Network Conditions
1. Checking Source Database Connectivity
| Reason for Failure | Suggestion |
|---|---|
| The account or password of the source database is incorrect. | Enter the correct database account and password, and then re-perform the precheck. |
| The network or server where the source database is deployed is configured with a firewall. | Check whether the firewall of the network where the database is deployed blocks the CIDR block or IP address of your DTS instance. If yes, disable the firewall or add the CIDR block or IP address of the DTS instance to the IP address trustlist of the firewall. |
| The permissions of the migration account to log in to the source database are insufficient. | Log in to the source database, and grant the migration account the relevant remote access permissions. |
| A network connectivity issue occurred. | Contact the administrator of the machine where the database runs and the administrator of the machine where your DTS instance is deployed for troubleshooting. |
2. Checking Destination Database Connectivity
| Reason for Failure | Suggestion |
|---|---|
| The account or password of the destination database is incorrect. | Enter the correct database account and password, and then re-perform the precheck. |
| The network or server where the destination database is deployed is configured with a firewall. | Check whether the firewall of the network where the database is deployed blocks the CIDR block or IP address of your DTS instance. If yes, disable the firewall or add the CIDR block or IP address of the DTS instance to the IP address trustlist of the firewall. |
| The permissions of the migration account to log in to the destination database are insufficient. | Log in to the destination database, and grant the migration account the relevant remote access permissions. |
| A network connectivity issue occurred. | Contact the administrator of the machine where the database runs and the administrator of the machine where your DTS instance is deployed for troubleshooting. |
Checking Database Objects
1. Checking Integrity Constraints
| Reason for Failure | Suggestion |
|---|---|
| The parent table on which the child table to be migrated depends is not selected as an object to be migrated. | Add the corresponding parent table to the objects to be migrated. |
Remove the corresponding child table from the objects to be migrated.
Remove the relevant foreign key dependency. |
2. Checking View Dependencies
| Reason for Failure | Suggestion |
|---|---|
| The table on which the view to be migrated depends does not exist or the corresponding database, table, or column is renamed. | Add the table on which the view depends to the objects to be migrated. |
Remove the view from the objects to be migrated.
Cancel the renaming of the database, table, or column on which the view depends. |
3. Checking Object Name Conflicts
| Reason for Failure | Suggestion |
|---|---|
| The destination database already contains tables, views, functions, or stored procedures that have name conflicts with the objects to be migrated, which means that they have the same names. | (1) Ignore the warning and run the DTS task. Then, DTS skips the objects with name conflicts during struct migration and overwrites the data of the same primary key in the tables with name conflicts during full data migration. Note that inconsistent table structs may cause the full data migration task to fail. (2) Delete or rename the objects with name conflicts in the destination database. (3) Remove the objects with name conflicts from the objects to be migrated. (4) If only tables have name conflicts, you can create table name mappings for these tables. |
4. Checking Schema's Default Character Set
| Reason for Failure | Suggestion |
|---|---|
| The character set configured for the source database does not support Chinese character encoding. A destination database object with the same name already exists, and the character set of the destination database does not support Chinese character encoding. | If the default character set of a database does not support Chinese character encoding, the Chinese characters will become garbled in the destination database after the task is completed. For security reasons, modify the character sets of these databases to character sets that support Chinese character encoding (utf8, gbk, latin1, or utf8mb4), and then start the task. |
5. Checking Storage Engines
| Reason for Failure | Suggestion |
|---|---|
| The storage engine of some tables to be migrated is not InnoDB or MyISAM. | (1) Run the following command to change the storage engine to a supported one, such as InnoDB: alter table engine=InnoDB; (2) Remove the tables whose storage engines are not supported from the objects to be migrated based on the error details. |