Mysql8.0高可用集群测试
一、测试MGR高可用
1、测试场景:MGR master故障
故障前:目前主是在mgr02(213)上,10代表写,30代表读
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 10 | 192.168.102.213 | 3306 | ONLINE |
| 30 | 192.168.102.212 | 3306 | ONLINE |
| 30 | 192.168.102.68 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
3 rows in set (0.00 sec)
故障中:
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.26 sec)
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+---------+
| 10 | 192.168.102.212 | 3306 | ONLINE |
| 40 | 192.168.102.213 | 3306 | SHUNNED |
| 30 | 192.168.102.68 | 3306 | ONLINE |
+--------------+-----------------+------+---------+
3 rows in set (0.01 sec)
恢复后:主变成了mgr01(212)上,10代表写,30代表读
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 10 | 192.168.102.212 | 3306 | ONLINE |
| 30 | 192.168.102.213 | 3306 | ONLINE |
| 30 | 192.168.102.68 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
3 rows in set (0.00 sec)
测试结果:复制组选出新master,宕机节点踢出复制组,ProxySQL识别到新主,负载均衡比例变化;
2、测试场景:MGR slave故障
故障中:mgr03宕掉了
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+---------+
| 10 | 192.168.102.212 | 3306 | ONLINE |
| 40 | 192.168.102.68 | 3306 | SHUNNED |
| 30 | 192.168.102.213 | 3306 | ONLINE |
+--------------+-----------------+------+---------+
3 rows in set (0.00 sec)
恢复后:
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 10 | 192.168.102.212 | 3306 | ONLINE |
| 30 | 192.168.102.68 | 3306 | ONLINE |
| 30 | 192.168.102.213 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
3 rows in set (0.00 sec)
测试结果: 复制组master不变,宕机节点踢出复制组,ProxySQL负载均衡比例变化
3、测试场景:MGR master故障和MGR slave故障两台同时故障
故障中: mgr01和mgr02同时宕掉
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+---------+
| 10 | 192.168.102.68 | 3306 | ONLINE |
| 40 | 192.168.102.213 | 3306 | SHUNNED |
| 40 | 192.168.102.212 | 3306 | SHUNNED |
+--------------+-----------------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 30 after 10000ms
恢复后:
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | PRIMARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
测试结果:复制组master不变,宕机节点踢出复制组,ProxySQL负载均衡比例变化,是有感知的,读存在问题
4、测试场景:MGR三台都故障
测试结果:MGR三台同时宕掉,无法进行访问
恢复:
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 30 | 192.168.102.213 | 3306 | ONLINE |
| 10 | 192.168.102.212 | 3306 | ONLINE |
| 30 | 192.168.102.68 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
3 rows in set (0.01 sec)
二、测试Proxysql高可用
1、测试场景:Proxysql使用节点宕掉,看vip是否飘到另一台(两台proxysql宕掉同理)
故障前:vip是在mgr02上,通过192.168.102.15可以正常进行访问
[root@mgr01 ~]# mysql -uproxysql -pproxysql@1025 -h192.168.102.15 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 8.0.26 (ProxySQL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
[root@mgr02 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 12:34:56:78:96:18 brd ff:ff:ff:ff:ff:ff
inet 192.168.102.213/22 brd 192.168.103.255 scope global noprefixroute ens160
valid_lft forever preferred_lft forever
inet 192.168.102.15/24 scope global ens160
valid_lft forever preferred_lft forever
inet6 fe80::ef48:def0:4499:80ef/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::37bf:4ad7:b805:198a/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::30ad:fb50:af40:6b75/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
故障中:停掉mgr02的Proxysql,vip已经飘到mgr01了,通过192.168.102.15可以正常进行访问
[root@mgr01 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 12:34:56:78:96:17 brd ff:ff:ff:ff:ff:ff
inet 192.168.102.212/22 brd 192.168.103.255 scope global noprefixroute dynamic ens160
valid_lft 141381sec preferred_lft 141381sec
inet 192.168.102.15/24 scope global ens160
valid_lft forever preferred_lft forever
inet6 fe80::ef48:def0:4499:80ef/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::37bf:4ad7:b805:198a/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::30ad:fb50:af40:6b75/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
[root@mgr01 ~]# mysql -uproxysql -pproxysql@1025 -h192.168.102.15 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 8.0.26 (ProxySQL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
恢复后:会按照priority参数,越大优先级越高,会飘到优先级最高的服务器上
[root@mgr03 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 12:34:56:78:98:16 brd ff:ff:ff:ff:ff:ff
inet 192.168.102.68/22 brd 192.168.103.255 scope global noprefixroute dynamic ens160
valid_lft 132086sec preferred_lft 132086sec
inet 192.168.102.15/24 scope global ens160
valid_lft forever preferred_lft forever
inet6 fe80::5f72:acde:162f:cb2f/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@mgr03 ~]# mysql -uproxysql -pproxysql@1025 -h192.168.102.15 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 8.0.26 (ProxySQL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
2、测试场景:Proxysql使用三个节点都宕掉
测试结果:Proxysql使用三个节点都宕掉,无法通过proxysql进行连接数据库,客户端无法访问
恢复后:
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 10 | 192.168.102.212 | 3306 | ONLINE |
| 30 | 192.168.102.213 | 3306 | ONLINE |
| 30 | 192.168.102.68 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
3 rows in set (0.01 sec)
三、总结
1、无感知故障
1.MGR master故障:复制组选出新master,宕机节点踢出复制组,ProxySQL识别到新主,负载均衡比例变化;
2.MGR slave故障:复制组master不变,宕机节点踢出复制组,ProxySQL负载均衡比例变化;
3.ProxySQL使用节点故障(两个PorxySQL都宕掉):复制组不变,会按照priority参数,越大优先级越高,会飘到优先级最高的服务器上;
4.MGR master和ProxySQL使用节点同时故障:复制组选出新master,宕机节点踢出复制组,ProxySQL识别到新主,Keepalived的VIP漂移到优先级最高的服务器上,负载均衡比例变化;
2、感知故障
1、三台mysql数据库宕掉两台及以上
2、三台Proxysql三台都宕掉