MySQL replication principle/library-based multi-threaded replication principle/BLGC-based multi-threaded replication principle

MySQL replication principle/library-based multi-threaded replication principle/BLGC-based multi-threaded replication principle

Single thread master-slave replication:

From the library to the main library to request Binlog, and binlog into their own relaylog, from the library redo Binlog inside the SQL,

This is done mainly by the following three threads.

Dump thread: On the main library, send Binlog

IO thread: On slave, receive, dump, request Binlog

SQL thread: On slave, redo Binlog

Library-based multi-threaded replication principle:

From the library to the main library to request Binlog, and binlog into their own relaylog, from the library redo Binlog inside the SQL,

This is done mainly by the following three threads.

Dump thread: On the main library, send Binlog

IO thread: On slave, receive, dump, request Binlog

SQL thread: On slave, read Binlog and assign Binlog to work thread (allocation principle, to determine whether a parallel execution transaction has the same database)

Work thread: Executes Binlog, can have multiple

Multithreaded replication

mysql5.7 is based on the BLGC multi-threaded replication principle, and there is no conflict between transactions in the prepare phase.

In mysql5.7 's Binlog, two new fields were added to annotate which transactions can be executed in parallel, and Binlog's write order

Last_committed: Transaction commit number, transactions within the same group, number same, can be executed in parallel.

Sequence_number:binglog the Write order, the user ensures that the Binlog order of Master is consistent with the binlog order of the Slave.

Some problems in multi-threaded replication

1.binlog in the order of the group of concurrent redo, if a previous group of Binlog a SQL execution failed, the whole group of SQL rollback, slave replication is paused?

If one of the SQL execution fails in a group, the entire group of SQL will be rolled back.

Whether the entire slave will be suspended at this time, pending verification

2. How to ensure that the order of Binglog on Binlog and slave on Master is consistent in parallel replication

In order to achieve parallel replication in mysql5.7, two fields were added to the Binlog log specifically, Last_committd,sequence_number,

In parallel replay by group on Slave, you need to wait for all SQL execution in the group to complete, then sort by sequence_number in the flush phase,

This guarantees that the write order of Binlog on Master is the same as the write order of Binlog on slave.

Resources

Http://www.tuicool.com/articles/EvQjEr

mysql5.7 About parallel replication:

http://blog.itpub.net/28218939/viewspace-1975822/

http://blog.itpub.net/28218939/viewspace-1975856/

http://www.ttlsa.com/mysql/mysql-5-7-enhanced-multi-thread-salve/

Http://dev.mysql.com/doc/refman/5.6/en/replication-implementation-details.html

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1788704

MySQL replication principle/library-based multi-threaded replication principle/BLGC-based multi-threaded replication principle