MGR同步复制验证

一 group_replication_consistency不同值含义介绍

官网地址：

MySQL :: MySQL 8.0 Reference Manual :: 20.9.1 Group Replication System Variables

eventual

Both RO and RW transactions do not wait for preceding transactions to be applied before executing. This was the behavior of Group Replication before this variable was added. A RW transaction does not wait for other members to apply a transaction. This means that a transaction could be externalized on one member before the others. This also means that in the event of a primary failover, the new primary can accept new RO and RW transactions before the previous primary transactions are all applied. RO transactions could result in outdated values, RW transactions could result in a rollback due to conflicts.

RO和RW事务在执行之前都不等待前面的事务应用完成（即，事务直接执行，不等待积压事务应用完成）。这是group_replication_consistency变量的默认值（也是8.0.14版本引入该系统变量之前组复制的默认行为）。RW事务不等待其他成员应用事务。这意味着设置该值的成员中的事务可以先于其他成员外部化。还意味着，在发生主节点故障转移时，新的主节点在旧主库事务被应用之前就可以接受新的RO和RW事务，这可能造成新的RO事务读取到陈旧的数据（因为之前旧主要节点中的最新数据还未同步到新的主节点）、新的RW事务可能由于冲突导致回滚（冲突认证检测会发现新的RW事务可能与来自旧主要节点的积压RW事务发生冲突）。

BEFORE_ON_PRIMARY_FAILOVER

New RO or RW transactions with a newly elected primary that is applying backlog from the old primary are held (not applied) until any backlog has been applied. This ensures that when a primary failover happens, intentionally or not, clients always see the latest value on the primary. This guarantees consistency, but means that clients must be able to handle the delay in the event that a backlog is being applied. Usually this delay should be minimal, but does depend on the size of the backlog.

新RO或RW事务在新当选的主节点应用完成来自旧的主节点的积压事务之前，会被保持(不应用，类似于处在等待状态，积压事务被应用完成之后，才会处理新的RO和RW事务)。这确保当主要节点故障转移发生时，客户端总是能查询到主节点的最新值。这保证了一致性，但意味着客户端必须能够在应用囤积的情况下处理延迟。通常这种延迟很小，但是实际延迟时间的长短取决于积压事务的大小。

before

A RW transaction waits for all preceding transactions to complete before being applied. A RO transaction waits for all preceding transactions to complete before being executed. This ensures that this transaction reads the latest value by only affecting the latency of the transaction. This reduces the overhead of synchronization on every RW transaction, by ensuring synchronization is used only on RO transactions. This consistency level also includes the consistency guarantees provided by BEFORE_ON_PRIMARY_FAILOVER.

RW事务在应用(applied)之前会等待所有前面的事务（积压事务）完成。RO事务在执行(executed)之前会等待所有前面的事务（积压事务）完成。这样使得事务仅通过牺牲响应延迟就可以确保读取到最新的值。

通过确保同步仅用于RO事务，这减少了每个RW事务的同步开销。此一致性级别还包括BEFORE_ON_PRIMARY_FAILOVER提供的一致性保证。

/*

实际上，只是确保了RO事务上的同步，对于RW事务来说，只是等待了它之前积压的事务完成，并不会等待它在所有的其他组成员上完成应用。

*/

after

A RW transaction waits until its changes have been applied to all of the other members. This value has no effect on RO transactions. This mode ensures that when a transaction is committed on the local member, any subsequent transaction reads the written value or a more recent value on any group member. Use this mode with a group that is used for predominantly RO operations to ensure that applied RW transactions are applied everywhere once they commit. This could be used by your application to ensure that subsequent reads fetch the latest data which includes the latest writes. This reduces the overhead of synchronization on every RO transaction, by ensuring synchronization is used only on RW transactions. This consistency level also includes the consistency guarantees provided by BEFORE_ON_PRIMARY_FAILOVER.

一个RW事务需要等待其在所有节点上的应用成功，才能执行成功。“AFTER”不影响RO事务。该级别确保了一个读写事务在本地提交成功后，后续的操作不管在哪个节点都能看到最新的数据。通过确保同步仅用于RW事务，这减少了每个RO事务的同步开销。此一致性级别还包括BEFORE_ON_PRIMARY_FAILOVER提供的一致性保证

before and after

A RW transaction waits for 1) all preceding transactions to complete before being applied and 2) until its changes have been applied on other members. A RO transaction waits for all preceding transactions to complete before execution takes place. This consistency level also includes the consistency guarantees provided by BEFORE_ON_PRIMARY_FAILOVER.

RW事务等待1）所有之前的事务在应用之前完成，2）直到其更改已应用于其他成员。RO事务在执行之前等待所有先前的事务完成。此一致性级别还包括BEFORE_ON_PRIMARY_FAILOVER提供的一致性保证。

二 同步复制验证

这里是在单主环境下进行的测试，数据库版本8.0.34。

2.1 准备工作

① 设置每个节点的group_replication_consistency

#以设置为eventual为例

在主从上设置group_replication_consistency

set global group_replication_consistency=EVENTUAL;

② 调小每个节点的wait_timeout参数，方便验证模拟，以便阻塞时快速报错，而不是一直等待，也kill不掉。

set global wait_timeout=20;

set global interactive_timeout=20;

/*

超时后会报类似如下错误：

3797 - Error while waiting for group transactions commit on group_replication_consistency= 'BEFORE'.

*/

③ 新打开一个会话，确认变量是否修改成功

show variables like '%consistency%';

show variables like '%wait_timeout%';

show variables like '%interactive_timeout%';

2.2 开始验证

2.3.1 验证eventual

节点1-会话1	节点2-会话1
	① lock table t1 read;
② insert into t1 values (1); //执行成功，不会产生等待
	③ select * from t1; //执行成功，返回结果为节点1-会话1插入前的状态，说明节点2读到的是老数据，说明是异步复制
	④ unlock tables;
	⑤ select * from t1; //执行成功，返回结果为节点1插入后的状态，说明此时数据才同步过来。

2.3.2 验证before

set global group_replication_consistency='before';

show variables like '%group_replication_consistency%';

2.3.2.1 验证lock table模拟延迟

2.3.2.1.1 验证从库RO

节点1-会话1	节点2-会话1
	① lock table t1 read;
② insert into t1 values (1); //执行成功，不会产生等待
	③ select * from t1;  //阻塞，如果超过wait_timeout设置的时间，报错：ERROR: 3797: Error while waiting for group transactions commit on group_replication_consistency= 'BEFORE'。阻塞说明实现了读一致性，以免读到老数据，但是读性能差些。 select * from t2; //读一个没锁定的表也出现阻塞，说明RO事务会等待前面的事务应用完才能读取新事务。 这看起来比较影响读的性能呀。
	④ unlock tables;
	⑤ select * from t1; //执行成功，返回结果为插入后的状态 select * from t2; //执行成功

2.3.2.1.2 验证从库RW

前提：

主从t1表里有一条id为1的数据。

节点1-会话1	节点2-会话1
	① set sql_log_bin=0; set global super_read_only=off; lock table t1 write;
② insert into t1 values (11); //没有阻塞，立即插入成功
	③ update t1 set id=id+10 where id=1; //出现阻塞，执行失败，报错：3797 - Error while waiting for group transactions commit on group_replication_consistency= 'BEFORE'.没有提示主键冲突。所以RW也需要等待先前的事务执行完才能执行事务
	④ unlock tables;
	⑤ select * from t1; //执行成功，返回结果为插入后的

2.3.2.2 验证delete大批数据

节点1-会话1	节点1-会话2	节点2-会话1
① delete from t1 ;//删除大批数据，这里删除五百万数据历时103秒
		② select count(*) from t1; //在①delete过程中，多次执行该sql，前几次很快（2秒钟）执行完毕，t1数据量是删除前的数据量，但发现有时会阻塞，阻塞了74秒，进程状态是Executing hook on transaction begin，不阻塞后，显示查询结果为0，读到的是① delete后的数据量。
		③ select * from t2; //① delete过程中，查询其他表，能正常查询
	④ insert into t1(id) values(6000003);  insert into t2(id) values(6); // ①执行期间，写入同一个表及其他表数据，都能正常写入
		⑤select * from t1 where id=6000003;//①执行期间，在节点2上查询同一个表，出现阻塞 select * from t2;  //①执行期间，在节点2上查询其他表，能看到数据同步过来

2.3.3 验证after

set global group_replication_consistency='after';

show variables like '%group_replication_consistency%';

2.3.3.1 验证lock table模拟延迟

2.3.3.1.1 验证从库RO

节点1-会话1	节点2-会话1	节点2-会话2
	① lock table t1 read;
② insert into t1 values (1); //产生阻塞
	③ select * from t1;  //在②阻塞期间，在节点2上执行查询sql，发现没阻塞，查询的是②插入之前的数据。
		④ select * from t2; //查询一个没锁的表，没有阻塞，这里比before友好。
⑤ insert into t2(id) values(4); //在②阻塞期间，在其他表写入数据也出现阻塞,进程state是waiting for handler commit
	⑥ unlock tables; //unlock后，节点1会话1的两个insert语句都立马执行成功
	⑦ select * from t1; //返回结果为插入后的状态

2.3.3.1.2 验证从库RW

前提：

主从t1表里有一条id为1的数据。

节点1-会话1	节点2-会话1
	① set sql_log_bin=0; set global super_read_only=off; lock table t1 write;
② insert into t1 values (11); //出现阻塞，超时没报错
	③ insert into t1 values (11); //写成功
④ insert into t2(id) values(5); //在②阻塞期间，在其他表写入数据也出现阻塞,进程state是waiting for handler commit
	⑤ unlock tables;  //unlock后，节点1会话1的两个insert语句都立马执行成功，节点1会话1 insert 11没报主键冲突的错
	⑥ select * from t1; //报错： 3796 - The option group_replication_consistency cannot be used on the current member state.发现节点2组复制停止，重启组复制，复制报主键冲突的错误。在节点2上删除11这条记录，再启动组复制，就正常了。

2.3.3.2 验证delete大批数据

验证after情况下delete一个五百万条数据大表延迟情况：

节点1-会话1	节点1-会话2	节点2-会话1
① delete from t1 ;//删除大批数据，这里删除五百万数据历时103秒
		② select count(*) from t1; //在①delete过程中，多次执行该sql，t1数据量一直是删除前的数据量，直到① delete完毕，这里变成0条数据，说明是同步复制，节点2删除完，节点1才执行成功。
		③ select * from t2; //查询其他表，能正常查询
	④ insert into t1(id) values(6000003);  insert into t2(id) values(6); // ①执行期间，写入同一个表及其他表数据，都能正常写入
		⑤select * from t1 where id=6000003; select * from t2;  //①执行期间以，在节点2上查询同一个表及其他表，没有阻塞，都能看到数据同步过来，说明after不会等待之前的事务执行完才执行新事务，不会影响其他事务写入数据。after读性能比before好，写性能没before好。

Lock table和delete大批数据区别：

在节点2上lock表，在节点1上不仅该表无法写入，其他表也无法写入。

如果节点2不lock表，只在节点1上删除大批数据，不会影响其他事务数据写入。

我测试的在节点2上stop group_replication，节点1上能正常写入数据。节点2启动后，节点1的数据能同步过来。

2.3.3.3 验证DDL

发现主库执行DDL期间：

① 从库未同步执行DDL时（show processlist还没看到这个DDL），主库其他表可以正常读写。

② 从库同步执行DDL时（show processlist能看到这个DDL），主库无法正常写入数据（

无法create database，也无法往其他表写入数据）

说明after比较影响写入性能。

2.3.4 验证before and after

set global group_replication_consistency='BEFORE_AND_AFTER';

show variables like '%group_replication_consistency%';

节点1-会话1	节点2-会话1	节点2-会话2
	① lock table t1 read;
② insert into t1 values (1); //产生阻塞
	③ select * from t1;  //节点1会话1 insert阻塞期间，在节点2上查询该表，出现阻塞现象，如果超过wait_timeout设置的时间，报错：ERROR: 3797: Error while waiting for group transactions commit on group_replication_consistency= 'BEFORE'。
		④ select * from t2; //读一个没锁定的表也出现阻塞，说明RO事务会等待前面的事务应用完才能读取新事务。
	⑤ unlock tables; //节点1会话1 insert阻塞期间，执行这个也阻塞了
	⑥ select * from t1; //节点1会话1 insert执行完之后，这里能查出数据了

验证了delete大批数据，在从库delete成功后，主库才提交。

三 如何选型

MySQL :: MySQL 8.0 Reference Manual :: 20.5.3.2 Configuring Transaction Consistency Guarantees

Scenario 1 you want to load balance your reads without worrying about stale reads, your group write operations are considerably fewer than your group read operations. In this case, you should choose AFTER.

Scenario 2 you have a data set that applies a lot of writes and you want to do occasional reads without having to worry about reading stale data. In this case, you should choose BEFORE.

Scenario 3 you want specific transactions in your workload to always read up-to-date data from the group, so that whenever that sensitive data is updated (such as credentials for a file or similar data) you want to enforce that reads always read the most up to date value. In this case, you should choose BEFORE.

Scenario 4 you have a group that has predominantly read-only (RO) data, you want your read-write (RW) transactions to be applied everywhere once they commit, so that subsequent reads are done on up-to-date data that includes your latest writes and you do not pay the synchronization on every RO transaction, but only on RW ones. In this case, you should choose AFTER.

Scenario 5 you have a group that has predominantly read-only data, you want your read-write (RW) transactions to always read up-to-date data from the group and to be applied everywhere once they commit, so that subsequent reads are done on up-to-date data that includes your latest write and you do not pay the synchronization on every read-only (RO) transaction, but only on RW ones. In this case, you should choose BEFORE_AND_AFTER.

读多写少，建议选择after。

写多读少，建议选择before。

希望始终读取最新的数据，建议选择before。

业务场景主要是只读的，您希望您的读写（RW）事务在提交后应用于所有节点，以便对包括您最近写入的最新数据进行后续读取，建议选择after。

业务场景主要是只读的，您希望您的读写（RW）事务始终读取最新数据，并在提交后应用于所有节点，以便对包括您的最新写入在内的最新数据进行后续读取，建议选择BEFORE_AND_AFTER。

--本篇文章参考自：

组复制常规操作-事务一致性保证 | 全方位认识 MySQL 8.0 Group Replication-CSDN博客

MySQL :: MySQL 8.0 Reference Manual :: 20.5.3.2 Configuring Transaction Consistency Guarantees

有关MySQL组复制的事务一致性参数理解 - 简书
MySQL :: MySQL 8.0 Reference Manual :: 20.9.1 Group Replication System Variables