preface:

In daily work, we often encounter the need of data migration, such as migrating a table, a library or an instance. Different migration schemes may be adopted according to different needs, and various size problems may be encountered in the process of data migration. In this article, let's take a look at MySQL data migration, hoping to help you.

1. About data migration

First of all, let's quote the explanation of data migration in Wikipedia:

Data migration refers to the process of selecting, preparing, extracting and converting data, and permanently transferring data from one computer storage system to another. In addition, verifying the integrity of migrated data and decommissioning the original old data storage are also considered to be part of the whole data migration process.
Data migration is a key consideration in any system implementation, upgrade or integration. It is usually carried out as automatically as possible, so as to liberate human resources from cumbersome tasks.
There are many reasons for data migration, including server or storage device replacement, maintenance or upgrade, application migration, website integration, disaster recovery and data center migration.

For MySQL database, we often encounter the need of data migration, such as moving from test library to development library, or from one machine to another. It may only migrate one table, or it may need to migrate the whole database instance. Different migration schemes may be adopted for different needs, but generally speaking, MySQL data migration schemes can be divided into physical migration and logical migration.

2. Migration scheme and points for attention

Physical migration is applicable to the overall migration under a large amount of data. Physical migration includes copying data files and using XtraBackup backup tool. Physical migration can be used between different servers. We can install the same version of database software on the new server and create the same directory. It is recommended that the configuration file should also be the same as the original database, then copy the data file and log file from the original database, configure the file group permissions, and then use mysqld command to start the database on the new server.

The advantage of using the physical migration scheme is that it is relatively fast, but it needs to stop the migration, and the MySQL version and configuration must be the same as the original server, which may also cause unknown problems.

In contrast, logical migration has a wider scope of application. Whether it is partial migration or full migration, logical migration can be used. The most commonly used in logical migration is to export and then import through backup tools such as mysqldump.

Mysqldump is also applicable to the migration between different versions and different configurations. However, during full migration, the author does not recommend using the - A parameter to back up all databases. Especially for the migration between different versions, some system libraries may be slightly different, and unknown problems are easy to occur after migration. If mysqldump is used for full migration, you can follow the following steps:

# The database creation statement obtained from the execution of the original library is executed in the new library (excluding the system library)
SELECT
  CONCAT( 'CREATE DATABASE IF NOT EXISTS ', '`', SCHEMA_NAME, '`', ' DEFAULT CHARACTER SET ', DEFAULT_CHARACTER_SET_NAME, ';' ) AS CreateDatabaseQuery 
FROM
  information_schema.SCHEMATA 
WHERE
  SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
  
 # The user creation statement obtained from the execution of the original library is executed in the new library (excluding system users)
 SELECT
  CONCAT( 'create user \'', USER, '\'@\'', HOST, '\'' ' IDENTIFIED BY PASSWORD \'', authentication_string, '\';' ) AS CreateUserQuery 
FROM
  mysql.`user` 
WHERE
  `User` NOT IN ( 'root', 'mysql.session', 'mysql.sys' );
  
# Execute show grants in the original library to obtain user permissions, and then execute it in the new library one by one (you can also copy those permission tables to the New Library)
show grants for 'testuser'@'%';

# So far, there are libraries and users to be migrated in the new environment, but there is no data
# The original environment uses mysqldump to back up all libraries except the system library 
mysqldump  -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql

# Then import the new environment
mysql -uroot -pxxxx < db.sql

For the migration of some libraries and tables, you can also use the similar scheme above, but you need to back up on demand during backup. Here are several common mysqldump backup scenarios:

# Backing up a single library
mysqldump -uroot -pxxxxx -R -E --single-transactio --databases db1 > db1.sql

# Backup part table
mysqldump -uroot -pxxxxx --single-transaction db1 tb1 tb2 > tb12.sql

# Check some tables
mysqldump -uroot -pxxxxx db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > db1.sql

# Backup only structure or data
mysqldump -uroot -pxxxxx testdb --no-data > testdb_jiegou.sql
mysqldump -uroot -pxxxxx testdb --no-create-info > testdb_data.sql

In general, the mysqldump scheme is more flexible. In order to migrate quickly, we should minimize the backup content, such as excluding useless log tables. For some large tables, we can also adopt a separate migration scheme. For example, the tb1 table in db1 is very large. We can exclude tb1 during backup. For large tables, we can use LOAD DATA or discard and import tablespace to migrate.

In the process of data migration, you may encounter various errors, which can be solved step by step. It is recommended to migrate after the new library has created users and granted permissions, so as to avoid the import errors of views and functions, because views and functions have the concept of a definer. When importing the new environment, it is best to use administrator users with SUPER permissions such as root, which can also avoid some problems caused by permissions.

After the migration, we should also check the new environment again, such as whether the number of tables is the same, randomly check several tables, whether the data is the same, whether there is garbled code, etc. It's only when you're sure.

Summary:

This article summarizes the MySQL database migration scheme and the related data guide points as follows: