How Can I Determine Whether a Data Migration task Can be Stopped?
Before you manually stop a data migration task, you must confirm the following items:
At least one complete data comparison is performed.
Business cutover is complete. Perform the following steps to complete business cutover:
Stop the business. (If the business has a light workload, you do not need to stop the business.)
Execute the SHOW PROCESSLIST statement on the source MySQL database and then observe for one to five minutes. If no new sessions are built to execute SQL statements, the business is stopped.
Make sure that the real-time synchronization latency is maintained as 0 for a certain period of time. You can also use the data audit feature to compare data before performing business cutover. For the data comparison duration, refer to the preceding data comparison record.
Specify the system cutover time when the business system points to the destination database and the business is restored.
Stop the migration task. After you stop the migration task, the migration instance is deleted, and the migration task still remains in the task list. You can view or delete the task.
How Does DTS Migrate MySQL Stored Procedures?
When DTS is migrating a stored procedure from a MySQL database to the destination database, it processes the stored procedure data as follows:
Changes the definer.
After DTS migrates a stored procedure, it changes the definer of the stored procedure to the user who performs the migration. For example, if the definer of the stored procedure in the source database is User A and User D creates a migration task to migrate the database, the definer is changed to User D after migration.
Changes the invoker.
After the stored procedure is migrated to the destination database, the role of User A on the destination database is Invoker. User A has only the call permissions and does not have the management permissions on the stored procedure in the destination database. If you want User A to be the definer of the stored procedure in the destination database, manually change the definer to User A on the destination database.
How Can I Confirm that Data Change in the Source Database is Ended During Incremental Data Migration/Synchronization?
You can use one of the following methods to check whether the data in the source database is no longer changed during incremental data migration or synchronization.
View the Incremental Data Migration/Synchronization Offset
Log in to the eSurfing Cloud official website and go to the DTS console. Then, go to Data Migration/Data Sync > Instance Management > Instance Details > Migration Details/Sync Details > Incremental Data Migration/Incremental Data Sync to view the status of the task.
Refresh the page for several times. If the data offset of the source database and the data offset that DTS pulls remains unchanged and you make sure that there is no data latency, the data in the source database is no longer changed.
If the data offset of the source database changes, but the data offset that DTS pulls remains unchanged and the latency increases, you need to proceed to the next step.
Log in to the Source Database to Execute the Following SQL Statement
Log in to the source database, execute the following SQL statement, and record the returned result.
show binlog events limit 10;
Analyze the returned result and check whether DDL or DML statements on the database or tables related to the DTS migration or synchronization task exist.
Repeat Step 1 and 2 for multiple times.
If the result of the SQL statement does not include a DDL or DML statement on the database or tables related to the DTS migration or synchronization task, the data in the source database is no longer changed.
Why Does an Error Indicating the Index Length Limit is Exceeded Prompt When Migrating Data to an RDS MySQL Database?
In MySQL, the maximum number of bytes can be contained in a single character varies by character set. The default character set of eSurfing Cloud RDS MySQL is utf8mb4 and the storage engine is InnoDB. The length limits of different types of indexes are as follows:
● Single-field index: The length of a field cannot exceed 767 bytes. Number of Characters = 767/Maximum bytes.
● Composite index: The length of each field must meet the single-field index length requirement, and the total length of all fields cannot exceed 3,072 bytes, which is the upper length limit of composite indexes.
For more information, see the following documents on the official website of MySQL:
https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html
Therefore, to migrate data to an RDS MySQL database, you must set suitable indexes in the source database.
How Does DTS Migrate MyISAM Tables from a MySQL Database?
If the source database is an eSurfing Cloud RDS MYSQL database, the default storage engine is InnoDB. Therefore, MyISAM tables do not need to be migrated. If the source database is a MySQL database from a third-party cloud or an on-premises MySQL database, DTS migrates the MyISAM tables from the source database to the destination database. The MyISAM tables in the destination database are consistent with the MyISAM tables in the source database.
How Can I Migrate Data to a MySQL 8.0 Database?
DTS can migrate data from a MySQL database of an earlier version to a database of MySQL 8.0. The following migrations are supported:
MySQL 5.6 -> MySQL 8.0
MySQL 5.7 -> MySQL 8.0
In addition, DTS can migrate tables, indexes, stored procedures, views, functions, events, triggers and others in MySQL databases. When creating a task to migrate data from MySQL 5.6 or 5.7 to MySQL 8.0, you need to check the following items:
Do the objects to be migrated include removed functions?
Some built-in functions in earlier versions are removed from MySQL 8.0. For more information, see the following document on the official website of MySQL:
https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html
Therefore, before you migrate composite objects, make sure that the objects do not include removed functions. If removed functions are included, you must manually modify the migrated objects in the destination database and set adaption configurations after the migration is complete.
| Name | Feature | Removed |
|---|---|---|
| DECODE() | Decodes strings | Yes |
| DES_DECRYPT() | Unencrypts strings | Yes |
| DES_ENCRYPT() | Encrypts strings | Yes |
| ENCODE() | Encodes strings | Yes |
| ENCRYPT() | Encrypts strings | Yes |
| PASSWORD() | Returns password strings | Yes |
Do the tables to be migrated use the deprecated character set utf8mb3？
In versions earlier than MySQL 8.0.29, UTF-8 refers to utf8mb3, and all utf8mb3 characters in SQL statements are automatically converted to UTF-8 characters.
https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8.html
In MySQL 8.0.30 and later, utf8mb3 characters are not converted to UTF-8 characters. For example, in SHOW CREATE TABLE, SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS, SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS statements, you can find character sets or sorting rule names with the UTF8MB3 or MTF8MB3_ prefix.
When DTS is executing a MySQL-to-MySQL structure migration, it remains the character set of the source database and tables. If the character set of the source database and tables is utf8mb3, the character set of the data migrated into the destination database is also utf8mb3. For better compatibility, we recommend that you manually change the character set to utf8mb4 after the migration is complete.
How Does DTS Migrate Events and Triggers in a MySQL Database?
To ensure data consistency after migration, DTS takes the following measures when migrating events and triggers in a MySQL database:
Events and triggers are different from tables, views, and stored procedures. DTS classifies all events and triggers into post migration objects.
DTS starts to migrate post migration objects after full data migration is complete and before incremental data migration begins.
If the migration task does not migrate the full data, DTS starts to migrate post migration objects in the last phase of structure migration.
DTS performs structure migration for post migration objects as for normal objects.
How Can I Configure the lower_case_table_names Parameter for MySQL Data Migration?
The lower_case_table_names parameter affects how table names are stored and compared. For more information, see the following documents on the official website of MySQL:
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
To ensure data consistency, you must set the lower_case_table_names parameter to the same value for the source database and the destination database.
At the precheck stage, if DTS detects that the values of the lower_case_table_names parameter for the source database and the destination database are different, it returns an error. You must modify the configurations as prompted.