MySQL5.7 new features: Lossless Replication lossless replication

three ways to replicate MySQL

1. Asynchronous asynchronous replication
2. Fully synchronous full synchronous replication
3. Semisynchronous Semi-synchronous replication

Asynchronous Replication

Principle: In asynchronous replication, master writes data to Binlog and Sync,slave request Binlog writes Relay-log and flush disk
Pros: Replication performance is best
Cons: Slave may lose transactions after master is hung out
Representative: MySQL native copy

Fully Synchronous replication

Principle: In full synchronous replication, master writes the data to Binlog and sync, all slave request Binlog write Relay-log and flush disk, and the log is completed and commit
Pros: Data is not lost
Cons: will block master session, poor performance, very dependent on the network
Representative: Mysql-cluster

semisynchronous Replication

• Normal semi-synchronous replication

Principle: In semi-synchronous replication, master writes data to Binlog and sync, and commits, and waits for an ACK. When at least one slave request Bilog is written to Relay-log and flush disk, an ACK is returned (no need to replay the log)
Pros: There is a risk of data loss (low)
Cons: Block master session, poor performance, very dependent on the network,
Representative: After commit, native semi-synchronous
Important: Since Master waits after the final commit phase of the three-segment commit, the other session of Master can see the commit transaction, so the data on Master is inconsistent with slave, and after master crash, Slave data loss

• Enhanced semi-synchronous replication (lossless replication)

Principle: In semi-synchronous replication, master writes data to Binlog and synchronizes, and then waits for an ACK. When at least one slave request Bilog is written to Relay-log and flush disk, an ACK is returned (no need to replay the log)
Pros: Data 0 is lost (provided that it is always lossless replication), good performance
Cons: Blocks the master session and relies heavily on the network
Rep: After sync, native half-sync
Important: Since Master waits after the second phase of the three commit sync Binlog is completed, the other session of master is not visible for this commit transaction, so the data on master is consistent with slave, and after master crash, Slave No data loss

Important Parameters

Parameters	Comment	Default Value	Recommended Values	whether dynamic
Rpl_semi_sync_master_wait_for_slave_count	At least n slave receive logs	1	1	Dynamic
Rpl_semi_sync_master_wait_point	Wait for Point	After_sync	After_sync	Dynamic
Rpl_semi_sync_master_timeout	Toggle replication of Timeout	10000 (10s)	(1s)	Dynamic
Rpl_semi_sync_master_enabled	Whether to turn on semi-sync	OFF	On	Dynamic
Rpl_semi_sync_slave_enabled	Whether to turn on semi-sync	OFF	On	Dynamic

How to open lossless replication

1 2 3 4 5 6

####### #semi Sync Replication settings######## plugin_dir=/usr/local/mysql/ Lib/plugin plugin_load = Span class= "string" > "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_ master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1 loose_rpl_semi_sync_master_timeout = 1000

practice is the only standard to test truth

How to test the above After_sync,after_commit
How to verify the correctness of the above principles

InnoDB Commit: Three-phase commit process

1 2 3

phase A. Wite prepare log --write XID Stage B. Write binlog phase C. Write commit log

Test Point

Master is a transaction waiting for Semi-sync ACK from Slave, what stage of the subsequent transaction is stuck in the a,b,c?

123456789Ten One A - - the - - - + - + A at - - - - - in - to + -

0,RC Mode 1. Semi-sync C-Phase wait Suppose that the set time-out=100000s, when the transaction one commits a large transaction, waits at the time of the write commit log (C-phase), So what stage is the second transaction at the time of the commit command? Is it stuck in Wite prepare log (phase a)? or write binlog (stage B)? or write commit log (phase C)  test: Semi-sync vs loss-less Semi-sync "Semi-sync" C-Phase wait0, turn on transaction 1, and then execute the Stop slave on the slave, making the timeout condition and blocking it. (Waiting for semi-sync ACK from slave) 1, in opening a transaction 2, transaction 2 inserts a special record (XXXXX). (Waiting for semi-sync ACK from slave) 2, on opening a transaction 3. 2.1, test case: This time,kill-9 mysqld, resulting in artificial MySQL crash 3, assuming that the card is in phase A, then transaction 3, must not see Transaction 1,2 write record (XXXXX), and after restarting MySQL, transaction 2 will not commit. 4, assuming that the card is in phase C, then transaction 3, it is certainly possible to see Transactions 1,2 Write Records (XXXXX).  Tested:1, is stuck in C-phase, that is, transaction 3 is can see transaction 1, Transaction 2. after 2,mysql crash restart, transaction 1, the DML for transaction 2 has been submitted successfully, stating that it is not stuck in phase a  "Loss-less Semi-sync" phase B wait 0, turn on transaction 1, and then execute the Stop slave on the slave, making the timeout condition and blocking it. (Waiting for semi-sync ACK from slave) 1, in opening a transaction 2, transaction 2 inserts a special record (XXXXX). (Waiting for semi-sync ACK from slave) 2, when opening a transaction 3 3, assuming that the card is in phase A, then transaction 3, must not see Transaction 1,2 write record (XXXXX), and after restarting MySQL, transaction 2 will not commit: 4, assuming that the card is in stage B, then transaction 3, must be able to see Transactions 1,2 Write Records (XXXXX), and after restarting MySQL, transaction 1,2 commits: 5, assuming that the card is in phase C, then transaction 3, it must be possible to see transaction 3 Write Records (XXXXX).  Tested:1, is stuck in the B stage, that is, transaction 3, neither see the commit content of transaction 1, also see the commit content of transaction 2, and after restarting MySQL, transaction 1,2 have been submitted. after 2,mysql crash restart, transaction 1, the DML for transaction 2 has been submitted successfully, stating that it is not stuck in phase a.

PerformancePerformance Comparison of Semi-sync vs lossless Semi-sync

According to the above test, it can be learned that lossless only card in the B stage, the ordinary semi-sync is stuck in the C stage.
Lossless performance is much better than ordinary semi-sync, ie (after_sync better than After_commit)
Because the lossless card can accumulate transactions during phase B, group commit can be performed in phase C.
Ordinary Semi-sync, stuck in the C stage, the transaction has been commit, and there is no accumulation process.

cap Theory

Consistency "C"
Availability "A"
Partition tolerance "P"
Theory: CAP Three cannot be combined, must sacrifice a

Partition, there is a certain existence, not you want to do not. So, there are only two combinations left.

• CP Sacrificing availability

This approach is to preserve strong consistency, sacrificing availability
Case: Rpl_semi_sync_master_timeout can be set to an infinite value, such as: 100 days, then master and slave are consistent, but the usability is greatly discounted

• AP Sacrifice Consistency

This approach is to preserve high availability at the expense of consistency
Example: This is the case with native asynchronous replication. Can be switched quickly, but consistency is not guaranteed

MySQL5.7 new features: Lossless Replication lossless replication