轉自 https://www.cnblogs.com/syksky/p/9429206.html

MySQL 5.7.22
啟用增強半同步複製

MySQL對該引數值的描述

Semisync can wait for slave ACKs at one of two points,
AFTER_SYNC or AFTER_COMMIT. AFTER_SYNC is the default value.
AFTER_SYNC means that semisynchronous replication waits just after the 
binary log file is flushed, but before the engine commits, and so 
guarantees that no other sessions can see the data before replicated to 
slave. AFTER_COMMIT means that semisynchronous replication waits just 
after the engine commits. Other sessions may see the data before it is replicated, even though the current session is still waiting for the commit 
to end successfully.

From: Source Code mysql-5.7.22\plugin\semisync\semisync_master_plugin.cc

Replication: Semisynchronous replication master servers now use a different wait point by default in communicating wih slaves. This is the point at which the master waits for acknowledgment of transaction receipt by a slave before returning a status to the client that committed the transaction. The wait point is controlled by the new rpl_semi_sync_master_wait_point system variable. These values are permitted:

AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.

AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

For older versions of MySQL, semisynchronous master behavior is equivalent to a setting of AFTER_COMMIT.

The replication characteristics of these settings differ as follows:

With AFTER_SYNC, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master.

In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date.

With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.

If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.

The new wait point is a behavior change, but requires no reconfiguration. The change does introduce a version compatibility constraint because it increments the semisynchronous interface version: Servers for MySQL 5.7.2 and up do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7.2 and up.

From：https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html

測試表

Create Table: CREATE TABLE `syk`.`t` (
`t` datetime DEFAULT NULL,
`a` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;insert into syk.t (t,a) values(now(),1);commit;

準備3個會話視窗，A,B,C
A視窗連線主庫，負責更改主庫的引數及表更新操作
B視窗負責監視主庫資料的變化，執行下述命令

while (true);domysql -uroot -pmysql-001 -e "select now(),t,a from syk.t" -s --skip-column-names 2>&1 | grep -v Warningsleep 1done

C視窗負責從庫，slave的重啟操作，模擬從庫的slave停止

AFTER_SYNC
5.7.2及以上預設值

A:

mysql> show variables like '%rpl%point%';+---------------------------------+------------+| Variable_name | Value |+---------------------------------+------------+| rpl_semi_sync_master_wait_point | AFTER_SYNC |+---------------------------------+------------+mysql> show global status like '%rpl%master%status%';+-----------------------------+-------+| Variable_name | Value |+-----------------------------+-------+| Rpl_semi_sync_master_status | ON |+-----------------------------+-------+mysql> select * from syk.t;+---------------------+------+| t | a |+---------------------+------+| 2018-08-02 14:57:15 | 1 |+---------------------+------+

B:
此時B視窗一直在查詢表資料
C:
停止slave

A:

mysql> update syk.t set t=now() where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> commit;
Query OK, 0 rows affected (10.00 sec)
mysql> select * from syk.t;+---------------------+------+| t | a |+---------------------+------+| 2018-08-02 15:21:51 | 1 |+---------------------+------+1 row in set (0.00 sec)

A視窗的commit延遲了10秒才提交成功。受rpl_semi_sync_master_timeout引數影響，預設100000毫秒，即10秒。

B：

2018-08-02 15:21:50 2018-08-02 14:57:15 12018-08-02 15:21:51 2018-08-02 14:57:15 1 <=========2018-08-02 15:21:52 2018-08-02 14:57:15 12018-08-02 15:21:53 2018-08-02 14:57:15 12018-08-02 15:21:54 2018-08-02 14:57:15 12018-08-02 15:21:55 2018-08-02 14:57:15 12018-08-02 15:21:56 2018-08-02 14:57:15 12018-08-02 15:21:57 2018-08-02 14:57:15 12018-08-02 15:21:58 2018-08-02 14:57:15 12018-08-02 15:21:59 2018-08-02 14:57:15 12018-08-02 15:22:00 2018-08-02 14:57:15 12018-08-02 15:22:01 2018-08-02 14:57:15 12018-08-02 15:22:02 2018-08-02 14:57:15 12018-08-02 15:22:03 2018-08-02 14:57:15 12018-08-02 15:22:04 2018-08-02 14:57:15 12018-08-02 15:22:05 2018-08-02 15:21:51 1 <=========

可以看到，主庫的其他會話需要等待提交顯示OK之後，才能看得到。

AFTER_COMMIT
5.7.2之前（不含5.7.2）的版本，的版本預設行為

A:

set global rpl_semi_sync_master_wait_point=AFTER_COMMIT;

C:
重啟slave

A:

mysql> show variables like '%rpl%point%';+---------------------------------+--------------+| Variable_name | Value |+---------------------------------+--------------+| rpl_semi_sync_master_wait_point | AFTER_COMMIT |+---------------------------------+--------------+1 row in set (0.00 sec)
mysql> show global status like '%rpl%master%status%';+-----------------------------+-------+| Variable_name | Value |+-----------------------------+-------+| Rpl_semi_sync_master_status | ON |+-----------------------------+-------+1 row in set (0.00 sec)
mysql> select * from syk.t;+---------------------+------+| t | a |+---------------------+------+| 2018-08-02 15:21:51 | 1 |+---------------------+------+1 row in set (0.00 sec)

B:
此時B視窗一直在查詢表資料
C:
停止slave

A:

mysql> update syk.t set t=now() where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> commit;
Query OK, 0 rows affected (10.01 sec)
mysql> select * from syk.t;+---------------------+------+| t | a |+---------------------+------+| 2018-08-02 15:38:42 | 1 |+---------------------+------+1 row in set (0.00 sec)

A視窗的commit同樣延遲了10秒才提交成功。受rpl_semi_sync_master_timeout引數影響，預設100000毫秒，即10秒。

B：

2018-08-02 15:38:41 2018-08-02 15:21:51 12018-08-02 15:38:42 2018-08-02 15:21:51 12018-08-02 15:38:43 2018-08-02 15:38:42 1 <=========2018-08-02 15:38:44 2018-08-02 15:38:42 12018-08-02 15:38:45 2018-08-02 15:38:42 12018-08-02 15:38:46 2018-08-02 15:38:42 12018-08-02 15:38:47 2018-08-02 15:38:42 12018-08-02 15:38:48 2018-08-02 15:38:42 12018-08-02 15:38:49 2018-08-02 15:38:42 12018-08-02 15:38:50 2018-08-02 15:38:42 12018-08-02 15:38:51 2018-08-02 15:38:42 12018-08-02 15:38:52 2018-08-02 15:38:42 12018-08-02 15:38:53 2018-08-02 15:38:42 12018-08-02 15:38:54 2018-08-02 15:38:42 12018-08-02 15:38:55 2018-08-02 15:38:42 12018-08-02 15:38:56 2018-08-02 15:38:42 1

雖然A的提交延遲了10秒，但其他會話已經看到已經提交的資料。

總結：
實驗的對比，可以發現與原始碼中的描述是一致的。
AFTER_SYNC意味著半同步複製，在binary log被flush之後，在儲存引擎commit前進入等待，這可以保證資料在被複制到從庫前不被其他會話可見；
AFTER_COMMIT意味著半同步複製在儲存引擎commit之後進入等待，儘管發起commit的會話還未收到commit成功的提示，其他的會話已經可以看到commit後的資料。
rpl_semi_sync_master_wait_point，該引數也正如其字面意思，master在哪個點開始等待。

MySQL增強半同步引數rpl_semi_sync_master_wait_point值AFTER_SYNC和AFTER_COMMIT

相關文章