MySQL Migration to MySQL

UDTS supports MySQL as a data transfer source/target, supporting versions including MySQL (including Percona) 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 migration of the following content:

• Database, Table structures, and data
• Views
• Functions, Stored Procedures

During full migration, to ensure data consistency and prevent data conflicts, the data in the target database is cleaned before migration. The contents corresponding to this migration, including Database and Table, are specifically cleaned as follows:

Notes:

• 1. Suppose the output of SHOW DATABASES on the source is:

> 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, remaining mydb1 and mydb2 will have the following executed on the target database during migration:

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

• 2. If the migration task is configured with the Database Name as db1,db2,db3, the following will be executed on the target database:

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

• 3. If the migration task is configured with the Database Name as db1, and migrating Table Name is table1,table2,table3, the following will be executed on the target database:

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

Preconditions

When migrating with UDTS, the Pre-check can be used to inspect necessary conditions, including:

• Connectivity check, ensuring the accuracy of information such as host address, port, username, and password.
• Permission check, verifying the permissions required for migration.
• Configuration check, such as sql_mode, binlog format, unique key check, etc.

Connectivity Check

Connectivity checks involve verifying the host address, port, username, and password provided.

• Host connectivity check: If an incorrect host address or port is entered, 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 ensure that 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 ensure the username and password are correct.

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

  • For a self-hosted MySQL, check the host’s iptables rule.
  • If a whitelist is set, contact technical support to obtain the whitelist address.

Permission Check

By default, migration requires the source database account to have SUPER privileges. If SUPER privileges are not available, select the “NoLocks” mode.
If users wish to use the NoBinlog mode during the full stage, SUPER privileges are required on the target database.

sql_mode Check

To ensure migrations proceed correctly, it is best to keep the sql_mode the same for both the source and target databases. The sql_mode can be queried using the following command:

select @@sql_mode;

--- or
show variables like "sql_mode";

If the sql_mode of the target database and the source database are not consistent, they can be modified using 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 migration task type is Incremental, Full + Incremental, or a full task that later requires incremental migration, the source database needs binlog to be enabled with the format set to ROW and the image set to FULL.

binlog_format    must be ROW
binlog_row_image must be 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: For MySQL 5.5, there is no binlog_row_image variable, so no setting is needed.

MyISAM Engine Table Check

If the table to be migrated in the source database includes a MyISAM engine table and the target database has GTID enabled, it may lead to MySQL 1785 errors, with an error message such as:

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 to convert MyISAM engine tables to InnoDB engine tables or turn off GTID mode on the target database. Query method:

# Query in source database if there are MyISAM tables in 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 target database whether GTID is enabled
show global variables like 'gtid_mode';

Setting method:

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

# Option 2: Modify the target database
# Turn off the GTID mode on the target database
set global gtid_mode = "ON_PERMISSIVE";
set global gtid_mode = "OFF_PERMISSIVE";
set global gtid_mode = "OFF";

Character Set Check When Migrating from MySQL to TiDB

TiDB currently supports character sets including ascii/latin1/binary/utf8/utf8mb4. If a table or a specific field in the source database to be migrated uses character sets outside these supported ones, migration will not be possible.

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 column1 in table table1 to utf8
alter table table1 change column1 column1 varchar(200) character set utf8;

Feature Limitations

MyISAM Engine Tables

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

• Exporting data locks MyISAM tables (reading is not affected) until all data is exported. This might impact services.
• Transactions cannot update both MyISAM and InnoDB tables simultaneously.
• Damaged tables must be repaired before migration.

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

Data Volume

It is recommended that a single full migration task does not exceed 200G in data size, with a maximum support of 500G. If the data to be migrated exceeds 500G, consider splitting the task into multiple parts. UDTS offers multi-dimensional migration methods by database, table, multiple databases, or multiple tables.

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

Other Limitations

• Built-in databases other than ‘test’ are not migrated.
• If DDL statements are executed on the database to be migrated during the dump process, the UDTS task will fail. Users can restart the task at a time when no DDL statements are executed.
• Event and trigger are temporarily unsupported during full and incremental stages.
• Before starting incremental synchronization, event must be turned off.

  -- Stop all events
  
  SET GLOBAL event_scheduler = OFF;
  
  -- If migrating by database, only stop the event of the specified database
  -- Find the corresponding event and stop it
  SHOW EVENTS;
  
  ALTER EVENT hello DISABLE;

• If the source is Alibaba Cloud MySQL RDS, all tables must have explicit primary keys; otherwise, incremental synchronization is not supported.
• When database names contain special characters like ’$’, only single-database migration is supported.
• During incremental migration, DDL statements only support character sets ascii/latin1/binary/utf8/utf8mb4.

Attention

Storage Space

If binlog is enabled on the MySQL target database, the binlog generated by the target database will occupy storage space. When the data volume is large (over 200G), it is recommended to enable the NoBinlog option. In this way, no binlog will be generated during the full migration process, reducing disk demand and accelerating migration speed. If binlog must be enabled during the migration process, please create a larger storage space for the target database or regularly clean up unneeded binlog files to prevent task failure due to insufficient storage space (it is generally estimated that migrating 3TB data generates about 3TB binlog files, meaning 3TB storage for source, 6TB for target).

UDB MySQL supports a binlog auto-cleaning policy based on retention time and disk usage percentage. Refer to UDB documentation for details. If the target database is highly available UDB, an HA alert will be triggered when NoBinlog is enabled. You can ignore this alert and reimplement HA once the full task completes.

If a task fails, you can restart it after adjusting the target database configuration, and the task will be restarted.

Master-Slave Switching

If the source MySQL database is in a master-slave setup, switching impacts UDTS as follows:

• Occurs during full migration, leading to full migration failure, requiring task reboot.
• Occurs during incremental synchronization. No impact if GTID synchronization is used; failure occurs if not, requiring full + incremental setup again.

SSL Secure Connection

To improve link security, SSL certification is supported for database connections. SSL encrypts data on the transport layer, enhancing communication security, though it adds some network response time.

UDTS supports certificates in pem format. If your certificates are in another format, convert them to pem. SSL Secure Connection can be set in source or target.

How to Use

In either source or target, enable the SSL Secure Connection option and upload the ca certificate.

Cross-Version Migration

When performing cross-version migration, be aware of any incompatibilities between the source and target databases, such as:

• The default collation utf8mb4_0900_ai_ci of MySQL 8.0 is incompatible with MySQL 5.x. To migrate from MySQL 8.0 to 5.x, change collation to a MySQL 5.x compatible type, such as utf8mb4_general_ci.
• MySQL 5.6 supports the geometry data type field as an empty string, while MySQL 5.7 and later do not. Ensure the geometry field in MySQL 5.6 is not an empty string before migrating to a higher version.

MySQL Form Filling

Data Source Form

Transfer Target Form

Suggested Rate Configuration Table

Reference values for source speed limit configuration

Reference values for target speed limit configuration