先看背景：
| IP | 主从关系 | host |
| 10.1.1.45 | 主 | db-1-45 |
| 10.1.1.46 | 从 | db-1-46 |
| 10.1.1.47 | 从 | db-1-47 |
上一篇已经把mysql实例全部升级为myql8.0.26了。
一、修改host
三台机器的host全部加上
#vi /etc/hosts10.20.1.45 db-1-4510.20.1.46 db-1-4610.20.1.47 db-1-47
二、配置增强半同步（普通复制改为增强半同步，如果已经是半同步则不用再操作了）
2.1 所有节点（3个实例）：
root@localhost [(none)]>set global super_read_only=0;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.01 sec)root@localhost [(none)]>install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.02 sec)
master节点配置
root@localhost [(none)]>set global rpl_semi_sync_master_enabled=ON;Query OK, 0 rows affected (0.01 sec)root@localhost [(none)]>show global variables like '%semi%';+-------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_for_slave_count | 1 || rpl_semi_sync_master_wait_no_slave | ON || rpl_semi_sync_master_wait_point | AFTER_SYNC || rpl_semi_sync_slave_enabled | OFF || rpl_semi_sync_slave_trace_level | 32 |+-------------------------------------------+------------+8 rows in set (0.00 sec)#root@localhost [(none)]>reset master;#Query OK, 0 rows affected (0.04 sec)
slave配置（2个节点）
root@localhost [(none)]>set global rpl_semi_sync_slave_enabled=ON;Query OK, 0 rows affected (0.00 sec)
检查
root@localhost:mysql_10120.sock [(none)]>show global status like '%semi%';+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 2 || Rpl_semi_sync_master_net_avg_wait_time | 0 || Rpl_semi_sync_master_net_wait_time | 0 || Rpl_semi_sync_master_net_waits | 0 || Rpl_semi_sync_master_no_times | 0 || Rpl_semi_sync_master_no_tx | 0 || Rpl_semi_sync_master_status | ON || Rpl_semi_sync_master_timefunc_failures | 0 || Rpl_semi_sync_master_tx_avg_wait_time | 0 || Rpl_semi_sync_master_tx_wait_time | 0 || Rpl_semi_sync_master_tx_waits | 0 || Rpl_semi_sync_master_wait_pos_backtraverse | 0 || Rpl_semi_sync_master_wait_sessions | 0 || Rpl_semi_sync_master_yes_tx | 0 || Rpl_semi_sync_slave_status | OFF |
三、配置MGR
3.1 master配置
root@localhost:mysql_10120.sock [(none)]>install plugin group_replication soname 'group_replication.so';Query OK, 0 rows affected (0.01 sec)
修正和增加一些参数（persist可以将参数的值持久化到mysqld-auto.cnf文件中）：
root@localhost:mysql_10120.sock [(none)]>set persist binlog_checksum=NONE;Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(none)]>set persist transaction_write_set_extraction=XXHASH64;Query OK, 0 rows affected, 1 warning (0.01 sec)root@localhost:mysql_10120.sock [(none)]>select uuid();+--------------------------------------+| uuid() |+--------------------------------------+| 0220ac35-f109-11eb-85ef-3448edf8dd99 |+--------------------------------------+1 row in set (0.00 sec)#设置uuid和group_replication的参数root@localhost:mysql_10120.sock [(none)]>set persist group_replication_group_name='0220ac35-f109-11eb-85ef-3448edf8dd99';Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(none)]>set persist group_replication_local_address='10.20.1.45:30120';Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(none)]>set persist group_replication_group_seeds='10.20.1.45:30120,10.20.1.46:30120,10.20.1.47:30120';Query OK, 0 rows affected (0.00 sec)
如果ssl开启就需要
SET persist group_replication_recovery_get_public_key = 1;
开启参数：
root@localhost:mysql_10120.sock [(none)]>set persist group_replication_bootstrap_group=off;Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(none)]>set persist group_replication_start_on_boot=off;Query OK, 0 rows affected (0.00 sec)#第一次启动设置，启动后需要关闭root@localhost:mysql_10120.sock [(none)]>set global group_replication_bootstrap_group=on;Query OK, 0 rows affected (0.00 sec)
设置完成后，启动group replication
root@localhost:mysql_10120.sock [(none)]>start group_replication;Query OK, 0 rows affected (2.10 sec)#可以同步查看实例的日志情况 error
#关闭启动引导和查看开启的情况root@localhost:mysql_10120.sock [(none)]>set global group_replication_bootstrap_group=off;Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(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 | 60e6c8db-cf21-11eb-b529-3448edf8dd99 | db-23-45 | 10120 | ONLINE | PRIMARY | 8.0.26 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+1 row in set (0.00 sec)
至此Master改造成功
3.2 slave改造
root@localhost:mysql_10120.sock [(none)]>install plugin group_replication soname 'group_replication.so';Query OK, 0 rows affected (0.02 sec)root@localhost:mysql_10120.sock [(none)]>set persist binlog_checksum=NONE;Query OK, 0 rows affected (0.01 sec)root@localhost:mysql_10120.sock [(none)]>set persist transaction_write_set_extraction=XXHASH64;Query OK, 0 rows affected, 1 warning (0.00 sec)root@localhost:mysql_10120.sock [(none)]>root@localhost:mysql_10120.sock [(none)]>set persist group_replication_group_name='0220ac35-f109-11eb-85ef-3448edf8dd99';Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(none)]>root@localhost:mysql_10120.sock [(none)]>set persist group_replication_local_address='10.20.146:30120';Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(none)]>set persist group_replication_group_seeds='10.20.1.45:30120,10.20.1.46:30120,10.20.1.47:30120';Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(none)]>set persist group_replication_bootstrap_group=off;Query OK, 0 rows affected (0.01 sec)root@localhost:mysql_10120.sock [(none)]>set persist group_replication_start_on_boot=off;Query OK, 0 rows affected (0.00 sec)root@localhost:mysql_10120.sock [(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.
这里开启时候报错了，可以看看错误日志：
[ERROR] [MY-013117] [Repl] Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: MY-013117
2021-07-30T15:58:58.097055+08:00 147328 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
关闭原有的复制，重新设置复制模式
root@localhost:mysql_10120.sock [(none)]>stop slave;Query OK, 0 rows affected, 2 warnings (0.00 sec)root@localhost:mysql_10120.sock [(none)]>reset slave all;Query OK, 0 rows affected, 1 warning (0.01 sec)#设置复制模式root@localhost:mysql_10120.sock [(none)]>change master to master_user='myrep',master_password='qAhUrjpseZ13C0q' for channel 'group_replication_recovery';Query OK, 0 rows affected, 5 warnings (0.00 sec)#再开启root@localhost:mysql_10120.sock [(none)]>start group_replication;Query OK, 0 rows affected (3.24 sec)#验证：root@localhost:mysql_10120.sock [(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 | 60e6c8db-cf21-11eb-b529-3448edf8dd99 | db-23-45 | 10120 | ONLINE | PRIMARY | 8.0.26 || group_replication_applier | c0a622ac-cf63-11eb-9456-801844edbae5 | db-23-46 | 10120 | ONLINE | SECONDARY | 8.0.26 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+2 rows in set (0.00 sec)
查看从库IO线程的连接状态信息
# 多线程和单线程主从复制时表中记录相同，如果是多主复制，则每个复制通道在表中个记录一行信息
root@localhost:mysql_10120.sock [(none)]>select * from performance_schema.replication_connection_status\G*************************** 1. row ***************************CHANNEL_NAME: group_replication_recoveryGROUP_NAME:SOURCE_UUID:THREAD_ID: NULLSERVICE_STATE: OFFCOUNT_RECEIVED_HEARTBEATS: 0LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000RECEIVED_TRANSACTION_SET:LAST_ERROR_NUMBER: 0LAST_ERROR_MESSAGE:LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION:LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000QUEUEING_TRANSACTION:QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000*************************** 2. row ***************************CHANNEL_NAME: group_replication_applierGROUP_NAME: 0220ac35-f109-11eb-85ef-3448edf8dd99SOURCE_UUID: 0220ac35-f109-11eb-85ef-3448edf8dd99THREAD_ID: NULLSERVICE_STATE: ONCOUNT_RECEIVED_HEARTBEATS: 0LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000RECEIVED_TRANSACTION_SET: 00a1eca5-7946-11ea-a743-dcf401e6ca11:1-1085258803,0220ac35-f109-11eb-85ef-3448edf8dd99:1-5,60e6c8db-cf21-11eb-b529-3448edf8dd99:1LAST_ERROR_NUMBER: 0LAST_ERROR_MESSAGE:LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000LAST_QUEUED_TRANSACTION: 0220ac35-f109-11eb-85ef-3448edf8dd99:5LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2021-07-30 16:05:20.684393LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2021-07-30 16:05:20.684393LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2021-07-30 16:05:20.684436LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2021-07-30 16:05:20.684482QUEUEING_TRANSACTION:QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.0000002 rows in set (0.02 sec)
slave2也同理操作即可，验证结果如下：
root@localhost:mysql_10120.sock [(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 | 34aa11b3-cfce-11eb-9c06-b82a72db584a | db-23-47 | 10120 | ONLINE | SECONDARY | 8.0.26 || group_replication_applier | 60e6c8db-cf21-11eb-b529-3448edf8dd99 | db-23-45 | 10120 | ONLINE | PRIMARY | 8.0.26 || group_replication_applier | c0a622ac-cf63-11eb-9456-801844edbae5 | db-23-46 | 10120 | ONLINE | SECONDARY | 8.0.26 |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+3 rows in set (0.00 sec)
# 多线程和单线程主从复制时表中记录相同，如果是多主复制，则每个复制通道在表中个记录一行信息
四、节点全部关闭后再启动（冷启动）
需要注意的是，第一个主节点启动时候，仍然需要设置下
set global group_replication_bootstrap_group=on;启动start group_replication;再关闭set global group_replication_bootstrap_group=off;
其它几点启动则不需要，直接启动实例后，进行start group_replication;
参考：https://t.1yb.co/xodU
文章转载自得塔江湖，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
