Summary of MySQL data migration

Preface:

In daily work, we often encounter the need to migrate data, such as migrating a table, a database, or an instance. Different migration plans may be required according to different needs, and various problems of varying sizes may be encountered during the data migration process. In this article, let’s take a look at MySQL data migration, hoping it will be helpful to everyone.

1. About data migration

First, let’s quote Wikipedia’s explanation of data migration:

Data migration is the process of selecting, preparing, extracting, and transforming data and permanently transferring it from one computer storage system to another. Additionally, verifying the integrity of the migrated data and decommissioning the old data store are also considered part of the overall data migration process.

Data migration is a key consideration for any system implementation, upgrade or integration and is often performed in as automated a manner as possible, freeing up human resources from tedious tasks.

Data migration occurs for a variety of reasons, including server or storage device replacement, maintenance or upgrades, application migration, website integration, disaster recovery, and data center relocation.

For MySQL databases, data migration needs are often encountered, such as migrating from a test library to a development library, or from one machine to another. It may be necessary to migrate only one table, or it may be necessary to migrate the entire database instance. Different migration plans may be required for different needs, but generally speaking, MySQL data migration plans can be roughly divided into two categories: physical migration and logical migration.

2. Migration plan and points to note

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

The advantage of using a physical migration solution is that it is relatively fast, but it requires downtime for migration and requires that the MySQL version and configuration must be the same as the original server, which may also cause unknown problems.

In comparison, logical migration has a wider scope of application and can be used for both partial migration and full migration. The most commonly used method in logical migration is to export and import using backup tools such as mysqldump.

mysqldump is also suitable for migration between different versions and configurations. However, when migrating a full database, I do not recommend using the -A parameter to back up the entire database, especially when migrating between different versions. Some system libraries may be slightly different, which may cause unknown problems after migration. If you use mysqldump for full migration, you can follow the steps below:

# The original database execution obtains the creation database statement and executes it in the new database (excluding the system database)
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 original database executes the created user statement obtained in the new database (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 database to get the user permissions and then execute them one by one in the new database (you can also copy those permission tables to the new database)
show grants for 'testuser'@'%';

# At this point, the new environment already has the libraries and users to be migrated, but no data # Use mysqldump to back up all libraries except the system library in the original environment
 mysqldump -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql

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

For migrating some libraries and tables, you can also use a similar solution as above, but backup should be done on demand. Here are some common mysqldump backup scenarios:

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

# Back up some tables 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

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

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

During the data migration process, you may encounter various errors, which can be solved step by step. It is recommended to create a user in the new database and grant permissions before migration. This can avoid errors in importing views and functions, because views and functions have a concept of definer. When importing a new environment, it is best to use an administrator user with SUPER privileges, such as root, to avoid some problems caused by permissions.

After the migration is completed, we should check the new environment again, such as whether the number of tables is the same, randomly check a few tables, whether the data is the same, whether there is garbled code, etc. Only when you are sure of everything are you successful.

Summarize:

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

The above is the detailed summary of MySQL data migration. For more information about MySQL data migration, please pay attention to other related articles on 123WORDPRESS.COM!