MySQL 8.0 MGR 日常运维命令

1.引导MGR集群

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> set GLOBAL group_replication_bootstrap_group=OFF;

2.加入MGR集群

mysql> START GROUP_REPLICATION;

3.停止MGR集群

mysql> STOP GROUP_REPLICATION;

4.查看MGR集群成员状态

mysql> SELECT * FROM performance_schema.replication_group_members;

5.查看MGR集群传输状态

mysql> SELECT * FROM performance_schema.replication_connection_status;
mysql> SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status WHERE  channel_name = 'group_replication_applier' UNION ALL SELECT variable_value FROM performance_schema.global_variables WHERE  variable_name = 'gtid_executed'\G

通过比较RECEIVED_TRANSACTION_SET 和variable_value 值查看接收到的事务和已执行完的事务之间的差距。

6.查看MGR集群延迟\冲突

mysql> SELECT MEMBER_ID AS id, COUNT_TRANSACTIONS_IN_QUEUE AS trx_tobe_certified, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS relaylog_tobe_applied, COUNT_TRANSACTIONS_CHECKED AS trx_chkd, COUNT_TRANSACTIONS_REMOTE_APPLIED AS trx_done, COUNT_TRANSACTIONS_LOCAL_PROPOSED AS proposed FROM performance_schema.replication_group_member_stats;

其中，relaylog_tobe_applied 的值表示远程事务写到relay log后，等待回放的事务队列，trx_tobe_certified 表示等待被认证的事务队列大小，这二者任何一个值大于0，都表示当前有一定程度的延迟。

7.查查看MGR集群性能指

mysql> SELECT * FROM performance_schema.replication_group_member_stats;
mysql> SELECT * FROM performance_schema.replication_applier_status_by_worker;

8.切换主节点

mysql客户端:
mysql> select * from performance_schema.replication_group_members;
mysql> select group_replication_set_as_primary('4697c302-3e52-11ed-8e61-0050568a658a');

MySQL Shell客户端:
JS > var c=dba.getCluster()
JS > c.status()
JS > c.setPrimaryInstance('172.20.23.16:3306')

9.多主\单主切换

mysql客户端:
mysql> select group_replication_switch_to_multi_primary_mode(); --切为多主
mysql> select group_replication_switch_to_single_primary_mode('4697c302-3e52-11ed-8e61-0050568a658a'); --切为单主

MySQL Shell客户端:
JS > var c=dba.getCluster()
JS > c.switchToMultiPrimaryMode() --切为多主
JS > c.switchToSinglePrimaryMode("172.20.23.16:3306") --切为单主

10.新增节点

mysql客户端:
首先，要先完成MySQL Server初始化，创建好MGR专用账户、设置好MGR服务通道等前置工作,可以参考
https://support.enmotech.com/article/3374/publish文章
mysql> set global clone_valid_donor_list='172.20.23.16:3306';
mysql> set global super_read_only=0; 
mysql> clone INSTANCE FROM repl@172.20.23.16:3306 IDENTIFIED BY 'repl@123';
mysql> start group_replication; 

MySQL Shell客户端:
先执行MySQL Server初始化，并执行 dba.configureInstance() 创建MGR专用账号后。而后，连接到Primary节点，直接调用 addInstance() 函数即可
新节点:
JS > dba.configureInstance() 
JS > dba.checkInstanceConfiguration("repl@172.20.23.19:3306")
主节点:
JS > var c=dba.getCluster()
JS > c.addInstance('repl@172.20.23.19:3306')
JS > c.describe()

11.删除节点

mysql客户端:
mysql> start group_replication;
mysql> reset master;
mysql> reset slave all;

MySQL Shell客户端:
JS > var c=dba.getCluster()
JS > c.removeInstance('172.20.23.19:3306');
JS > c.removeInstance('repl@172.20.23.19:3306',{force:true})

12.异常退出的节点重新加回

JS > var c=dba.getCluster()
JS > c.rejoinInstance('172.20.23.19:3306');

当节点因为网络断开、实例crash等异常情况与MGR集群断开连接后，这个节点的状态会变成 UNREACHABLE，待到超过 group_replication_member_expel_timeout + 5 秒后，集群会踢掉该节点。等到这个节点再次启动并执行 start group_replication，正常情况下，该节点应能自动重新加回集群。

13.重启MGR集群

mysql客户端:
确认各节点当前的事务执行情况,然后引导已执行完的事务GTID值最大的节点为主节点；其他节点加入集群。
所有节点检查：
mysql> select RECEIVED_TRANSACTION_SET from performance_schema.replication_connection_status where 
channel_name = 'group_replication_applier' union all 
select variable_value from performance_schema.global_variables where 
variable_name = 'gtid_executed'\G
主节点：
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
其他节点：
mysql> start group_replication;

MySQL Shell客户端:
JS > dba.rebootClusterFromCompleteOutage('myCluster'); 

14.删除mysql_innodb_cluster_metadata元数据库

JS > dba.dropMetadataSchema();

15.解散集群

JS > var c=dba.getCluster()
JS > c.dissolve({force:true})

16.MySQL Shell接管集群

var c=dba.createCluster('MGR1', {adoptFromGr:true});