MySQL database migration process

Preface

MySQL database migration refers to the process of migrating a MySQL database from one server to another, or from one environment to another.

This is usually to upgrade servers, increase storage space, improve performance, or change the database schema.

The following is a detailed analysis of MySQL database migration, including preparations, migration methods, precautions, and advantages and disadvantages.

Preparation before migration

Identify migration requirements: Define the purpose and requirements of the migration, including determining the database to be migrated, the target platform (the hardware, operating system, MySQL version of the new server, etc.), and the timetable. At the same time, evaluate the complexity of data migration, such as the size, structure, data volume, index, trigger, etc. of the database.
Backup data: Before performing a database migration, be sure to back up all data to prevent data loss or corruption. This can be done using the mysqldump command or MySQL backup tool.

Migration method

There are many methods for migration of MySQL databases. The following are several commonly used methods:

1. Export and import using mysqldump

(1) Export data: On the server that needs to be migrated, use the mysqldump command to export the database.

mysqldump -u username -ppassword Database name &gt; Export file name.sql

Notice:

If your database is very large or contains a large number of tables, you can use the --single-transaction option to avoid locking tables, which is especially useful for InnoDB storage engines.
Use the --quick option to reduce memory usage, especially for large tables.
Using --lock-tables=false can avoid locking tables during export, but this may increase the risk of data inconsistency.

(2) Transfer files: Transfer the exported SQL files to the target server.

scp Export file name.sql user@Target server:/path/to/destination/

(3) Import data: On the target server, use the mysql command to import the SQL file.

mysql -u username -ppassword Database name &lt; Import file name.sql

Notice:

If the imported database already exists and contains data, you may need to perform some cleaning operations on the target database first, such as deleting old data or resetting tables.
Use the --force option to ignore some errors, but this can cause data inconsistency.
If you encounter permission issues during the import process, make sure that the user of the target database has sufficient permissions to create tables and insert data.

advantage:

The migration process does not affect the source database: the export operation is a read operation and will not have a performance impact on the source database.
Exported SQL files are easy to transfer and store: SQL files are usually in text format, easy to transfer and backup.
High flexibility: It can migrate data between different MySQL versions and operating systems.

shortcoming:

Slow import speed: Especially for large databases, the import process can take a long time.
Large amount of data may lead to insufficient disk space: exported SQL files may be very large and require sufficient storage space.
Data consistency risk: During the export and import process, if data is written to the source database, data inconsistency may occur.

2. Migrate using ibd file

(1) Lock the table and generate the cfg file: On the source database, perform table lock operations on the table that needs to be migrated and generate the corresponding cfg file.

#Login source databasemysql -uusername -p

#Select a databaseUSE Database name;

#Lock the table and generate cfg fileFLUSH TABLES Table name FOR EXPORT;

(2) Initialize the table structure and clear the table content: On the target database, initialize the table structure and clear the table content.

#Get the SQL statement to create the specified tableSHOW CREATE TABLE Table name;

#Execute SQL statements to create tables on the target databaseCREATE TABLE Table name (
  ...
) ENGINE=InnoDB;

#Clear table contentTRUNCATE TABLE Table name;

(3) Copy the file: Copy the cfg file and ibd file on the source database to the file directory of the target database.

scp /path/to/source/Table name.ibd user@Target server:/path/to/destination/ 
scp /path/to/source/Table name.cfg user@Target server:/path/to/destination/

(4) Unlock the table and import the data: Unlock the table on the source database and import the data using the ALTER TABLE statement on the target database.

#Unlock tableUNLOCK TABLES;

#Import dataALTER TABLE Table name DISCARD TABLESPACE;
ALTER TABLE Table name IMPORT TABLESPACE;

Notice:

Version compatibility: Ensure that the MySQL version of the source and target databases are compatible, especially the InnoDB version.
File permissions: Ensure that the data directory of the target database has appropriate file permissions, and that MySQL users can read and write these files.
Table structure consistency: Ensure that the table structure of the source database and the target database are completely consistent, including indexes, foreign keys, etc.
Data consistency: During the migration process, ensure data consistency and avoid data loss or corruption.
Backup: Before migration, be sure to back up the source and target databases to prevent accidents.

advantage:

Faster import speed: Direct copying of data files avoids the data export and import process, and the migration efficiency is high, especially suitable for large data sets.
High data integrity: Maintain the original data format during migration, avoiding losses caused by data conversion and ensuring data integrity and accuracy.
Supports large table migration: For particularly large tables, using ibd file migration is more suitable because only copying the files without complex SQL conversion.

shortcoming:

Operation complexity: The MySQL version and configuration of the target server are required to be strictly matched with the source server; it is necessary to ensure that the table is the InnoDB engine, and for tables that are not InnoDB engines, additional conversion steps may be required.
Data consistency risk: Before copying ibd files, you need to make sure that the database is closed or the table is locked to avoid the risk of data inconsistency.
File system dependencies: The file system compatibility needs to be ensured during the migration process, and additional conversion or adjustment steps may be required if incompatible.

3. Use directory migration as a whole

(1) Copy the data folder

#Stop MySQL service on the source database serversystemctl stop 

#Back up the data folder of the source databasecp -R /var/lib/mysql /var/lib/mysql_backup

# You can use rsync tool to efficiently copy data folders (except MySQL's own data folders such as performance_schema, sys, etc.)sync -avz --exclude={performance_schema,sys} /var/lib/mysql/ user@Target server:/var/lib/mysql/

(2) Start new MySQL

#Stop MySQL service on the target database serversystemctl stop 

#Authorization, ensure that users have access to data folderschown -R mysql:mysql /var/lib/mysql
chmod -R 750 /var/lib/mysql

#If there is no MySQL on the target server, MySQL needs to be initialized firstmysqld --initialize

#Start MySQL servicesystemctl start

(3) Verify migration

#Login MySQLmysql -uusername -p

#Check whether the database and table existSHOW DATABASES;
USE Database name;
SHOW TABLES;
SELECT * FROM Table name;

Notice:

Version compatibility: Ensure that the MySQL version of the source and target databases are compatible, especially the InnoDB version.
File permissions: Ensure that the data directory of the target database has appropriate file permissions, and that MySQL users can read and write these files.
Table structure consistency: Ensure that the table structure of the source database and the target database are completely consistent, including indexes, foreign keys, etc.
Data consistency: During the migration process, ensure data consistency and avoid data loss or corruption.
Backup: Before migration, be sure to back up the source and target databases to prevent accidents.
Manually delete unwanted data: If there is an unwanted data folder, you can manually exclude it before copying or delete it manually on the target server.

advantage:

Fast import speed: Directly copy data files, avoiding the time-consuming SQl import process.
Easy to operate: suitable for fast initialization of a new MySQL server.

shortcoming:

Large data files: all need to be copied, taking up more storage space and network bandwidth.
Manual deletion: The data file is large and needs to be copied all of it. If there is unwanted data, it needs to be manually deleted.

Summarize

Migration of MySQL databases is a complex task, involving multiple steps and links. In order to effectively reduce the risks that may arise during the migration process and ensure the security and consistency of data, sufficient preparation, detailed planning and rigorous implementation steps are essential.

Through these measures, the success rate of migration can be greatly improved and the entire process can be ensured smoothly.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.