mysql-5.7.20 版本的 mysql-group-replication 可用性测试报告

时间:2022-09-13 18:30:59

一、喜迎 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