Shulou(Shulou.com)05/31 Report--

How to understand mysql5. In the parallel replication, I believe that many inexperienced people can do nothing about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. Preface

Prior to version 5.5 and 5.5, the replication flowchart is as follows:

Process:

If dump thread, the main library of ①, finds that there is an update in binlog, then push the corresponding binlog events to the IO thread of slave

The IO thread of ② slave writes to its own relay log after receiving the newly generated events from master.

The SQL thread of ③ slave starts applying relay log and executes the corresponding update statement

Disadvantages:

① master in the case of multiple connections writing concurrently, slave has only one sql thread to update, which will seriously cause master-slave delay.

Second, concurrent replication

Officially known as enhanced multi-threaded slave (referred to as MTS), before the official mysql5.6 version, some people in Ali have implemented the concurrent replication function of slave, which is divided into database, table and row according to the granularity. Here we mainly talk about the official version.

1 > mysql5.6 parallel replication based on database

Set the number of worker threads for concurrent replication through slave_parallel_workers=n

Process:

If dump thread, the main library of ①, finds that there is an update in binlog, then push the corresponding binlog events to the IO thread of slave

The IO thread of ② slave writes to its own relay log after receiving the newly generated events from master.

Coordinator thread of ③ slave performs hash scheduling and assigns to worker thread according to database of binlog events

The ④ worker thread executes the corresponding update statement without affecting each other

Analysis:

① implements parallel replication of database, and the write improvement of slave is obvious in the case of multi-database updates, but if the master is mainly updated in a certain database, then this parallel replication is more difficult.

2 > mysql5.7 parallel replication based on database/logical_clock

The new parameter slave_parallel_type=DATABSE/LOGICAL_CLOCK sets the mode of parallel replication, where database is the same as 5.6. here we discuss the mode for LOGICAL_CLOCK.

Principle: by dividing the master commit into two phases, prepare and commit, while the transaction of prepare marks the same last_commited (the seq num of the current most recently committed transaction). Commit is implemented by marking the corresponding transaction with sequence num (incremented in turn), and the information committed by these groups is recorded in GTID.

Process:

If dump thread, the main library of ①, finds that there is an update in binlog, then push the corresponding binlog events to the IO thread of slave

The IO thread of ② slave writes to its own relay log after receiving the newly generated events from master.

The coordinator thread of ③ slave assigns the same last_commited in binlog events to worker thread

The ④ worker thread executes the corresponding update statement, which does not affect each other. For each transaction, after the execution of the seq=n is completed, the last_commited=n can execute.

This is almost the same as the concurrent operation of master, so for slave, this concurrent replication based on group commit has reached the concurrency of master.

For example:

The first number is last_commited and the second number is seq num

The order of execution on slave is

① trx1 trx2 trx3

② trx4

③ trx5 trx6

④ trx7

Among them, trx4 can be executed after the execution of trx1, and trx5 trx6; trx5 can be executed after the execution of trx2. Trx7.

PS: test whether GTID is enabled or not (for setting slave_parallel_type= LOGICAL_CLOCK from the slave library)

1 > 5.6-> 5.6

There is no problem with parallel replication based on database

2 > 5.7-> 5.7

When ① enables GTID, record the information such as last_commited and seq num submitted by the group into GTID

If GTID is not enabled in ②, mysql5.7 will generate an annonymous_gtid before each events submission, and then record the last_commited and seq num information submitted by the group, which can be replicated in parallel.

3 > 5.6-> 5.7

If GTID is not enabled in ①, mysql5.6, as master, will not perform set gtid_mode='annonymous' operation, so there will be no last_commited and seq num. The parallel replication setting of mysql5.7 slave will not expire (actually a single thread executes the update statement) and will not report an error.

When GTID is enabled in ②, if master does not have concurrent transactions and the parallel replication setting fails (actually a single thread executes update statements), replication will not report an error; if there are concurrent transactions, replication will make a direct error, as shown in the following figure (events of 5.6format reaches 5.7.This cannot be recognized when logical_clock is set)

The above is no problem in the case of slave_parallel_type=DATABASE.

To sum up, do not turn on parallel replication of logical_clock mode in the synchronization of 5.6muri-> 5.7m

After reading the above, you know how to understand mysql5. Do you have the method of parallel replication in? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.