MySQL replication/mysql master-slave replication principle

The content is organized according to leader training and sharing

I'm the split line ****************************************.

mysql5.5 replication

Approximate process:

One. First execute a thing in master, commit (MySQL default is set to Auto commit),

Two. After committing to write to 2 files, one is to write data to datafile (this data is consistent, for example, there is a data unit start data is 1, in the middle through the change of 2,3, finally 4, then write the final result 4), one is to write things bin_log (binary log), Exactly said not necessarily is the thing, specifically divides into 3 kinds, 1.statement-based logging, 2.row-based loggging,3.mixed logging.

Binlog is a lot of event, Binlog opening is 4 empty, the specific role is unknown (according to leader is the sign of the beginning of the event). After 4 slots There is a representation of format Description E (FDE), which writes data to Binlog in things, and a thing can contain n event,binlog to end with a XID flag. (is toconfirm:xid an end symbol for a thing or a symbol for the end of Binlog content?) ）

Every event has a check bit, if the network transmission problems can be resumed, such as a breakpoint,

Three. Master the Binlog file through a line called dump path to Slave, slave will receive an event written to a file called Relay log (relay log), and then according to relay log with a call applier something to commit ( Write datafile, and Binlog)

The Binlog and relay log content is not always the same, because you can set the eventsize if there is a thing on master that writes to the Binlog so that the size exceeds the eventsize (can be large, because Binlog is stored in things, Instead of an event unit), this is passed to slave, and when relay log reaches the event size of the slave end, the new file is written to the subsequent received event.

Unsafe replication, if the assignment of the Rand class in the thing is safe, because the event record contains the seed of Rand, but if there is now () such assignment is unsafe, because there is a time difference, this time statement-based Logging will be wrong, you can use mixed logging.

Problems with the mysql5.5 version of replication:

Master-Slave Data differences: the operation of the master can be multi-parallel, but to from the write is actually a number of parallel operations to Binlog, and then serially sent to the slave, so from the data before the full execution of the Binlog with the main difference, and the efficiency of parallel to serial execution may be very poor. The solution is to consider parallelism, or semisynchronize, from the operation, but if parallel at the same time, the efficiency of the master may be reduced because it needs to perform its own parallel operation and send the event in Binlog in parallel. Semisychronize is appropriate to reduce the concurrency of the master, waiting for the slave, but if the response from the slave is too slow to seriously affect the master's response, the master will continue to perform his own concurrency.

I'm the split line ****************************************.

MySQL 5.6 Replication

One. Use Gtid instead of the Pos of bin log to identify the event. Because if there is a master-slave structure such as master-->slave1 (POS)--->slave2 (POS), Master-->slave3 (pos150), if master suddenly falls down, Slave3 becomes master and slave, Slave2 will not be able to distinguish the next event position, Slave2 (POS80) is only relative slave1 Binlog in the event POS

Two. Multi-Threading using MTS (multiple thread slave) in schema, because operations on different schemas do not affect each other, so you can parallel

I'm the split line ****************************************.

Groupcommit (group commit), MySQL multiple commits and groups, the purpose is to improve the writing speed, Binlog and Groupcommit will have problems

Toconfirm (MySQL bug 70370)

I'm the split line ****************************************.

Semisynthronize process

The meaning of semi-synchronous eg: the user has performed the insertion in the Lord, the data has not been updated, the user's Lord can find new data, from the top but not found.

In order to solve similar problems using Semisynthronize, execute the user's action commit first do not execute, write to Binlog, and then add the Binlog new event dump to slave, slave accept good after the main ACK, Then the Lord commits.

MySQL replication/mysql master-slave replication principle