MySQL Database migration

MySQL database migration (data file Direct migration)

In late October this year, the company's servers needed to be migrated, which involved MySQL database migrations. Look at the size of the MySQL data file, close to the size of 60G (the actual data does not work that much). Due to business needs on the server, minimize the loss of server migrations. So the migration time was chosen at 0 o'clock in the evening, and the time needed to minimize the migration was minimized.

There are three scenarios before the migration:

1. The database is exported directly, the files are copied to the new server and imported on the new server.
2. Use the Mysqlmigrationtool in MySQL GUI Tools.

3. The data File 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. Setup cumbersome.
2. In the transmission network abnormal, can not be found in time, and will remain in the data transmission status can not be stopped, if not carefully observed will not be found abnormal.
3. The transmission is longer relative to other fang.
4. After an exception it is difficult to continue the transfer from the location of 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 MySQL service on Server B to detect any exceptions

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

The Data folder file list is as follows:

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 '.

(EXT) http://www.cnblogs.com/advocate/archive/2013/11/19/3431606.html

MySQL database migration (RPM)