day13-03-MGR故障测试

多主故障处理

10.0.50.61：
[root@VM_0_48_centos ~]# service mysqld stop
Shutting down MySQL............ SUCCESS!

mysqladmin -S /data/mysql/3306/socket/mysql.sock shutdown


node02 节点 10.0.50.62：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 503f547c-2308-11eb-9d3b-525400d3d7ba | mgr-node02  |        3306 | ONLINE       |
| group_replication_applier | 6f3983da-2303-11eb-9aa2-525400f1a562 | mgr-node03  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

mysql> 
mysql> insert into simon_db.t1 values (2,'bb');
Query OK, 1 row affected (0.01 sec)

mysql> select * from simon_db.t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)



node03 节点 10.0.50.63：

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 503f547c-2308-11eb-9d3b-525400d3d7ba | mgr-node02  |        3306 | ONLINE       |
| group_replication_applier | 6f3983da-2303-11eb-9aa2-525400f1a562 | mgr-node03  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

mysql> insert into simon_db.t1 values(3,'cc'),(4,'dd');
Query OK, 2 row affected (0.01 sec)

mysql> select * from simon_db.t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
+----+------+
4 rows in set (0.00 sec)

mysql>


恢复 node01 节点：
su - mysql
mysqld_safe --defaults-file=/data/mysql/3306/conf/my.cnf &
2020-11-10T05:15:50.985964Z mysqld_safe Logging to '/data/mysql/3306/logs/mysql-err.log'.
2020-11-10T05:15:51.001491Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/3306/data

mysql -S /data/mysql/3306/socket/mysql.sock

change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';
start group_replication;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 096c36e6-2303-11eb-9a9e-5254000e1659 | mgr-node01  |        3306 | ONLINE       |
| group_replication_applier | 503f547c-2308-11eb-9d3b-525400d3d7ba | mgr-node02  |        3306 | ONLINE       |
| group_replication_applier | 6f3983da-2303-11eb-9aa2-525400f1a562 | mgr-node03  |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)


mysql> insert into simon_db.t1 values(5,'ee');
Query OK, 1 row affected (0.01 sec)

mysql> select * from simon_db.t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
+----+------+
5 rows in set (0.00 sec)

mysql>

补充：模拟一个节点和两个节点挂恢复是一样的：

set global group_replication_single_primary_mode =OFF;

change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';

start group_replication;

• 查看节点复制延迟与应用情况

  select * from performance_schema.replication_group_member_stats where MEMBER_ID=@@server_uuidG;
  
  
  *************************** 1. row ***************************
  
  CHANNEL _NAME: group_replication_applier
  
  VIEW_ID: 15218000786938271:11
  
  MEMBER_ID: 0a1e8349-2e87-11e8-8c9f-525400bdd1f2
  
  COUNT_TRANSACTIONS_IN_QUEUE: 287640 # 该字段显示当前接收到的relay log与当前应用到的relay log之间的事务差异
  
  COUNT_TRANSACTIONS_CHECKED: 0
  
  COUNT_CONFLICTS_DETECTED: 0
  
  COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
  
  TRANSACTIONS_COMMITTED_ALL_MEMBERS: 2d623f55-2111-11e8-9cc3-0025905b06da:1-2,
  
  aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13779 # 该字段显示当前节点应用到的日志对应的GTID
  
  LAST_CONFLICT_FREE_TRANSACTION:
  
  1 row in set (0.02 sec)
  
  PS：该方式必须保证集群中已有节点的binlog未执行过清理，一旦有清理，新加节点无法通过全量binlog复制来加入集群