MySQL组复制学习笔记(基于MySQL 8+) -- 部署篇

组复制组内每个数据库服务器实例运行在单独的硬件主机上，这是推荐的部署方式，但这不是强制的。组复制首先需要安装3个MySQL数据库, MySQL8.0内建提供了组复制插件, 安装没有特殊要求, 本文不介绍。组复制最少需要3个MySQL服务器实例。

2.1. 组复制的需求与限制

组复制需求:

1). 数据必须放在InnoDB存储引擎, 禁用除InnoDB之外的其他存储引擎, 对应的配置项: disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
2). 每个表必须有主键或者有不允许空值的唯一键；对应的配置项：sql_require_primary_key=ON 或者 REQUIRE_TABLE_PRIMARY_KEY_CHECK=ON.
3). 稳定高新能的网络环境.网络对于组复的稳定性和性能至关重要，组内的每个服务器应该物理部署得尽可能靠近, 从MySQL 8.0.14开始, 支持IPv4/IPv6网络架构, 或者两种混合。
   (阿里的Oceanbase提到的两地三中心的部署是否靠谱, 或者只是为了迎合金融行业的监管要求?)
4). 必须开二进制日志，              对应配置项：log-bin[=log_file_name]
5). 二进制记录其来自其他成员的跟新, 对应配置项: log-slave-updates = ON 
6). 二进制日志必须使用行模式,       对应配置项: binlog-format = ROW 
7). 关闭二进制日志的校验(checksum), 对应配置项: binlog-checksum = NONE 
8). 开启GTID,                       对应配置项: gtid_mode = ON
9). 复制信息必须存放在数据库表内,   对应配置项: master_info_repository=TABLE 和 relay_log_info_repository=TABLE
10). 写集"write set"的哈希值使用XXHASH64算法, 对应配置项: transaction-write-set-extraction = XXHASH64
11). 设置binlog_transaction_dependency_tracking = WRITESET_SESSION可以提高组成员的性能
12). 每个组成员的default-table-encryption值必须一致；
13). 每个组成员的lower-case-table-names值必须一致, 需要设置为1(这是非默认值);
14). 开启多线程应用(Multithreaded Appliers), 对应配置项:
  slave_parallel_workers = (线程数, 根据情况配置大于0, 最大1024)
  slave_preserve_commit_order = 1   #确保应用事务的顺序与发起服务器一致
  slave_parallel_type=LOGICAL_CLOCK 

组复制限制

1). 组复制不允许使用--upgrade=MINIMAL升级选项, 否者会无法启动;
2). certification过程不支持"Gap Locks", 如果不需要可重复读(REPEATABLE_READ), 建议使用READ_COMMITTED事务隔离级别, 对应配置项: transaction-isolation='READ-COMMITTED'
3). certification过程不支持表锁, 如LOCK TABLES t1 ...;
4). 不支持二进制日志校验(checksum), 与上面第7点同;
5). 不支持SERIALIZABLE事务隔离级别；
6). 不允许不同成员同时对一个对象同时执行DML和DDL操作;
7). 多主模式不允许外键级联约束(Foreign Keys with Cascading Constraints), 多主模式下建议设置group_replication_enforce_update_everywhere_checks=ON;
    单主模式不存这个限制;置为OFF可以提高性能？
8). 多主模式下, SELECT .. FOR UPDATE将导致死锁；
9). 不允许使用Replication Filters(不允许设置 replicate-do-*|replicate-ignore-*?)；
10). MySQL 8.0.16 and MySQL 8.0.17以前版本不支持TLSv1.3加密连接, MySQL 8.0.18后开始支持；
11). In releases before MySQL 8.0.20, you cannot initiate a cloning operation manually if the operation involves a group member on which Group Replication is running. 
12). 单个复制组最多允许9个成员(MySQL服务器), 到达9个成员后无法继续添加；
13). 限制事务的大小, 如果单个事务的大小太大, 事务在5秒的时间窗口无法通过网络在组成员间拷贝, 成员将被认为故障而驱逐出组。为了避免这个问题:
  - 可以配置group_replication_member_expel_timeout变量值，设定在5秒的检测窗口后，再等待额外的多少秒后才判定为失败节点，驱逐出组，参数默认为0，即5秒后立刻驱逐；
  - 最好还是想办法减小事务的大小，如load data时将一个大文件拆分成多个小文件执行;
  - group_replication_compression_threshold可以设定合适开启消息压缩, 默认1000000 bytes (1 MB), 大于1MB的消息将自动压缩；
  - MySQL 8.0.16+开始, 支持将大消息拆散传输(fragmented), 由group_replication_communication_max_message_size控制，10485760 bytes (10 MiB)，大于1OMiB的制动拆分。
如果将上述配置项都设置为0，关闭掉上述的安全保护, 那么一个applier线程可以处理的最大的消息由slave_max_allowed_packet控制, 默认为最大值1G，当一个成员尝试处理一个消息超过这个值的消息时会失败，一个事务发起则发送事务的大小的上限为4G。

2.2. 配置参考

配置一个3个组员的复制组：
192.168.55.10 oceanbase01
192.168.55.20 oceanbase02
192.168.55.30 oceanbase03

根据组复制的需求和限制，配置my.cnf参数：

## Group Replication Requirements
disabled_storage_engines               = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
sql_require_primary_key                = ON
log_slave_updates                      = ON  
log-bin                                = /data/mysql/mgr80/binlog/blog
log-bin-index                          = /data/mysql/mgr80/binlog/blog.index
binlog-format                          = ROW
binlog-checksum                        = NONE
server_id                              = 101  #每个成员必须设置不同
gtid_mode                              = ON
enforce_gtid_consistency               = ON
master_info_repository                 = TABLE 
relay_log_info_repository              = TABLE
transaction-write-set-extraction       = XXHASH64
binlog_transaction_dependency_tracking = WRITESET_SESSION
default-table-encryption               = OFF
lower_case_table_names                 = 1
slave_parallel_workers                 = 4
slave_parallel_type                    = LOGICAL_CLOCK
slave_preserve_commit_order            = ON
transaction-isolation                  = 'READ-COMMITTED'

## Group Replication Settings
plugin_load_add                                    = 'group_replication.so'
group_replication_group_name                       = "65e5f89b-770d-4936-b72f-c7a190d5884a" #可以通过uuidgen生成
group_replication_single_primary_mode              = OFF # OFF=muti-primary mode; ON=single-primary mode;
group_replication_enforce_update_everywhere_checks = ON  # if 'muti-primary mode' -> ON, if 'single-primary mode' -> OFF;
group_replication_start_on_boot                    = off
group_replication_bootstrap_group                  = off
group_replication_local_address                    = "192.168.55.10:33061" # 根据成员实际的IP不而不同
group_replication_group_seeds                      = "192.168.55.10:33061,192.168.55.20:33061,192.168.55.30:33061"
group_replication_ip_whitelist                     = "192.168.55.0/24,192.168.203.0/24" #设置白名单
group_replication_recovery_get_public_key          = ON 

#注意:MySQL-8.0.18引入新的功能变更(https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html)
When the server is run with --initialize, there is no reason to load non-early plugins. The server now logs a warning and ignores
any --plugin-load or --plugin-load-add options given with --initialize. (Bug #29622406)

MySQL-8.0.18开始, plugin_load_add在执行–initialze时, 是会被忽略的(–initialize时不允许装载插件), 其后的group_replication_都是无效的, 所以"Group Replication Settings"部分需要执行完–initialze后添加，否者–initialze会过不去。加loose-前缀应该也可以，个人不喜欢。

2.2.1. 创建组, 并添加第一个成员

在第一个成员(oceanbase01), 创建复制用户rpl_user:

root@localhost [(none)]> SET SQL_LOG_BIN=0;
root@localhost [(none)]> CREATE USER rpl_user@'%' IDENTIFIED BY 'Passw0rd';
root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
root@localhost [(none)]> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
root@localhost [(none)]> FLUSH PRIVILEGES;
root@localhost [(none)]> SET SQL_LOG_BIN=1;
root@localhost [(none)]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Passw0rd' FOR CHANNEL 'group_replication_recovery';

创建组, 启动组复制, 任何情况下启动组复制, 第一个启动的MySQL服务器必须执行boostrap操作来创建组，后续启动的组不能再执行bootstrap, 只需要直接start group_replication即可加入组。

root@localhost [(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
root@localhost [(none)]> START GROUP_REPLICATION;
root@localhost [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;
root@localhost [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 3eed933f-e584-11d4-a71b-525400046468 | oceanbase01 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

至此，已经创建了复制组, 并包含一个成员。

2.2.2. 为组添加第二个成员

设置my.cnf参数：

server_id                              = 102
...
group_replication_local_address = "192.168.55.20:33061" 

MySQL服务器启动后, 同样再创建复制用户rpl_user:

root@localhost [(none)]> SET SQL_LOG_BIN=0;
root@localhost [(none)]> CREATE USER rpl_user@'%' IDENTIFIED BY 'Passw0rd';
root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
root@localhost [(none)]> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
root@localhost [(none)]> FLUSH PRIVILEGES;
root@localhost [(none)]> SET SQL_LOG_BIN=1;
root@localhost [(none)]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Passw0rd' FOR CHANNEL 'group_replication_recovery';

启动组复制，加入组：

root@localhost [(none)]> start group_replication;
root@localhost [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 15f92590-e587-11d4-a2d8-525400401a99 | oceanbase02 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 3eed933f-e584-11d4-a71b-525400046468 | oceanbase01 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.01 sec)

2.2.3. 为组添加第三个成员

与2.2.2.类似。

2.3. 无法加入组的处理汇总

(1). 新加入的成员执行了不在组内的事务, 通过reset master解决。

root@localhost [(none)]> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

[root@oceanbase02 logs]# cat /data/mysql/mgr80/logs/mysql.err 
...
2001-01-09T01:04:03.256039+08:00 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions:15f92590-e587-11d4-a2d8-525400401a99:1-4 > Group transactions: 3eed933f-e584-11d4-a71b-525400046468:1-4,65e5f89b-770d-4936-b72f-c7a190d5884a:1'
2001-01-09T01:04:03.256189+08:00 0 [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

查看oceanbase02的binlog情况：

root@localhost [(none)]> show master status;
+-------------+----------+--------------+------------------+------------------------------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------+----------+--------------+------------------+------------------------------------------+
| blog.000002 |     1094 |              |                  | 15f92590-e587-11d4-a2d8-525400401a99:1-4 |
+-------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

第二个节点在启动加入组之前已经执行了一些事务， 导致无法加入组。

root@localhost [(none)]> show binlog events in 'blog.000002';
+-------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| Log_name    | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                             |
+-------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| blog.000002 |    4 | Format_desc    |       102 |         124 | Server ver: 8.0.19-10, Binlog ver: 4                                                                                             |
| blog.000002 |  124 | Previous_gtids |       102 |         151 |                                                                                                                                  |
| blog.000002 |  151 | Gtid           |       102 |         226 | SET @@SESSION.GTID_NEXT= '15f92590-e587-11d4-a2d8-525400401a99:1'                                                                |
| blog.000002 |  226 | Query          |       102 |         433 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' /* xid=5 */ |
| blog.000002 |  433 | Gtid           |       102 |         508 | SET @@SESSION.GTID_NEXT= '15f92590-e587-11d4-a2d8-525400401a99:2'                                                                |
| blog.000002 |  508 | Query          |       102 |         708 | CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' /* xid=7 */        |
| blog.000002 |  708 | Gtid           |       102 |         781 | SET @@SESSION.GTID_NEXT= '15f92590-e587-11d4-a2d8-525400401a99:3'                                                                |
| blog.000002 |  781 | Query          |       102 |         935 | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION /* xid=8 */                                                          |
| blog.000002 |  935 | Gtid           |       102 |        1008 | SET @@SESSION.GTID_NEXT= '15f92590-e587-11d4-a2d8-525400401a99:4'                                                                |
| blog.000002 | 1008 | Query          |       102 |        1094 | flush privileges                                                                                                                 |
+-------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

通过reset master清空binlog.

root@localhost [(none)]> reset master;
root@localhost [(none)]> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| blog.000001 |      151 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (4.03 sec)

(2). 新加入的组无法执行，组复制过来的事务，而无法加入组。

错误日志报：

2001-01-09T01:51:02.245776+08:00 35 [System] [MY-010562] [Repl] Slave I/O thread for channel 'group_replication_recovery': connected to master 'rpl_user@oceanbase01:3306',replication started in log 'FIRST' at position 4
2001-01-09T01:51:02.567296+08:00 37 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': Worker 1 failed executing transaction '3eed933f-e584-11d4-a71b-525400046468:2' at master log blog.000002, end_log_pos 708; Error 'Operation CREATE USER failed for 'root'@'%'' on query. Default database: ''. Query: 'CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F'', Error_code: MY-00139617:51:02 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fbf0640f000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fbf69f6ce68 thread_stack 0x46000
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1f7e6ae]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x323) [0x11b7e33]
/lib64/libpthread.so.0(+0xf630) [0x7fbf84153630]
/usr/local/mysql/bin/mysqld(Relay_log_info::cannot_safely_rollback() const+0xa7) [0x1c08397]
/usr/local/mysql/bin/mysqld() [0x1c19618]
/usr/local/mysql/bin/mysqld(mts_checkpoint_routine(Relay_log_info*, bool)+0x71) [0x1c2cfe1]
/usr/local/mysql/bin/mysqld(handle_slave_sql+0x239a) [0x1c3035a]
/usr/local/mysql/bin/mysqld() [0x243f6dc]
/lib64/libpthread.so.0(+0x7ea5) [0x7fbf8414bea5]
/lib64/libc.so.6(clone+0x6d) [0x7fbf820be8cd]

这是由于第二个节点(加入)，无法执行组的"3eed933f-e584-11d4-a71b-525400046468:2"事务对于的"CREATE USER ‘root’@’%’ …“语句，
首先这是由于组内的第一个节点执行"CREATE USER ‘root’@’%’ …”, 且没有设置SET SQL_LOG_BIN=0; 这个创建用户的语句写入了binlog，复制给后续加入的成员执行，如果后续加入的成员也已经存在有相同的用户，无法创建报错。

解决方法是后续节点删除报错的用户，后启动，或者跳过这个无法执行的事务：解决方法与普通的复制类似.

第一个节点重启(我的情况不重启不行, 第二个节点一直是UNREACHABLE状态,关闭第二个节点后依然，无法继续加入):

root@localhost [(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
root@localhost [(none)]> START GROUP_REPLICATION;
root@localhost [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;

第二个节点(要加入组的成员)执行:

root@localhost [(none)]> stop group_replication;
root@localhost [(none)]> set @@session.gtid_next='3eed933f-e584-11d4-a71b-525400046468:2';
root@localhost [(none)]> begin;
root@localhost [(none)]> commit;
root@localhost [(none)]> set @@session.gtid_next = automatic;
root@localhost [(none)]> start group_replication;
Query OK, 0 rows affected (4.12 sec)

root@localhost [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 15f92590-e587-11d4-a2d8-525400401a99 | oceanbase02 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 3eed933f-e584-11d4-a71b-525400046468 | oceanbase01 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.01 sec)