mysql-5.7.20 版本的 mysql-group-replication 可用性测试报告
一、喜迎 mysql-5.7.20
事实上mysql-group-replication 功能是在mysql-5.7.17这个版本上引入的，它实现了mysql各个结点间数据强一致性，
这个也成为了我心目中mysql的杀手级功能。 同样在它前期也是深深的伤害了我，先从官方文档上说起。官方文档上对
mysql-group-replication环境的搭建demo是在一台机器上安装三个mysql数据结点来讲解的，但是在我想把mysql-gro
up-replication安装到三台主机上时就出现了许多的问题。
1、 如果三台主机之间没有做dns解析的话，mysql-group-replication 会搭建不起来，这个问题我想哭的心都有了，因为
我在配置文件中填的是ip地址呀！ 但是为何还要去走一遍dns我也是太阳了dog了。 这个问题可以通过配置好dns解决，
mysql后面的版本也把这个bug给修了。
2、mysql-group-replication 还有一个最让我受不了的问题就是、就在是你建好环境之后、把环境中的一台mysql数据库(master)
给它kill 掉、这种情况下mysql会把master切到uuid排第二的主机上去；但是之前的master再也加不进集群了，你说这集群还能用吗？
当然是不能！
3、mysql-5.7.20 终于、终于、王者归来！ 上面的问题它都没有了、对于这个已经基本可用的版本、表现出了无限的崇拜、于是
第一时间把mysql-group-replication的自动化安装整合到了mysqltools(下载地址https://github.com/Neeky/mysqltools)这个工具中。
二、环境规划
主机名 ip地址 在mgr中的角色
mgr17 10.186.19.17 primary
mgr18 10.186.19.18 seconde
mgr19 10.186.19.19 seconde
三、使用mysqltools自动化安装mysql-group-replication：
ansible-playbook install_group_replication.yaml PLAY [mgr1] ******************************************************************** TASK [Gathering Facts] ********************************************************* ok: [mrg17] ok: [mrg19] ok: [mrg18] TASK [create mysql user] ******************************************************* ok: [mrg19] ok: [mrg18] ok: [mrg17] TASK [create and config /etc/my.cnf] ******************************************* changed: [mrg17] changed: [mrg19] changed: [mrg18] TASK [transfer mysql install package to remote host and unarchive to /usr/local/] *** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [change owner to mysql user] ********************************************** changed: [mrg17] changed: [mrg19] changed: [mrg18] TASK [make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql] ***************** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [export mysql share object (*.os)] **************************************** ok: [mrg18] ok: [mrg17] ok: [mrg19] TASK [load share object] ******************************************************* changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [export path env variable] ************************************************ ok: [mrg17] ok: [mrg18] ok: [mrg19] TASK [export path env to /root/.bashrc] **************************************** ok: [mrg17] ok: [mrg18] ok: [mrg19] TASK [make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql] ***************** ok: [mrg17] ok: [mrg18] ok: [mrg19] TASK [create libmysqlclient_r.so file for php-5.6] ***************************** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [create datadir] ********************************************************** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [initialize-insecure] ***************************************************** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [create systemd config file] ********************************************** ok: [mrg18] ok: [mrg19] ok: [mrg17] TASK [enable mysqld service] *************************************************** changed: [mrg19] changed: [mrg18] changed: [mrg17] TASK [start mysql(sytemctl)] *************************************************** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [config mysql.service start up on boot] *********************************** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [config sysv start script] ************************************************ skipping: [mrg17] skipping: [mrg18] skipping: [mrg19] TASK [start mysql(service)] **************************************************** skipping: [mrg17] skipping: [mrg18] skipping: [mrg19] TASK [config mysql.service start up on boot] *********************************** skipping: [mrg17] skipping: [mrg18] skipping: [mrg19] TASK [transfer sql statement to remonte] *************************************** changed: [mrg17] changed: [mrg18] changed: [mrg19] TASK [config mysql group replication] ******************************************************* changed: [mrg17] changed: [mrg19] changed: [mrg18] TASK [remove temp file /tmp/config_mysql_group_replication.sql] **************** changed: [mrg17] changed: [mrg18] changed: [mrg19] PLAY RECAP ********************************************************************* mrg17 : ok=21 changed=14 unreachable=0 failed=0 mrg18 : ok=21 changed=14 unreachable=0 failed=0 mrg19 : ok=21 changed=14 unreachable=0 failed=0
看样子、mysql-group-replication集群是安装成功了、下面我给出mysql-5.7.20 在可用性方面的表现。
四、测试一下mysql-group-replication中各个成员的状态是否正常
select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 8cd24f4c-c51c-11e7-85a8-9a17854b700d | mtls17 | 3306 | ONLINE | | group_replication_applier | 8d6dd76f-c51c-11e7-bff6-1e1b3511358e | mtsl18 | 3306 | ONLINE | | group_replication_applier | 8fc1d79a-c51c-11e7-b9f1-8a7c439b72d9 | mtls19 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)
结论： 各个成员状态正常
五、测试primary成员是否能写入
mysql> create database tempdb char set utf8; Query OK, 1 row affected (0.18 sec) mysql> mysql> use tempdb; Database changed mysql> mysql> create table person( -> id int not null auto_increment primary key, -> name varchar(4), -> v int -> ) char set utf8; Query OK, 0 rows affected (0.02 sec) mysql> mysql> insert into person(name,v) values('张三',100),('李四',200),('王五',300); Query OK, 3 rows affected (0.21 sec) Records: 3 Duplicates: 0 Warnings: 0
结论： primary 能正常写入
六、测试seconde结点是否正常的同步到了数据
[root@actionsky18 ~]# mysql tempdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.20-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from person; +----+--------+------+ | id | name | v | +----+--------+------+ | 7 | 张三 | 100 | | 14 | 李四 | 200 | | 21 | 王五 | 300 | +----+--------+------+ 3 rows in set (0.01 sec)
结论：seconde 成员能正常同步到数据
七、kill 掉primary 成员、观察剩余成员的状态是否正常
在mtls17上执行(当前的primary在mtls17主机上)
[root@mtls17 mysql]# ps -ef | grep mysql mysql 27928 1 0 02:07 ? 00:00:09 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf root 29030 15848 0 02:24 pts/0 00:00:00 grep --color=auto mysql [root@mtls17 mysql]# kill -9 27928
在mtls18上剩余成员是否正常
select * from replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 8d6dd76f-c51c-11e7-bff6-1e1b3511358e | mtsl18 | 3306 | ONLINE | | group_replication_applier | 8fc1d79a-c51c-11e7-b9f1-8a7c439b72d9 | mtls19 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.01 sec)
结论：kill 掉primary 成员后、剩余的成员状态都正常;
在这里还是补充一下技术上的细节、在安装mysql-group-replication的时候mysqltools默认为会把第一个主机配置成primary,
在primary 宕机后、默认情况下mysql-group-replication会把存活着的mysql中member_id 最小的主机设置为新的primary，
由上面的select语句可知mtls18的member_id是小于mtls19的、所以在mtls17宕机之后新的primary就是mtls18。
八、测试新的primary是否支持写入
mysql> use tempdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_tempdb | +------------------+ | person | +------------------+ 1 row in set (0.00 sec) mysql> insert into person(name,v) values('赵六',100); Query OK, 1 row affected (0.01 sec)
结论：新选出的primary 支持写入
九、 测试之前的primary重启后能否正常加入高可用组
[root@mtls17 ~]# systemctl start mysql [root@mtls17 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> start group_replication; Query OK, 0 rows affected (3.39 sec) mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 8cd24f4c-c51c-11e7-85a8-9a17854b700d | mtls17 | 3306 | ONLINE | | group_replication_applier | 8d6dd76f-c51c-11e7-bff6-1e1b3511358e | mtsl18 | 3306 | ONLINE | | group_replication_applier | 8fc1d79a-c51c-11e7-b9f1-8a7c439b72d9 | mtls19 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> select * from tempdb.person; +----+--------+------+ | id | name | v | +----+--------+------+ | 7 | 张三 | 100 | | 14 | 李四 | 200 | | 21 | 王五 | 300 | | 24 | 赵六 | 100 | +----+--------+------+ 4 rows in set (0.00 sec)
结论：老的primary可以正常恢复
十、我写的关于mysql group replication 的相关文章
1、mysql group replication 安装与配置详解：http://www.cnblogs.com/JiangLe/p/6727281.html#3849996
2、mysql group replication 在mysql-5.7.20版本下的可用性报告：http://www.cnblogs.com/JiangLe/p/7809229.html
3、mysql group replication 主节宕机点恢复 https://i.cnblogs.com/EditPosts.aspx?postid=7941929
4、mysql group replication 多数据结点丢失情况下的恢复
5、我写的全自动化安装mysql-group-replication 开源工具在我的测试环境下安装mysql group replication 只要72秒 https://github.com/Neeky/mysqltools
