Mysql MGR（2）基本搭建与使用
【1】MGR 单主配置
（1.0）限制要求与基本架构
深入MGR限制，官网参考：https://dev.mysql.com/doc/refman/8.0/en/group-replication-requirements.html
限制：
- 仅InnoDB Engine(Transactional and row level lock)
- 表必须有主键
- gtid-mode=ON
- binlog格式为Row-based
- MGR无法使用复制事件检测 即 binlog_checksum=NONE
- MGR不支持串行化隔离级别
- MGR不支持复制过滤 Replication Filters
- MGR最多可以有9个成员
- MGR的验证过程不考虑table locks 和named locks Replication Event Checksums
下面是关于MGR使用的一些限制：
- 所有表必须是InnoDB引擎。可以创建非InnoDB引擎表，但无法写入数据，在利用Clone构建新节点时也会报错。
- 所有表都必须要有主键。同上，能创建没有主键的表，但无法写入数据，在利用Clone构建新节点时也会报错。
- 不要使用大事务，默认地，事务超过150MB会报错，最大可支持2GB的事务（在GreatSQL未来的版本中，会增加对大事务的支持，提高大事务上限）。
- 如果是从旧版本进行升级，则不能选择 MINIMAL 模式升级，建议选择 AUTO 模式，即
upgrade=AUTO。 - 由于MGR的事务认证线程不支持
gap lock，因此建议把所有节点的事务隔离级别都改成READ COMMITTED。基于相同的原因，MGR集群中也不要使用table lock及name lock（即GET_LOCK()函数 ）。 - 在多主（
multi-primary）模式下不支持串行（SERIALIZABLE）隔离级别。 - 不支持在不同的MGR节点上，对同一个表分别执行DML和DDL，可能会造成数据丢失或节点报错退出。
- 在多主（
multi-primary）模式下不支持多层级联外键表。另外，为了避免因为使用外键造成MGR报错，建议设置group_replication_enforce_update_everywhere_checks=ON。 - 在多主（
multi-primary）模式下，如果多个节点都执行SELECT ... FOR UPDATE后提交事务会造成死锁。 - 不支持复制过滤（Replication Filters）设置。
看起来限制有点多，但绝大多数时候并不影响正常的业务使用。
此外，想要启用MGR还有几个要求：
- 每个节点都要启用binlog。
- 每个节点都要转存binlog，即设置
log_slave_updates=1。 - binlog format务必是row模式，即
binlog_format=ROW。 - 每个节点的
server_id及server_uuid不能相同。 - 在8.0.20之前，要求
binlog_checksum=NONE，但是从8.0.20后，可以设置binlog_checksum=CRC32。 - 要求启用 GTID，即设置
gtid_mode=ON。 - 要求
master_info_repository=TABLE及relay_log_info_repository=TABLE，不过从MySQL 8.0.23开始，这两个选项已经默认设置TABLE，因此无需再单独设置。 - 所有节点上的表名大小写参数
lower_case_table_names设置要求一致。 - 最好在局域网内部署MGR，而不要跨公网，网络延迟太大的话，会导致MGR性能很差或很容易出错。
- 建议启用writeset模式，即设置以下几个参数
slave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = N，N>0，可以设置为逻辑CPU数的2倍binlog_transaction_dependency_tracking = WRITESETslave_preserve_commit_order = 1slave_checkpoint_period = 2
192.168.191.25 db1 M
192.168.191.44 db2 S
192.168.191.51 db3 S
（1.1）前置 /etc/hosts 配置
cat <>/etc/hosts 192.168.191.25 db1 192.168.191.44 db2 192.168.191.51 db3 EOF
（1.2）配置文件
MGR必备配置：
gtid_mode=ON enforce_gtid_consistency=ON binlog_checksum=NONE skip_name_resolve=on
transaction_isolation='READ-COMMITTED' # binlog log_bin=binlog log_slave_updates=ON binlog_format=ROW master_info_repository=TABLE relay_log_info_repository=TABLE # MGR disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" transaction_write_set_extraction=XXHASH64 plugin_load_add='group_replication.so,validate_password.so;semisync_master.so;semisync_slave.so' group_replication_enforce_update_everywhere_checks = OFF group_replication_single_primary_mode = on group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address= "192.168.191.25:33061" # 要根据不同机器修改成自己本机的IP group_replication_group_seeds= "192.168.191.25:33061,192.168.191.44:33061,192.168.191.51:33061" group_replication_bootstrap_group=off
参数释义
binlog_checksum = NONE ： MGR不支持带checksum的binlog event
transaction_write_set_extraction = XXHASH64 ：Group Replication要求每个表必须要有主键，用来做冲突检测。组内所有成员必须配置相同的哈希算法。
group_replication_group_name = 'ee99091a-e1e6-11ea-90d7-fa163ec61b6c' ：一个UUID值，组内唯一，组内都是用这个值，用来标记组内所有成员上产生的binlog event。
loose ：loose 前缀的意义在于第一次启动时还没加载组复制的plugin,可以让mysql server忽略该参数，继续启动
group_replication_group_seeds ：设置种子成员的地址，当加入一个组时,新成员首先需要和组内的成员进行通信来完成加入的步骤,因此需要知道至少一个当前组内成员的地址
group_replication_start_on_boot = off ：指示插件mysql启动的时候不要自动启动组复制
我的配置文件，所有
[mysql] password=bfgame20 prompt="\u@mysqldb \R:\m:\s [\d]> " no-auto-rehash socket = /data/mysql/mysql.sock [client] password=bfgame20 port = 3306 socket = /data/mysql/mysql.sock user=root [mysqld] user=mysql basedir = /usr/local/mysql datadir = /data/mysql socket = /data/mysql/mysql.sock pid-file = /data/mysql/mysql.pid tmpdir = /data/mysql slow_query_log_file = /data/mysql_log/slowlog/slow.log log_error = /data/mysql_log/errorlog/error.log log_bin = /data/mysql_log/binlog/mysql-bin relay-log = /data/mysql_log/relaylog/relay-bin port = 3306 server_id=1652430883 # 注意不同机器也要不一样 character_set_server = utf8mb4 skip_name_resolve = 1 max_connections = 4096 max_connect_errors = 100000 max_allowed_packet = 128M tmp_table_size = 128M sort_buffer_size=4M slow_query_log = 1 long_query_time = 1 lock_wait_timeout=36000 secure-file-priv='' default_authentication_plugin=mysql_native_password log_timestamps=system group_concat_max_len=65535 #replication gtid_mode = on enforce_gtid_consistency = on log_slave_updates sync_binlog = 0 max_binlog_size = 1G #binlog_expire_logs_seconds = 864000 binlog_format = row master_info_repository = TABLE relay_log_info_repository = TABLE #innodb transaction_isolation = READ-COMMITTED innodb_flush_method=O_DIRECT innodb_buffer_pool_size = m innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 1G innodb_flush_neighbors = 0 innodb_thread_concurrency = 32 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_rollback_segments = 128 innodb_undo_tablespaces = 3 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 1G #performance-schema performance-schema-instrument='memory/%=COUNTED' performance_schema_digests_size = 40000 performance_schema_max_table_handles = 40000 performance_schema_max_table_instances = 40000 performance_schema_max_sql_text_length = 4096 performance_schema_max_digest_length = 4096 #table cache performance settings table_open_cache = 6000 table_definition_cache = 6000 table_open_cache_instances = 36 transaction_write_set_extraction=XXHASH64 group_replication_enforce_update_everywhere_checks = OFF group_replication_single_primary_mode = on skip_name_resolve=on plugin_load_add='group_replication.so' group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address= "192.168.191.51:33061" # 注意不同机器要改成对应机器IP group_replication_group_seeds= "192.168.191.25:33061,192.168.191.44:33061,192.168.191.51:33061" group_replication_bootstrap_group=off
（1.3）准备复制账户
-- （1）构造复制用户 3个机器上都运行 SET SQL_LOG_BIN=0; CREATE USER rpl_user@'192.168.191.%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE,CONNECTION_ADMIN,BACKUP_ADMIN,GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'192.168.191.%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; -- （2）构造复制通道，3个机器上都运行 CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='password' FOR CHANNEL "group_replication_recovery";
疑问：
1，master_auto_position=1 为什么不需要了？
2，master_host 为什么不要了？
3，FOR CHANNEL 'group_replication_recovery' 这一句的原理？ group_replication_recovery:标准异步复制通道
理解就是，可能是在 channel 通道名称为 group_replication_recovery 里面，mysql 已经内置好了吧；
（1.4）MGR 主节点引导组复制启动
选择 db1 或者任意做主库的执行
-- 主库上执行 db1 192.168.191.25 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION USER='rpl_user', PASSWORD='password'; SET GLOBAL group_replication_bootstrap_group=OFF; SELECT * FROM performance_schema.replication_group_members;
root@mysqldb 17:11: [test]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | b1336aa3-c13a-11ec-9cab-fa163e6ae43e | db1 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 1 row in set (0.00 sec)
解析这一步 ：
SET GLOBAL group_replication_bootstrap_group=ON;
SET GLOBAL group_replication_bootstrap_group=OFF;
啥意思呀 为什么要重复开关，第一次接触一脸懵逼！！！ ###后面了解到 这个是默认引导主，理解起来就是整个集群的主。
只在一台做这个操作，做完之后要关闭，不关闭会怎么样？ 不关闭就引导没有完成
构造语句：
-- 主句上执行 测试数据 CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); INSERT INTO t1 VALUES (1, 'Luis'); SELECT * FROM t1; show binlog events; root@mysqldb 17:11: [test]> show binlog events; +------------------+------+----------------+------------+-------------+---------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+------------+-------------+---------------------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1650521822 | 126 | Server ver: 8.0.28, Binlog ver: 4 | | mysql-bin.000001 | 126 | Previous_gtids | 1650521822 | 157 | | | mysql-bin.000001 | 157 | Gtid | 1650521822 | 243 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1' | | mysql-bin.000001 | 243 | Query | 1650521822 | 309 | BEGIN | | mysql-bin.000001 | 309 | View_change | 1650521822 | 412 | view_id=16526922299317057:1 | | mysql-bin.000001 | 412 | Query | 1650521822 | 484 | COMMIT | | mysql-bin.000001 | 484 | Gtid | 1650521822 | 568 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2' | | mysql-bin.000001 | 568 | Query | 1650521822 | 690 | CREATE DATABASE IF NOT EXISTS test /* xid=26 */ | | mysql-bin.000001 | 690 | Gtid | 1650521822 | 774 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3' | | mysql-bin.000001 | 774 | Query | 1650521822 | 916 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) /* xid=29 */ | | mysql-bin.000001 | 916 | Gtid | 1650521822 | 1002 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4' | | mysql-bin.000001 | 1002 | Query | 1650521822 | 1077 | BEGIN | | mysql-bin.000001 | 1077 | Table_map | 1650521822 | 1132 | table_id: 116 (test.t1) | | mysql-bin.000001 | 1132 | Write_rows | 1650521822 | 1178 | table_id: 116 flags: STMT_END_F | | mysql-bin.000001 | 1178 | Xid | 1650521822 | 1209 | COMMIT /* xid=30 */ | +------------------+------+----------------+------------+-------------+---------------------------------------------------------------------------------+ 15 rows in set (0.00 sec)
如上图，我们可以看到，binlog 已经在我们新建的 group_name 的 GTID 下生成事务了；
（1.5）MGR 从节点加入
与之前在 db1 上执行的步骤不同，这里的不同之处在于您 不需要引导该组，因为该组已经存在。
换句话说，在 db2 group_replication_bootstrap_group 上设置为 OFF，并且在开始组复制之前不要发出 SET GLOBAL group_replication_bootstrap_group=ON;
这是因为组已经由服务器 db1 创建和引导。此时服务器 db2 只需添加到已经存在的组中。
start group_replication; SELECT * FROM performance_schema.replication_group_members; root@mysqldb 17:45: [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 8f5f3f26-d297-11ec-ba45-fa163eb26cb2 | db3 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | | group_replication_applier | b1336aa3-c13a-11ec-9cab-fa163e6ae43e | db1 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom | | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | RECOVERING | SECONDARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.01 sec) root@mysqldb 17:45: [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 8f5f3f26-d297-11ec-ba45-fa163eb26cb2 | db3 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | | group_replication_applier | b1336aa3-c13a-11ec-9cab-fa163e6ae43e | db1 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom | | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.00 sec)
如果发现一直不是online 状态，则可以查看连接情况：
select * from performance_schema.replication_connection_status\G
如上所述，我们可以看到新加入的从节点会有短暂的 RECOVERING，那是因为主节点有事务在复制组缓存内；
然后GTID 所有事务都可以完美衔接（这里直接从事务号 1 开始当然可以接得上），所以从库在重做
从库查询，数据也同步过来了；
root@mysqldb 17:57: [test]> select * from test.t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) root@mysqldb 17:57: [test]>
至此，一个新的 MGR 单主集群已经构成；
（1.6）数据验证
主库操作
create table t2(id int primary key,str varchar(100)); insert into t2 values(1,'a'); insert into t2 values(2,'b'),(3,'c');
从库查看
OK，完成，没有问题；
【2】MGR 单主测试
（2.1）从库只读
不仅仅是 read_only，连 super_read_only 都开了
root@mysqldb 17:33: [(none)]> show variables like '%read_only%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | super_read_only | ON | | transaction_read_only | OFF | +-----------------------+-------+
《1》关闭主节点，剩下2台会自动选举一台作为主（很快1s不到）
《2》加入剩余2台再关闭一台呢？剩下2台再关闭一台 那会继续选举一台作为主（很快1s不到）
《3》那如果在《2》的情况下，2台机器断开呢
这里有个疑问。官方默认最少节点是3台。不知道为何这种情况就可以1台运行
（2.2）故障转移测试----1主2从=》正常关闭主节点（shutdown）
[root@db1 ~]# service mysqld stop
Shutting down MySQL........ SUCCESS!
如上图，故障转移成功；1-2秒就切换成功；
（2.2）故障转移测试----1主2从=》非正常关闭主节点（kill -9）
如下面所示，自动故障转移切换成功，大概花了20 秒左右吧；
root@mysqldb 11:11: [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 8f5f3f26-d297-11ec-ba45-fa163eb26cb2 | db3 | 3306 | UNREACHABLE | PRIMARY | 8.0.28 | XCom | | group_replication_applier | b1336aa3-c13a-11ec-9cab-fa163e6ae43e | db1 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.00 sec) root@mysqldb 11:11: [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | b1336aa3-c13a-11ec-9cab-fa163e6ae43e | db1 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom | | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
（2.3）故障转移测试----1主1从=》再次关闭主节点（正常关闭）
如下，主节点会自动告知剩余节点，剩余节点变成主节点，1秒切换成功
root@mysqldb 11:28: [(none)]> SELECT * FROM performance_schema.replication_group_members ; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 8f5f3f26-d297-11ec-ba45-fa163eb26cb2 | db3 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom | | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 2 rows in set (0.00 sec) root@mysqldb 11:44: [(none)]> SELECT * FROM performance_schema.replication_group_members ; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 1 row in set (0.00 sec)
（2.3）故障转移测试----1主1从=》再次关闭主节点（非正常关闭 kill -9 ）
5秒检测，变成 UNREACHABLE
root@mysqldb 11:18: [(none)]> SELECT * FROM performance_schema.replication_group_members ; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 8f5f3f26-d297-11ec-ba45-fa163eb26cb2 | db3 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 2 rows in set (0.00 sec) root@mysqldb 11:18: [(none)]> SELECT * FROM performance_schema.replication_group_members ; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 8f5f3f26-d297-11ec-ba45-fa163eb26cb2 | db3 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom | | group_replication_applier | da27603d-d29b-11ec-bda2-fa163e8bc590 | db2 | 3306 | UNREACHABLE | PRIMARY | 8.0.28 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 2 rows in set (0.01 sec)
然后就算把主库重新拉起来也没用；不会自动加入、恢复
-- 主库DB2 重新启动后操作 root@mysqldb 11:54: [(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.
然后恢复不了啦；整个集群就挂了；如何恢复？ db2 执行
SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION USER='rpl_user', PASSWORD='password'; SET GLOBAL group_replication_bootstrap_group=OFF; SELECT * FROM performance_schema.replication_group_members;
从库上显示好了；
但主库上却并没有识别到
把 db3，从库， stop group_replication 再 start group_replication 一下，就好了；
【参考文档】
官网必看：https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html