Mysql Group Replication自动failover

目前组复制一共有4个节点，具体如下：

机器名        IP                        角色

qht131    172.17.61.131        primary   

qht132    172.17.61.132        secdnode1

qht133    172.17.61.133        secdnode2

qht134    172.17.61.134        secdnode3

测试目的：如果qht131出现意外的话，其它的节点能否接管组复制; 成功的话，能否将qht131重新加入组。

1.目前的组成员状态：

qht131:

mysql>  select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89c | qht134      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
4 rows in set (0.00 sec)

mysql> select * from l5m.test_mgr;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.24 sec)

mysql> insert into l5m.test_mgr values(5);
Query OK, 1 row affected (0.32 sec)

mysql> select * from l5m.test_mgr;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

qht132,qht133,qht134:

mysql> select * from l5m.test_mgr;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.11 sec)

各组成员的复制没有问题。

2.kill掉primary的mysql服务

qht131:

[root@qht131 mysql]# ps -ef | grep mysql
root      6475     1  0 10:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/u01/mysql --pid-file=/u01/mysql/mysqld.pid
mysql     6954  6475  0 10:59 pts/0    00:00:26 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/u01/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/u01/log/mysql/mysql_3306.err --open-files-limit=8192 --pid-file=/u01/mysql/mysqld.pid --socket=/u01/mysql/mysql.sock
root      7480  2704  0 14:03 pts/0    00:00:00 grep mysql
[root@qht131 mysql]# kill -9 6954

qht132:

mysql> select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89c | qht134      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.03 sec) 

3.qht131被kill掉之后，其它的三个节点都正常，从memeber_id的顺序来看，qht134现在应该是目前的primary server.

qht134:

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

看qht134能否正常写入数据。

mysql> insert into l5m.test_mgr values (6);
Query OK, 1 row affected (0.13 sec)

mysql>  select * from l5m.test_mgr;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

qht132,qht133的数据也都同步过来了。

mysql>  select * from l5m.test_mgr;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

4.那么qht131还能否重新加入到组？

qht131:

[root@qht131 mysql]#  service mysql  start
Starting MySQL..                                           [  OK  ]

mysql>  select * from l5m.test_mgr;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.01 sec)

重新开启qht131的服务，直接加入组复制 

mysql> start group_replication;
Query OK, 0 rows affected (2.77 sec)

mysql>   select * from l5m.test_mgr;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

加入组成功后，数据自动同步了，qht131目前是Read Only状态，说明已被做为一个slave库了。

mysql>  select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89c | qht134      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
4 rows in set (0.00 sec)

结论：目前这个版本（5.7.21）的mgr可用性还是不错的，移除节点后都是自动化failover到memer_id最小的一台server上继续服务，新加入节点也是比较方便的。