MySQL半同步after_sync与after_commit

前言:

在MySQL半同步复制中，有两种日志同步的ACK模式，分别是after_sync与after_commit，本文主要介绍两种模式下，主从同步数据的一致性情况。

测试环境:

半同步配置参数:

rpl_semi_sync_master_timeout=10000000rpl_semi_sync_master_wait_for_slave_count=1rpl_semi_sync_master_wait_point=after_sync|after_commit

after_commit模式：

after_commit:主库写入事务到binlog以及同步从库，sync binlog,并且提交事务到存储引擎，在提交之后主库等待从库接受到事务的确认，在接受到确认之后，源端返回提交完成到客户端。

在after_commit下，提交事务的客户端需要等待确认从库已经接收到事务才能返回，但由于提交到存储引擎是在确认从库之前完成，所以，其他客户端将比提交事务的客户端更早的看到提交事务的数据，在发生故障切换时，在对于已经提交存储引擎但还没有确认从库已经提交的事务，其他客户端可能会出现与他们在源上看到的数据相关的数据丢失。

提交流程:client-->execute sql-->wrtie redolog-->write binlog-->innodb storage commit-->wait ACK-->client receive OK。

暂停从库io_thread。

stop slave io_thread;​root@localhost : (none)06:19:40>stop slave io_thread;Query OK, 0 rows affected (0.01 sec)​root@localhost : (none)06:19:55>show slave status\G* 1. row * Slave_IO_State:     Master_Host: 192.168.1.110    Master_User: rep    Master_Port: 3306  Connect_Retry: 60Master_Log_File: nlog.000010   Read_Master_Log_Pos: 377 Relay_Log_File: mysql-relay-bin.000018  Relay_Log_Pos: 500 Relay_Master_Log_File: nlog.000010      Slave_IO_Running: No     Slave_SQL_Running: YesReplicate_Do_DB:    Replicate_Ignore_DB:     Replicate_Do_Table: Replicate_Ignore_Table:Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:      Last_Errno: 0     Last_Error:    Skip_Counter: 0   Exec_Master_Log_Pos: 377Relay_Log_Space: 707Until_Condition: None Until_Log_File:   Until_Log_Pos: 0    Master_SSL_Allowed: No    Master_SSL_CA_File:     Master_SSL_CA_Path: Master_SSL_Cert:      Master_SSL_Cipher:  Master_SSL_Key:  Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No  Last_IO_Errno: 0  Last_IO_Error:  Last_SQL_Errno: 0 Last_SQL_Error:   Replicate_Ignore_Server_Ids:Master_Server_Id: 3306    Master_UUID: 2b3039c9-57fa-11eb-b504-000c29ed797a      Master_Info_File: mysql.slave_master_info      SQL_Delay: 0   SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates    Master_Retry_Count: 86400    Master_Bind:Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:  Master_SSL_Crl:     Master_SSL_Crlpath:     Retrieved_Gtid_Set: 2b3039c9-57fa-11eb-b504-000c29ed797a:8346311     Executed_Gtid_Set: 1b80feab-4aa6-11ec-9a60-000c29a6e7be:1-10,2b3039c9-57fa-11eb-b504-000c29ed797a:1-8346311,3a59d149-d4b8-11eb-8cf6-000c29a6e7b4:1-10,a0a3d4b2-fff8-11eb-a420-000c29a6e7be:1-27  Auto_Position: 1  Replicate_Rewrite_DB:    Channel_Name:     Master_TLS_Version:

插入数据。

begin;insert into sbtest1(k,c,pad,d) values(1,'a','ziniu',1);commit;

可以看到commit被挂起，没有返回。

主库其他会话查询该条数据，可以查到数据。

select * from sbtest1 where pad='ziniu';​root@localhost : sbtest06:27:17>select * from sbtest1 where pad='ziniu';+--------+---+---+-------+------+| id     | k | c | pad   | d    |+--------+---+---+-------+------+| 170006 | 1 | a | ziniu |    1 |+--------+---+---+-------+------+1 row in set (0.05 sec)

 但在从库查询不到该条数据。

root@localhost : sbtest06:27:27>select * from sbtest1 where pad='ziniu';Empty set (0.06 sec)

这时候，如果发生主从故障切换，就会出现有些客户端在源上看到相关的数据丢失，因为故障切换前，在主库查到"ziniu"这条数据，但在切换之后却查不到该条数据，并且对于写入数据的客户端是没有收到commit提交完成的确认，所以，after_commit下主从出现了数据丢失，主从两边不是强一致性。

after_sync模式：

after_sync(默认):主库写入事务到binlog以及同步从库并且sync binlog to disk，主库同步后等待从库接受到事务的确认，在等到从库的确认之后，主库提交事务到存储引擎并且返回客户端。

在after_sync下，全部客户端同一时间看到已经提交的事务，因为是在确认从库已经接收到事务之后再提交存储引擎的，所以全部客户端是同一时间看到已经提交事务的数据；此外，在发生故障切换时，主库全部已经提交的事务已经同步到从库的relay log，从库的数据是无损的。

提交流程:client-->execute sql-->wrtie redolog-->write binlog-->wait ACK-->innodb storage commit-->client receive OK。

暂停从库io_thread。

root@localhost : sbtest06:30:32>stop slave io_thread;Query OK, 0 rows affected (0.00 sec)​root@localhost : sbtest06:59:44>root@localhost : sbtest06:59:45>root@localhost : sbtest06:59:45>root@localhost : sbtest06:59:45>show slave status\G* 1. row * Slave_IO_State:     Master_Host: 192.168.1.110    Master_User: rep    Master_Port: 3306  Connect_Retry: 60Master_Log_File: nlog.000010   Read_Master_Log_Pos: 3013 Relay_Log_File: mysql-relay-bin.000023  Relay_Log_Pos: 808 Relay_Master_Log_File: nlog.000010      Slave_IO_Running: No     Slave_SQL_Running: YesReplicate_Do_DB:    Replicate_Ignore_DB:     Replicate_Do_Table: Replicate_Ignore_Table:Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:      Last_Errno: 0     Last_Error:    Skip_Counter: 0   Exec_Master_Log_Pos: 3013Relay_Log_Space: 2099Until_Condition: None Until_Log_File:   Until_Log_Pos: 0    Master_SSL_Allowed: No    Master_SSL_CA_File:     Master_SSL_CA_Path: Master_SSL_Cert:      Master_SSL_Cipher:  Master_SSL_Key:  Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No  Last_IO_Errno: 0  Last_IO_Error:  Last_SQL_Errno: 0 Last_SQL_Error:   Replicate_Ignore_Server_Ids:Master_Server_Id: 3306    Master_UUID: 2b3039c9-57fa-11eb-b504-000c29ed797a      Master_Info_File: mysql.slave_master_info      SQL_Delay: 0   SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates    Master_Retry_Count: 86400    Master_Bind:Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:  Master_SSL_Crl:     Master_SSL_Crlpath:     Retrieved_Gtid_Set: 2b3039c9-57fa-11eb-b504-000c29ed797a:8346311-8346318     Executed_Gtid_Set: 1b80feab-4aa6-11ec-9a60-000c29a6e7be:1-10,2b3039c9-57fa-11eb-b504-000c29ed797a:1-8346318,3a59d149-d4b8-11eb-8cf6-000c29a6e7b4:1-10,a0a3d4b2-fff8-11eb-a420-000c29a6e7be:1-27  Auto_Position: 1  Replicate_Rewrite_DB:    Channel_Name:     Master_TLS_Version: 1 row in set (0.00 sec)

插入数据。

begin;insert into sbtest1(k,c,pad,d) values(1,'a','niuniu',1);commit;

可以看到commit被挂起,没有返回。

主库其他会话查询该条数据，并没有查到数据。

select * from sbtest1 where pad='niuniu';root@localhost : sbtest07:02:06>select * from sbtest1 where pad='niuniu';Empty set (0.05 sec)​root@localhost : sbtest07:02:06>root@localhost : sbtest07:02:07>

在从库也查询不到该条数据。

root@localhost : sbtest07:03:59>select * from sbtest1 where pad='niuniu';Empty set (0.06 sec)​root@localhost : sbtest07:04:02>

这时候，如果发生主从故障切换，对于"niuniu"这条数据，只是一条客户端提交失败的数据，不存在数据的丢失，所以，after_sync下主从两边数据是强一致性​。

总结:

对于生产环境半同步的配置，建议配置after_sync模式，在after_sync模式下，​才能保证主从数据的强一致性。