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

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.