mysql 之 主从双机 加 keepalived
接着上一篇《配置mysql数据库的主从同步实验 》，本文主要从应用的角度，在两台mysql服务器上部署keepalived服务，可以做到在任意一台mysql服务器故障的情况下，不影响mysql数据库的使用。应用服务器配置的mysql数据库的地址是一个VIP（业务虚拟地址），这个VIP作为keepalived中的virtual_server地址，keepalived的real_server地址分别是两台mysql数据库服务器的地址。
1、安装keepalived 。在两台服务器依次做以下操作
yum install keepalived -y
2、对keepalived配置文件进行配置。在MySQL-01上进行操作
先将/etc/keepalived/keepalived.conf文件清空，然后将下面内容复制进去：
! Configuration File for keepalived
global_defs {
notification_email {
yuwb@zycsy.cn
}
notification_email_from yuwb@zycsy.cn
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-ha
}
vrrp_instance VI_1 {
state master
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.8.243
}
}
virtual_server 192.168.8.243 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 192.168.8.241 3306 {
weight 3
notify_down /var/lib/mysql/killkeepalived.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
在MySQL-02上进行操作：
先将/etc/keepalived/keepalived.conf文件清空，将上面的内容中的：
“ real_server 192.168.8.241 3306 ”改为：
“ real_server 192.168.8.242 3306 ”
vrrp部分改为：state BACKUP,priority 80
在两台服务器做以下操作 ：
vim /var/lib/mysql/killkeepalived.sh
#!/bin/sh
service keepalived stop
chmod +x /var/lib/mysql/killkeepalived.sh
3、测试是否可用
创建一个测试用户 ：
mysql> grant all privileges on *.* to 'test'@'%' identified by '123456';
mysql> flush privileges;
在MySQL-01上多开一个终端，tailf /var/log/messages,然后再另一个终端启动keepalived服务service keepalived start。
在MySQL-02同样打开一个新的终端查看日志信息,然后启动keepalived
可以通过 ip addr 命令查看keepalived其的VIP是192.168.8.243
# ip addr
1: lo:mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0:mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
link/ether 08:00:27:a3:63:54 brd ff:ff:ff:ff:ff:ff
inet 192.168.8.241/24 brd 192.168.8.255 scope global eth0
inet 192.168.8.243/32 scope global eth0
inet6 fe80::a00:27ff:fea3:6354/64 scope link
valid_lft forever preferred_lft forever
在MySQL-01上手动停掉MySQL服务。同时得到日志信息:
Feb 24 09:22:45 localhost Keepalived_healthcheckers: TCP connection to :3306 failed !!!
Feb 24 09:22:45 localhost Keepalived_healthcheckers: Removing service :3306 from VS :3306
Feb 24 09:22:45 localhost Keepalived_healthcheckers: Executing for service :3306 in VS :3306
Feb 24 09:22:45 localhost Keepalived_healthcheckers: Lost quorum 1-0=1 > 0 for VS :3306
Feb 24 09:22:45 localhost Keepalived_healthcheckers: Remote SMTP server :25 connected.
Feb 24 09:22:45 localhost kernel: IPVS: __ip_vs_del_service: enter
Feb 24 09:22:45 localhost Keepalived: Stopping Keepalived v1.2.13 (03/19,2015)
Feb 24 09:22:45 localhost Keepalived_vrrp: VRRP_Instance(VI_1) sending 0 priority
Feb 24 09:22:45 localhost Keepalived_vrrp: VRRP_Instance(VI_1) removing protocol VIPs.
Feb 24 09:22:45 localhost Keepalived_healthcheckers: Netlink reflector reports IP 192.168.8.243 removed
在MySQL-02上的日志信息如下：
Feb 24 09:22:46 localhost Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE
Feb 24 09:22:47 localhost Keepalived_vrrp: VRRP_Instance(VI_1) Entering MASTER STATE
Feb 24 09:22:47 localhost Keepalived_vrrp: VRRP_Instance(VI_1) setting protocol VIPs.
Feb 24 09:22:47 localhost Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.8.243
Feb 24 09:22:47 localhost Keepalived_healthcheckers: Netlink reflector reports IP 192.168.8.243 added
Feb 24 09:22:52 localhost Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.8.243
在两台服务器上执行ip addr命令查看，发现VIP地址192.168.8.243已经切换到了MySQL-02上。因此也就实现了应用连接数据库的自动切换。
此时可以使用连接MySQL的工具测试一下，虚拟ip地址还是可用的。如下所示：
# mysql -u test -p123456 -h 192.168.8.243
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 243
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, 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>
注意/etc/keepalived/keepalived.conf配置文件中的notify_down 参数是：
/var/lib/mysql/killkeepalived.sh
也就是说检测到3306端口关闭后，keepalived会执行这个脚本，这个脚本的内容是关闭keepalived本身，会将VIP地址remove掉。
所以，当故障主机mysql数据库恢复正常之后，需要手动将keepalived 服务启动起来：
service keepalived start
以上的配置做到这个程度，我个人认为已经够了。虽然还可以继续深入，通过优化keepalived配置脚本提高自动化水平，但是在生产环境中，我们会对主机 操作系统本身、mysql数据库做监控预警，因此当首次出现故障的时候，以上的配置可以保障业务正常运行，后面再人工介入查找并解决问题。因为如果出现故障，无论再怎么自动化，最终还是需要人工介入进行故障分析。
页:
[1]