Mysql+KeepAlived双主热备可用性测试 mysql

正常环境
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: 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: 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: 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.测试写入新数据

文章图片

【Mysql+KeepAlived双主热备可用性测试】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: 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.查看从库负载