MySQL data migration those things

Foreword:

In daily work, we often encounter data migration needs, such as migrating a table, a library, or an instance. According to different needs, different migration schemes may be adopted, and various problems of various sizes may also be encountered during the data migration process. In this article, let's take a look at the things about MySQL data migration, hoping to help you all.

1. About data migration

First quote the explanation of data migration in Wikipedia:

Data migration refers to the process of selecting, preparing, extracting and transforming data, and permanently transferring the data from one computer storage system to another computer storage system. In addition, verifying the integrity of the migrated data and decommissioning the original old data store is also considered part of the entire data migration process.
Data migration is a key consideration for any system implementation, upgrade, or integration, and is usually performed in the most automated manner possible, thereby freeing human resources from tedious tasks.
There are many reasons for data migration, including server or storage device replacement, maintenance or upgrades, application migration, website integration, disaster recovery, and data center migration.

For MySQL databases, data migration requirements are often encountered, such as moving from a test library to a development library, or from one machine to another machine. It may be just to migrate a table or the entire database instance may need to be migrated. . Different migration schemes may be adopted for different requirements, but in general, MySQL data migration schemes can be roughly divided into physical migration and logical migration.

2. Migration plan and points of attention

Physical migration is suitable for overall migration under a large amount of data. Physical migration includes copying data files and using XtraBackup backup tools. Physical migration can be used between different servers. We can install the same version of the database software on the new server and create the same directory. It is recommended that the configuration file be the same as the original database, and then copy the data files and logs from the original database. File, configure the file group permissions, and then use the 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 requires the MySQL version and configuration to be the same as the original server, which may also cause unknown problems.

In contrast, logical migration has a wider range of applications, whether it is partial or full migration, logical migration can be used. The most used in logical migration is to export and import through backup tools such as mysqldump.

mysqldump is also suitable for migration between different versions and different configurations. However, during full migration, I do not recommend using the -A parameter to back up all databases. Especially for migration between different versions, some system libraries may be slightly different, which is easy to migrate. An unknown problem occurred. If you use mysqldump for full migration, you can follow the steps below:

# 原库执行 得到的创建数据库语句在新库执行 (排除系统库)
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' );
  
 # 原库执行 得到的创建用户语句在新库执行 (排除系统用户)
 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' );
  
# 原库执行 show grants 得到用户权限 然后逐一在新库执行 （也可以复制那几张权限表到新库）
show grants for 'testuser'@'%';

# 至此 新环境已经存在要迁移的库和用户 只是没有数据
# 原环境使用mysqldump备份除系统库外的所有库 
mysqldump  -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql

# 然后导入新环境
mysql -uroot -pxxxx < db.sql

For migrating some libraries and some tables, you can also use a similar solution to the above, but you need to back up on demand when you back up. Here are a few common mysqldump backup scenarios:

# 备份单个库
mysqldump -uroot -pxxxxx -R -E --single-transactio --databases db1 > db1.sql

# 备份部分表
mysqldump -uroot -pxxxxx --single-transaction db1 tb1 tb2 > tb12.sql

# 排查某些表
mysqldump -uroot -pxxxxx db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > db1.sql

# 只备份结构或数据
mysqldump -uroot -pxxxxx testdb --no-data > testdb_jiegou.sql
mysqldump -uroot -pxxxxx testdb --no-create-info > testdb_data.sql

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

In the process of data migration, various errors may be encountered, and you can solve them step by step. It is recommended to create users and grant permissions in the new library before migrating, so as to avoid import errors of views and functions, because views and functions have the concept of a definer. When importing a new environment, it is best to use an administrator user with SUPER authority such as root, which can also avoid some problems caused by authority.

After the migration is completed, we should check again for the new environment, such as whether the number of tables is the same, randomly checking a few tables, whether the data is the same, whether there are garbled codes, etc. Only when you are sure and correct will you be done.

summary:

This article introduces the MySQL database data migration related solutions and points of attention, and summarizes the following mind map: