MySQL

UDTS supports MySQL as a data transfer source/target, supporting versions include MySQL (including Percona version) 5.5/5.6/5.7/8.0; MariaDB 10.1.2 and above, as well as PolarDB (MySQL compatible version).

Migration Content

Full and incremental task migration includes the following content

• Database, Table structure and data
• Views
• Functions, Procedures

During the full migration, in order to ensure data consistency and prevent data conflicts, the data in the target database will be cleaned up before the migration. The cleanup content corresponds to the Database and Table of this migration, as follows

Notes:

• 1. Assume the content of executing SHOW DATABASES on the source database is as follows:

     > SHOW databases;
      
     INFORMATION_SCHEMA
     PERFORMANCE_SCHEMA
     mysql
     sys
     mydb1
     mydb2

     The current built-in databases are mysql, sys, INFORMATION_SCHEMA, PERFORMANCE_SCHEMA. Excluding these DBs, the remaining are mydb1 and mydb2. Therefore, during the migration task, the following will be executed in the target database:

     DROP DATABASE IF EXISTS `mydb1`;
     DROP DATABASE IF EXISTS `mydb2`;

• 2. If the database name configured in the migration task is db1, db2, db3, then it will be executed in the target database.

     DROP DATABASE IF EXISTS `db1`;
     DROP DATABASE IF EXISTS `db2`;
     DROP DATABASE IF EXISTS `db3`;

• 3. The database name configured for the migration task is db1, and the table names to be migrated are table1, table2, table3, then it will be executed in the target database.

     DROP TABLE IF EXISTS `db1`.`table1`;
     DROP TABLE IF EXISTS `db1`.`table2`;
     DROP TABLE IF EXISTS `db1`.`table3`;

Attention

During the migration, UDTS can perform a pre-check to verify the necessary conditions, including the following:

• Connectivity check, including the correctness of host address, port, username, and password.
• Permission check, to verify the permissions needed during migration.
• Configuration check, such as sql_mode, binlog format, unique key check, etc.

Connectivity Check

The connectivity check will verify the host address, port, username, and password you have entered.

• Host Connectivity Check: If you have entered an incorrect host address or port, a connectivity check failure will occur: Project OR IP:10.19.37.212 OR Vpc:uvnet-u0ecvp, Subnet:subnet-2slodr error, please check again. Please confirm whether the current host address, port, selected VPC ID, subnet, and project are correct.

• Username and password check: If an incorrect username or password is entered, a connectivity check failure will occur: Error 1045: Access denied for user 'root1'@'10.42.255.213' (using password: YES). Please confirm that the username and password are correct.

• Firewall and Whitelist: If the host address, port, username, and password are all correct

  • If MySQL is self-built by the host, you need to check the host's iptables rules
  • If a whitelist is set, please contact technical support to get the whitelist address

Permission Check

The default configuration migration requires the source database account to have SUPER privileges. If you do not have SUPER privileges, please select the NoLocks mode. If the user needs to enable the NoBinlog mode during the full amount stage, then SUPER privileges are also required in the target database.

sql_mode Check

To ensure the migration is executed correctly, it is best to keep the sql_mode of the source and target databases consistent. You can query the sql_mode with the following commands:

select @@sql_mode;

--- or
show variables like "sql_mode";

If the sql_mode of the target database and the source database are inconsistent, you can modify it with the following command:

SET GLOBAL sql_mode='xxxx';

The specific value can be queried by connecting to the source database.

Binlog Format Check

If the type of migration task is Incremental, Full + Incremental, or if incremental migration is required after the full task, the source database needs to enable binlog, and binlog_format should be set to ROW, binlog_row_image should be set to FULL.

Query method:

show global variables like 'binlog_format';
show global variables like 'binlog_row_image';

Setting method:

set global binlog_format = "ROW" ;
set global binlog_row_image = "FULL" ;

Note: If it is MySQL 5.5, there is no binlog_row_image variable, no need to set

MyISAM Engine Table Check

If the tables to be migrated from the source database include MyISAM engine tables, and the target database has GTID enabled, it may lead to MySQL 1785 error, with the following error message:

When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables

It is recommended that users convert MyISAM engine tables to InnoDB engine tables, or turn off the GTID mode of the target database.

Query method:

# Query in the source database whether there is a MyISAM table in the database db1
select table_schema, table_name
	from information_schema.tables
	where engine = 'MyISAM'
		and table_type = 'BASE TABLE'
		and table_schema in (db1);

# Query in the target database whether GTID has been turned on
show global variables like 'gtid_mode';

Setting Method:

# Solution One: Modify the source database
# Change the engine of MyISAM table table1 to InnoDB
alter table table1 ENGINE = InnoDB;

# Solution Two: Modify the target database
# Turn off the GTID mode of the target database
set global gtid_mode = "ON_PERMISSIVE";
set global gtid_mode = "OFF_PERMISSIVE";
set global gtid_mode = "OFF";

Checking the Source Database Character Set When Migrating from MySQL to TiDB

The character sets currently supported by TiDB include ascii/latin1/binary/utf8/utf8mb4. When migrating from MySQL to TiDB, if the character set used by the table or a field in the table to be migrated in the source database is not included in the above character sets, the migration cannot be performed.

Query method:

show create table table1;

Setting method:

# Change the character set of table table1 to utf8
alter table task character set utf8;

# Change the character set of field column1 in table table1 to utf8
alter table table1 change column1 column1 varchar(200) character set utf8;

Function Limitations

MyISAM Engine Tables

UDTS supports full migration and incremental synchronization of MyISAM engine tables, but there are the following limitations:

• When exporting data, the MyISAM table will be locked (reading is not affected) until the full table data is exported. This may affect business operations.
• It does not support updating MyISAM engine tables and InnoDB engine tables in the same transaction.
• For damaged tables, repairs should be made before migration.

It is recommended that users convert MyISAM engine tables to InnoDB engine tables before using UDTS for migration.

Data Volume

It is recommended that the data volume migrated by a single full migration task does not exceed 200G, with a maximum support of 500G. If the data volume to be migrated exceeds 500G, it is recommended to split the task into multiple tasks for migration. UDTS provides multi-dimensional migration methods such as by database, by table, by multiple databases, and by multiple tables.

If the migration task exceeds 500G and cannot be split into multiple tasks, please contact technical support.

Other Limitations

• Do not migrate built-in databases other than 'test'.
• During the dump process, if there are DDL statements being executed on the database to be migrated, the UDTS task will fail. Users can choose a time period without DDL statements to restart the task.
• Full and incremental phases temporarily do not support 'event' and 'trigger'.
• Before starting incremental synchronization, 'event' needs to be turned off.

-- Stop all events

SET GLOBAL event_scheduler = OFF;

-- If migrating by database, just stop the event of the specified database
-- Find the corresponding event and stop it
SHOW EVENTS;

ALTER EVENT event1 DISABLE;

• When the database name contains special character '$', only single database migration is supported.
• During incremental migration, DDL statements only support the character set ascii/latin1/binary/utf8/utf8mb4.

Attention

Storage Space

If the target MySQL database has Binlog enabled, the Binlog generated by the target database will occupy storage space. When the data volume is large (exceeding 200G), it is recommended that users enable the NoBinlog option. This way, Binlog will not be generated in the target database during the full migration process, reducing the additional demand for disk space. It can also speed up the full migration process and shorten the process. If you must enable Binlog during the migration process, please create larger storage space for the target database or regularly clean up unnecessary Binlog files to avoid task failure due to insufficient storage space (based on experience, migrating 3TB of data will generate about 3TB of Binlog files, that is, if the source database storage space is 3TB, the target needs 6TB of storage space).

If the target database is the high-availability version of UDB, turning on NoBinlog will trigger a high-availability alert. Users can ignore this alert and wait for the full task to complete before redoing the high-availability of the target database.

If the task fails, you can restart the task after adjusting the configuration of the target database. The task will start again.

Master-Slave Switching

If the source MySQL database is a master-slave structure, the impact on UDTS when a master-slave switch occurs is as follows:

• If it occurs during the full migration phase, the full migration fails and the task needs to be restarted.
• If it occurs during the incremental synchronization phase, there is no impact if GTID synchronization is used; if GTID synchronization is not used, the synchronization fails and full + incremental synchronization needs to be redone.

SSL Secure Connection

To enhance the security of the link, we support connecting to the database using an SSL certificate. SSL encrypts data at the transport layer, improving the security of communication data, but it may increase the network connection response time.

UDTS currently supports certificates in pem format. If you are using certificates in other formats, you can first convert them to pem format. The SSL Secure Connection can be set in the source or target.

Operation Guide

In the source or target, turn on the SSLSecurity option and upload the ca certificate at the same time.

Cross-Version Migration

When migrating across versions, please note whether there are features in the source database that are incompatible with the target database, such as:

• The default collation utf8mb4_0900_ai_ci of MySQL8.0 is not compatible with MySQL5.x. If you need to migrate from MySQL8.0 to MySQL5.x, please modify the collation to a type supported by MySQL5.x, such as utf8mb4_general_ci.
• MySQL5.6 supports data type geometry fields as empty strings, while MySQL5.7 and later versions do not. If you need to migrate from MySQL5.6 to a higher version, please ensure that the geometry fields in the source database are not empty strings.

MySQL Form Filling

Data Source Form

Transfer Target Form

Recommended Rate Configuration Table

Reference values for source speed limit configuration

Reference values for target speed limit configuration