Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of data migration in MySQL". The content is simple and easy to understand and organized. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn the "sample analysis of data migration in MySQL" article.

1. About Data Migration

First of all, quote the following explanation of data migration in Wikipedia:

Data migration refers to the process of selecting, preparing, extracting, and transforming data and permanently transferring data from one computer storage system to another. In addition, verifying the integrity of migrated data and decommissioning old data stores are considered part of the overall data migration process.

Data migration is a key consideration for any system implementation, upgrade or integration, and is typically performed as automatically as possible, freeing human resources from tedious tasks.

There are many reasons for data migration, including server or storage replacement, maintenance or upgrade, application migration, website integration, disaster recovery, and data center migration.

For MySQL databases, you will often encounter data migration requirements, such as moving from a test library to a development library, or from one machine to another, which may be just a single table or an entire database instance. Different migration schemes may be adopted for different requirements, but in general, MySQL data migration schemes can be roughly divided into two categories: physical migration and logical migration.

2. Migration Plan and Points for Attention

Physical migration is suitable for bulk migration with large data volumes. 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 database software on the new server, create the same directory, recommend that the configuration file be the same as the original database, 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 a physical migration solution is that it is faster, but it requires downtime and requires that MySQL version and configuration must be the same as the original server, and may cause unknown problems.

In contrast, logical migration has a wider application range, whether it is partial migration or full migration, logical migration can be used. Logical migration is most commonly used through mysqldump backup tools such as export and import.

mysqldump is also suitable for migration between different versions and different configurations, but when migrating in full volume, I do not recommend using the-A parameter to backup all databases, especially for migration between different versions, some system libraries may be slightly different, and unknown problems are easy to occur after migration. To perform a full migration using mysqldump, follow these steps:

#Create database statements obtained by executing the original database are executed 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' ); #Create user statements obtained by executing the original library are 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 get user privileges and then execute them one by one in the new library show grants for 'testuser'@'%';#So far, the new environment already has libraries and users to migrate, but there is no data #The original environment uses mysqldump to backup all libraries except system libraries mysqldump -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql #Then import the new environment mysql -uroot -pxxxx

db1.sql #Backup partial tables mysqldump -uroot -pxxxxx --single-transaction db1 tb1 tb2 > tb12.sql #Investigate certain 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, it is more flexible to use mysqldump, and we should minimize the backup content for fast migration, 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 particularly large, so we can exclude tb1 first when backing up. For large tables tb1, we can use LOAD DATA or abandon and 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 good user in the new library and grant good permissions before migration, so as to avoid view and function import errors, because views and functions have a definition of the concept. When performing import in 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, for the new environment, we should check 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, etc. It's only when you're sure.

The above is "MySQL data migration sample analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.