关于mysql:MysqlKeepAlived双主热备可用性测试

44次阅读

共计 6613 个字符，预计需要花费 17 分钟才能阅读完成。

失常环境

1. 环境介绍

测试数据库 1：192.168.1.21
测试数据库 2：192.168.1.20
虚构 IP1（写库）：192.168.1.111
虚构 IP2（读库）：192.168.1.112

2. 查看主从状态 

show slave status

3. 查看 VIP

ip a

#VIP1 192.168.1.21
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:9e:e7:26:5c brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.111/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::5054:9eff:fee7:265c/64 scope link 
       valid_lft forever preferred_lft forever

#VIP2 192.168.1.20
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:9e:17:53:e5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.20/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.112/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::5054:9eff:fe17:53e5/64 scope link 
       valid_lft forever preferred_lft forever

4. 查看 Keepalived 日志 

tail -f /var/log/messages

#192.168.1.21
Aug 24 16:36:14 i-6gxo6kx7 Keepalived_vrrp[4207]: VRRP_Instance(VI_1) Transition to MASTER STATE
Aug 24 16:36:15 i-6gxo6kx7 Keepalived_vrrp[4207]: VRRP_Instance(VI_1) Entering MASTER STATE

#192.168.1.20
Aug 24 16:11:55 i-7jaope92 Keepalived_vrrp[1502]: VRRP_Instance(VI_2) Transition to MASTER STATE
Aug 24 16:11:56 i-7jaope92 Keepalived_vrrp[1502]: VRRP_Instance(VI_2) Entering MASTER STATE

可用性测试

1. 读库宕机测试 (192.168.1.20)

1. 敞开从库 Mysql

service mysql stop

2. 查看 keepalived 日志 

#192.168.1.20
Aug 24 16:11:55 i-7jaope92 Keepalived_healthcheckers[1499]: Activating healthchecker for service [192.168.1.20]:3306
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: TCP connection to [192.168.1.20]:3306 failed !!!
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: Removing service [192.168.1.20]:3306 from VS [192.168.1.111]:3306
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: Executing [/usr/local/bin/mysql.sh] for service [192.168.1.20]:3306 in VS [192.168.1.111]:3306
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: Lost quorum 1-0=1 > 0 for VS [192.168.1.111]:3306
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: TCP connection to [192.168.1.20]:3306 failed !!!
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: Removing service [192.168.1.20]:3306 from VS [192.168.1.112]:3306
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: Executing [/usr/local/bin/mysql.sh] for service [192.168.1.20]:3306 in VS [192.168.1.112]:3306
Aug 24 16:46:57 i-7jaope92 Keepalived_healthcheckers[1499]: Lost quorum 1-0=1 > 0 for VS [192.168.1.112]:3306
Aug 24 16:46:58 i-7jaope92 Keepalived_vrrp[1502]: VRRP_Instance(VI_2) sending 0 priority
Aug 24 16:46:58 i-7jaope92 kernel: [2114.753151] IPVS: __ip_vs_del_service: enter
Aug 24 16:46:58 i-7jaope92 kernel: [2114.753156] IPVS: __ip_vs_del_service: enter

#192.168.1.21
Aug 24 16:46:58 i-6gxo6kx7 Keepalived_vrrp[4207]: VRRP_Instance(VI_2) Transition to MASTER STATE
Aug 24 16:46:59 i-6gxo6kx7 Keepalived_vrrp[4207]: VRRP_Instance(VI_2) Entering MASTER STATE

3. 查看 VIP2

ip a

#192.168.1.21
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:9e:e7:26:5c brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.111/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.112/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::5054:9eff:fee7:265c/64 scope link 
       valid_lft forever preferred_lft forever

可见 VIP2 漂移到了 192.168.1.21

4. 测试写入新数据

5. 重启读库 

service mysql start
service keepalived start

6. 查看 keepalived 日志 

#192.168.1.20
Aug 24 16:52:41 i-7jaope92 Keepalived_vrrp[5279]: VRRP_Instance(VI_2) Transition to MASTER STATE
Aug 24 16:52:42 i-7jaope92 Keepalived_vrrp[5279]: VRRP_Instance(VI_2) Entering MASTER STATE

#192.168.1.21
Aug 24 16:52:41 i-6gxo6kx7 Keepalived_vrrp[4207]: VRRP_Instance(VI_2) Received higher prio advert
Aug 24 16:52:41 i-6gxo6kx7 Keepalived_vrrp[4207]: VRRP_Instance(VI_2) Entering BACKUP STATE

可见 VIP2 漂移回到了 192.168.1.20
7. 查看从库数据


可见新增数据已同步到读库

2. 写库宕机测试 (192.168.1.21)

1. 敞开写库 Mysql

service mysql stop

2. 查看 keepalived 日志 

#192.168.1.20
Aug 24 16:56:56 i-7jaope92 Keepalived_vrrp[5279]: VRRP_Instance(VI_1) Transition to MASTER STATE
Aug 24 16:56:57 i-7jaope92 Keepalived_vrrp[5279]: VRRP_Instance(VI_1) Entering MASTER STATE

#192.168.1.21
Aug 24 16:36:13 i-6gxo6kx7 Keepalived_healthcheckers[4206]: Activating healthchecker for service [192.168.1.21]:3306
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: TCP connection to [192.168.1.21]:3306 failed !!!
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: Removing service [192.168.1.21]:3306 from VS [192.168.1.111]:3306
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: Executing [/usr/local/bin/mysql.sh] for service [192.168.1.21]:3306 in VS [192.168.1.111]:3306
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: Lost quorum 1-0=1 > 0 for VS [192.168.1.111]:3306
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: TCP connection to [192.168.1.21]:3306 failed !!!
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: Removing service [192.168.1.21]:3306 from VS [192.168.1.112]:3306
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: Executing [/usr/local/bin/mysql.sh] for service [192.168.1.21]:3306 in VS [192.168.1.112]:3306
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_healthcheckers[4206]: Lost quorum 1-0=1 > 0 for VS [192.168.1.112]:3306
Aug 24 16:56:55 i-6gxo6kx7 Keepalived_vrrp[4207]: VRRP_Instance(VI_1) sending 0 priority
Aug 24 16:56:55 i-6gxo6kx7 kernel: [2716.629624] IPVS: __ip_vs_del_service: enter
Aug 24 16:56:55 i-6gxo6kx7 kernel: [2716.629646] IPVS: __ip_vs_del_service: enter

3. 查看 VIP1

ip a

#192.168.1.20
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:9e:17:53:e5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.20/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.112/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.111/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::5054:9eff:fe17:53e5/64 scope link 
       valid_lft forever preferred_lft forever

可见 VIP1 漂移到了 192.168.1.20，读库同时承当写入性能

4. 测试写入新数据

5. 重启写库 

service mysql start
service keepalived start

6. 查看 keepalived 日志 

#192.168.1.20
Aug 24 16:59:32 i-7jaope92 Keepalived_vrrp[5279]: VRRP_Instance(VI_1) Received higher prio advert
Aug 24 16:59:32 i-7jaope92 Keepalived_vrrp[5279]: VRRP_Instance(VI_1) Entering BACKUP STATE

#192.168.1.21
Aug 24 16:59:32 i-6gxo6kx7 Keepalived_vrrp[6483]: VRRP_Instance(VI_1) Transition to MASTER STATE
Aug 24 16:59:33 i-6gxo6kx7 Keepalived_vrrp[6483]: VRRP_Instance(VI_1) Entering MASTER STATE

可见 VIP1 漂移回到了 192.168.1.21
7. 查看写库数据

可见在读库新增的数据已同步到写库

负载测试

1. 从库高负载

1. 查看最大连接数 

show variables like 'max_connections';   //151

2. 对从库进行压力测试 

  mysqlslap --defaults-file=/etc/mysql/my.cnf --concurrency=500 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=200000 -uroot -proot --verbose

3. 查看从库负载

正文完
mysql
发表至： mysql
2021-08-24
 0
关于mysql:万答2一样的Python代码为什么可以删表却不能更新数据
关于java:剖析6个MySQL死锁案例的原因以及死锁预防策略
关于mysql:HHDBCS便捷功能简介
关于mybatis:mybatis这样子用起来很舒服