Brief description of fast data migration in MySQL sharding, brief description of migration in mysql

Brief description of fast data migration in MySQL sharding, brief description of migration in mysql

Recommended: the fastest way to migrate MySQL databases across operating systems

Mysql backup and Migration Data Synchronization Method

Operation Background:

The travelrecord table is defined as 10 shards. It tries to transfer two of the 10 shards to the second MySQL instance and complete the record. The fastest data migration method is required, and the service interruption time is the shortest.

Idea 1: Use mysqldump:

Here, we only analyze mysql innodb engine databases with bin-log configuration. Because it is to migrate two of the 10 shards, it is actually the database migration process, that is, to migrate two of the 10 databases to another machine; the shortest way to interrupt the business is:

1) prepare a new mycat configuration file for backup to quickly switch the configuration file.

2) use mysqldump for full database backup (with -- flush-logs) in the early hours of the Business peak period );

3) perform full recovery on the second machine after the full backup of the two sharded databases;

4) Stop the mycat server,

5) Stop the mycat instance of the first machine (assuming that the original shard is in a mysql instance)

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

7) start the first data instance and verify the correctness of the two migrated parts (mainly based on the number of records)

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

In this solution, the service interruption time is mainly used to process the incremental data recovery of mysqlbinlog.

Idea 2 cascade replication using mysql's ms Mechanism

Here, we have two parts, for example, the first shard db11 and the second shard db21. On db11, I have another cascade master and slave, db11 --> db16, create a cascade master-slave db21-> db26 on db21. In this way, real-time data synchronization will interrupt the business:

1) prepare a new mycat configuration file for backup to quickly switch the configuration file.

2) during off-peak hours, for example, early morning, start to stop the mycat Server

3) db11-db16 Master/Slave switch, db21-db26 Master/Slave Switch

4) Stop the mycat instance of the first machine

5) during the master-slave switchover, if new dml and ddl operations exist in the binlog on the old master db11 and db21, mysqlbinlog is used for parsing, redo it on the new master db16 and db26.

6) use the mk-table-checksum tool to check the data consistency of the new master-slave db16-> db11, db26-> db21.

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

Now, we will introduce you to quick data migration in MySQL sharding. I hope to help you. For more information about mysql Data Migration, log on to www.jb51.net!

Articles you may be interested in:

• The fastest way to migrate MySQL databases across operating systems
• Mysql backup and Migration Data Synchronization Method
• Share data migration stored procedures in mysql
• Considerations for MySQL data migration-direct replacement of data Directories
• How to migrate massive data in MySQL
• Detailed steps for MySQL database migration data folder location
• Perl accesses MSSQL and migrates it to a MySQL database script instance.
• Use perl to split data tables (mysql) and migrate data instances