mysql 8.0 mgr 日常运维命令 -m6米乐安卓版下载

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});