MySQL Database migration

MySQL database migration (data file Direct migration)

There are three scenarios before the migration:

1. Directly export the database, copy the file to the new server and import it on the new server.

2. Use the Mysqlmigrationtool in MySQL GUI Tools.

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

After I tested the virtual machine on my computer, I checked the third scenario with the least time-consuming. Here is a comparison of three scenarios:

The advantage of the first scenario is that the data file is rebuilt to reduce the footprint of the data file.

The disadvantage of the first scenario is that it takes a long time. (Importing and exporting takes a long time, and the exported file also travels over the network and takes a certain amount of time.) )

Advantages of the second scenario: Transport unattended after Setup is complete

Disadvantages of the second scenario:

1. Cumbersome setup.

2. Transmission network anomalies, can not be found in a timely manner, and will remain in the state of the data transmission can not be stopped, such as not careful observation will not be found abnormal.

3. Transmission is longer relative to other fang.

4. It is difficult to continue transmission from the abnormal position after the exception.

The advantage of the third scenario is that time is short and the file can be transmitted at a breakpoint. Fewer steps to operate. (Most of the time is in the file network transmission)

The disadvantage of the third scenario is that it may cause unknown problems and is not discovered for the time being.

Here are the third scenario D migration steps:

1. Ensure the MySQL version is consistent, the installation configuration is basically the same (note: both the data file and the library table structure file are specified in the same directory data)

2. Stop the MySQL service on both sides (a server-migration-->b server)

3. Delete all files in the data directory of the B server MySQL

4. Copy a server MySQL data directory except for Ib_logfile and. err files to B server data

5. Start the B server's MySQL service to detect if an exception occurred

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

Note: After testing, the source MySQL installation directory and data file directory can be inconsistent with the target MySQL installation directory and data file directory.

At this point, just copy the dbname you need to move (as above: PA, TestDB) and ' MySQL ' and ' ibdata1 '.

MySQL Database migration