Brief introduction of FAST data migration _mysql in Mysql fragmentation

Recommended reading: MySQL database The fastest migration method across the operating system

MySQL Backup and Migration data synchronization method

Operation Practice Background:

The Travelrecord table, defined as 10 slices, attempts to transfer 2 fragments from 10 fragments to the second MySQL, and completes the recording, requiring the fastest data migration practices, the shortest disruption of business time

Thinking one utilizes mysqldump:

Here we only target MySQL InnoDB engine, and configure the Bin-log database for analysis. Because it is the 10 fragments of the two fragments to migrate, in fact, is the database migration process, is the 10 database of two data migration to another machine; the shortest way to break business is to:

1 Prepare to mycat the new configuration file for quick switching of configuration files.

2 in the business peak period, such as the early morning, the use of mysqldump to carry out a full backup of the database (with--flush-logs);

3 Full-volume recovery of the second machine on a total of two fragmented databases;

4 Stop Mycat Server,

5 Stop the Mycat instance of the first machine (assuming the original fragment is in a MySQL instance)

6 Copy the Binlog file from the first machine to the second machine and use the MySQL Mysqlbinlog tool to recover the incremental data from the two databases.

7 Start the first Data instance, verify the migration of the two fragments of data correctness (mainly look at the number of records)

8 Replace the Mycat configuration file and start the Mycat instance

In this scenario, the time of business interruption is mainly the processing of Mysqlbinlog incremental data recovery.

Idea two use MySQL's MS mechanism to do cascade replication

Here two pieces such as the first fragment Db11, the second piece of db21, I db11 above to do a cascade master and slave, Db11-->db16, in the db21 above do a cascade master and slave db21->db26, so that the data real-time synchronization, interruption of business relatively short practice is:

1 Prepare to mycat the new profile for quick switching of configuration files.

2 in the business low peak period, such as early morning hours, start operation, stop Mycat server

3) DB11-DB16 main standby switch, db21-db26 main standby switch

4 Stop the Mycat instance of the first machine

5 in the main standby switching process, the old main db11, db21 above Binlog if there is a new DML, DDL operations, the Mysqlbinlog parse out, in the new main db16 and db26 above redo

6 Use the Mk-table-checksum tool to check the data consistency of the new master-slave db16->db11 and Db26->db21.

7 Replace the Mycat configuration file and start the Mycat instance

Well, about MySQL fragmentation in the fast data migration related knowledge to introduce here, I hope to help you, more about MySQL data migration knowledge Please login Www.jb51.net Learn more!