Describe the rapid data migration in MySQL sharding

Recommended reading: The fastest way to migrate MySQL databases across operating systems

mysql backup and migration data synchronization method

Operational Practice Background:

travelrecord table is defined as 10 shards. It attempts to transfer 2 shards of the 10 shards to the second MySQL and complete the record, requiring the fastest data migration and the shortest interruption time

Idea 1 Using mysqldump:

Here we will only analyze the database of mysql innodb engine and configure ES19en-ES20en. Because it is to migrate two of the 10 shards, in fact, it is the process of database migration, is to migrate two data in the 10 databases to another machine; The shortest way to interrupt business is:

1) Prepare the new mycat configuration file for backup, so that the configuration file can be switched quickly.

2) During business peak, such as early morning, use mysqldump to make full backup of the database (with flush-ES29en);

3) Carry out full recovery on the second machine for full backup of two sharded databases;

4) Stop the mycat server,

5) Stop the mycat instance of the first machine (assuming the original sharding is in one mysql instance)

6) Copy the binlog file of the first machine to the second machine, and use the mysqlbinlog tool of mysql to restore the incremental data of the two databases.

7) Start the first data instance to verify the correctness of the two shard data transferred (mainly based on the number of records)

8) Replace the configuration file for mycat and start the mycat instance

In this scenario, the business interruption time is mainly to process the incremental data recovery of mysqlbinlog.

Idea 2 uses ms's ms mechanism for cascading replication

There are two slices here, such as the first slice db11 and the second slice db21. I make a cascade of master slave, db11--, on db11 > db16, make a cascade of db21- above db21 > db26. In this way, when the time comes, the data will be synchronized in real time and the business interruption is relatively short.

1) Prepare the new mycat profile for backup, so that the configuration file can be switched quickly.

2) Start operation and stop mycat server during low peak hours, such as early morning hours

3) Carry out the main and backup switch of db11-db16 and the main and backup switch of db21-db26

4) Stop the instance of mycat for the first machine

5) During the switch between main and standby, if there are new dml and ddl operations in the above binlog of the old db11 and db21, then resolve them with mysqlbinlog and redo them on the new db16 and db26

6) Use the mk-ES103en-ES104en tool to check the new master and slave db16- > db11, db26 - > Data 1 of db21 is induced.

7) Replace the mycat configuration file and start the mycat instance

Ok, about MySQL shard in the fast data migration knowledge to you here, hope to be helpful to you, more about mysql data migration knowledge please login ES119en.ofstack.com to know more!