MySQL database migration

MySQL database migration

MySQL database migration (direct migration of data files)

In late October this year, the company's servers needed to be migrated, which involved MySQL database migration. Check the MySQL data file size, which is close to 60 GB (the actual data is not so much ). Due to the business needs on the server, the loss during server migration should be minimized. Therefore, the migration time starts at, and the migration time should be minimized.

There are three solutions before migration:

1. Directly export the database, copy the file to the new server, and import it on the new server.
2. Use MySQLMigrationTool in [MySQL GUI Tools.

3. Data Files and database table structure files are directly copied to the new server and mounted to the same configured MySQL service.

After I tested my computer using a virtual machine, I selected the third solution that took the least time. The following is a comparison of the three solutions:

Advantages of the first solution: data files are rebuilt to reduce the space occupied by data files.
Disadvantages of the first solution: long time occupation. (It takes a long time to import and export files, and the exported files must be transmitted over the network .)

Advantages of solution 2: unattended transmission after setup
Disadvantages of the second solution:

1. Configuration is cumbersome.
2. An exception occurs in the transmission network, so it cannot be found in time and will remain in the data transmission status and cannot be stopped. If you do not observe carefully, no exception will be found.
3. Transmission takes a long time relative to other fang.
4. It is difficult to continue transmission from the abnormal location after an exception.

Advantages of the third solution: short time occupation, resumable file transmission. The operation steps are few. (Most of the time is transmitted over the file Network)
Disadvantages of the third solution: Unknown problems may occur and are not found yet.

Next we will introduce the third solution d migration steps:

1. Ensure that the Mysql version is consistent, and the installation and configuration are basically consistent (note: the data files and database table structure files are both specified under the same directory of data)
2. Stop Mysql services on both sides (server A -- migration --> server B)
3. Delete all files in the data directory of server B Mysql
4. Copy the data directory of Mysql server A, except for ib_logfile.AndFiles other than. err are stored in server B data.
5. Start the Mysql service of server B and check for exceptions

After the migration is complete, the service starts normally and no other exceptions are found.

The data folder file list is as follows:

Note: after testing, the installation directory and data file directory of the source mysql may be different from the installation directory and data file directory of the target Mysql.

In this case, you only need to copy the dbname you want to move (such as pa and testdb), 'mysql', and 'ibdata1.

This article permanently updates the link address: