本文介绍如何利用Keepalived结合MySQL实现自动故障转移，包括网络结构搭建、Keepalived安装配置、状态检测脚本编写及防火墙设置等关键步骤。
Keepalived+mysql实现mysql自动切换
作者：佚名 和
mysql相关
width="300" height="250" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px; background: 0px 0px;">
id="iframeu1053763_0" src="http://pos.baidu.com/ockm?rdid=1053763&dc=2&di=u1053763&dri=0&dis=0&dai=1&ps=126x1145&dcb=BAIDU_SSP_define&dtm=BAIDU_DUP_SETJSONADSLOT&dvi=0.0&dci=-1&dpt=none&tsr=0&tpr=1463123627241&ti=Keepalived%2Bmysql%E5%AE%9E%E7%8E%B0mysql%E8%87%AA%E5%8A%A8%E5%88%87%E6%8D%A2-mysql-%E7%94%B5%E8%84%91%E7%BC%96%E7%A8%8B%E7%BD%91&ari=1&dbv=2&drs=1&pcs=1760x944&pss=1760x392&cfv=0&cpl=35&chi=1&cce=true&cec=GBK&tlm=1364851170<u=http%3A%2F%2Fbiancheng.dnbcw.info%2Fmysql%2F381020.html<r=https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3Dp5EJaX-Rw6kaG8_3icjOQQcNRS6bKfERuI7yPo31EZ0x7Qd1EdQ7y5SfeZzAQB9Ff2zCWsSHkQp50YqxAdvwG_%26wd%3D%26eqid%3D8cdea17a00030a340000000557357e54&ecd=1&psr=1920x1080&par=1920x1040&pis=-1x-1&ccd=24&cja=false&cmi=75&col=zh-CN&cdo=-1&tcn=1463123627&qn=9812ca7ef01b9722&tt=1463123627142.141.964.979" width="300" height="250" align="center,center" vspace="0" hspace="0" marginwidth="0" marginheight="0" scrolling="no" frameborder="0" allowtransparency="true" style="border-width: 0px; border-style: initial; vertical-align: bottom; margin: 0px; background: 0px 0px;">
Keepalived+
mysql 自动切换
网络结构：
VIP 192.168.88.200
mysq11 192.168.88.130 主
mysql2 192.168.88.131 备
一 mysql 主主同步
二 安装keepalived
三 keepalived 主备配置文件
四 mysql状态检测脚本/root/keepalived_check_mysql.sh
五 防火墙设置
六 启动keepalived
七 查看vrrp通讯记录
八 查看虚拟IP
九 测试
一 mysql 主主同步(略)
说明： 数据库testA和testB分别在mysql1和mysql2上，testDB是通过主主复制获得，便于后面的测试
二 安装keepalived
1.软件包下载地址http://www.keepalived.org
安装keepalived，从官方网站下载keepalived-1.2.2.tar.gz后
# tar xvf keepalived-1.2.2.tar.gz
# cd keepalived-1.2.2
# ./configure
# make && make install
2.查看keepalived位置
[root@lvs-dr1 keepalived-1.2.2]# find / -name "keepalived"
/root/keepalived-1.2.2/keepalived
/root/keepalived-1.2.2/keepalived/etc/keepalived
/root/keepalived-1.2.2/bin/keepalived
/usr/local/sbin/keepalived
/usr/local/etc/rc.d/init.d/keepalived
/usr/local/etc/keepalived
/usr/local/etc/sysconfig/keepalived
3.复制文件
# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
# mkdir /etc/keepalived
# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
# cp /usr/local/sbin/keepalived /usr/sbin/
三 keepalived 主备配置文件
主备置文件不同处有 state nopreempt priority 参考资料里有详细说明作用
192.168.88.130 主配置
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
15301727316@189.cn
}
notification_email_from zalifei@126.com
smtp_connect_timeout 3
smtp_server 127.0.0.1
router_id MySQL-ha
}
vrrp_script check_run {
script "/root/keepalived_check_mysql.sh"
interval 5
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 88
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass skyai1.cublog.cn
}
track_script {
check_run
}
virtual_ipaddress {
192.168.88.200
}
}
复制代码192.168.88.131 备用配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
hkh21@163.com
网络结构：
VIP 192.168.88.200
mysq11 192.168.88.130 主
mysql2 192.168.88.131 备
一 mysql 主主同步
二 安装keepalived
三 keepalived 主备配置文件
四 mysql状态检测脚本/root/keepalived_check_mysql.sh
五 防火墙设置
六 启动keepalived
七 查看vrrp通讯记录
八 查看虚拟IP
九 测试
一 mysql 主主同步(略)
说明： 数据库testA和testB分别在mysql1和mysql2上，testDB是通过主主复制获得，便于后面的测试
二 安装keepalived
1.软件包下载地址http://www.keepalived.org
安装keepalived，从官方网站下载keepalived-1.2.2.tar.gz后
# tar xvf keepalived-1.2.2.tar.gz
# cd keepalived-1.2.2
# ./configure
# make && make install
2.查看keepalived位置
[root@lvs-dr1 keepalived-1.2.2]# find / -name "keepalived"
/root/keepalived-1.2.2/keepalived
/root/keepalived-1.2.2/keepalived/etc/keepalived
/root/keepalived-1.2.2/bin/keepalived
/usr/local/sbin/keepalived
/usr/local/etc/rc.d/init.d/keepalived
/usr/local/etc/keepalived
/usr/local/etc/sysconfig/keepalived
3.复制文件
# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
# mkdir /etc/keepalived
# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
# cp /usr/local/sbin/keepalived /usr/sbin/
三 keepalived 主备配置文件
主备置文件不同处有 state nopreempt priority 参考资料里有详细说明作用
192.168.88.130 主配置
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
15301727316@189.cn
}
notification_email_from zalifei@126.com
smtp_connect_timeout 3
smtp_server 127.0.0.1
router_id MySQL-ha
}
vrrp_script check_run {
script "/root/keepalived_check_mysql.sh"
interval 5
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 88
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass skyai1.cublog.cn
}
track_script {
check_run
}
virtual_ipaddress {
192.168.88.200
}
}
复制代码192.168.88.131 备用配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
hkh21@163.com
hhlm21@163.com
}
notification_email_from hkh21@163.com
smtp_connect_timeout 3
smtp_server 127.0.0.1
router_id MySQL-ha
}
vrrp_script check_run {
script "/root/keepalived_check_mysql.sh"
interval 5
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 88
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass skyai1.cublog.cn
}
track_script {
check_run
}
virtual_ipaddress {
192.168.88.200
}
}
四 mysql状态检测脚本/root/keepalived_check_mysql.sh
# vim /root/keepalived_check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/webserver/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=mysql
CHECK_TIME=3
#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done
赋予执行权限
# chmod +x /root/keepalived_check_mysql.sh
五 防火墙设置
vrrp协议 使用224.0.0.18地址组播
iptables -I RH-Firewall-1-INPUT -d 224.0.0.18 -j ACCEPT
六 启动keepalived
# service keepalived start
七 查看vrrp通讯记录
# tcpdump vrrp
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
11:51:45.632044 IP 192.168.88.130 > vrrp.mcast.net: VRRPv2, Advertisement, vrid 88, prio 100, authtype simple, intvl 1s, length 20
八 查看虚拟IP
# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue
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: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
link/ether 00:0c:29:bf:c5:77 brd ff:ff:ff:ff:ff:ff
inet 192.168.88.130/24 brd 192.168.88.255 scope global eth0
inet 192.168.88.200/32 scope global eth0
inet6 fe80::20c:29ff:febf:c577/64 scope link
valid_lft forever preferred_lft forever
3: sit0: <NOARP> mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0
九 测试
# mysql -uroot -h192.168.88.200 -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| testA |
| testDB |
+--------------------+
5 rows in set (0.00 sec)
停掉主 数据库服务，测试
# service mysqld stop
# mysql -uuser -h "192.168.88.200" -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| testB |
| testDB |
+--------------------+
5 rows in set (0.02 sec)
到这里，测试完成
}
notification_email_from hkh21@163.com
smtp_connect_timeout 3
smtp_server 127.0.0.1
router_id MySQL-ha
}
vrrp_script check_run {
script "/root/keepalived_check_mysql.sh"
interval 5
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 88
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass skyai1.cublog.cn
}
track_script {
check_run
}
virtual_ipaddress {
192.168.88.200
}
}
四 mysql状态检测脚本/root/keepalived_check_mysql.sh
# vim /root/keepalived_check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/webserver/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=mysql
CHECK_TIME=3
#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done
赋予执行权限
# chmod +x /root/keepalived_check_mysql.sh
五 防火墙设置
vrrp协议 使用224.0.0.18地址组播
iptables -I RH-Firewall-1-INPUT -d 224.0.0.18 -j ACCEPT
六 启动keepalived
# service keepalived start
七 查看vrrp通讯记录
# tcpdump vrrp
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
11:51:45.632044 IP 192.168.88.130 > vrrp.mcast.net: VRRPv2, Advertisement, vrid 88, prio 100, authtype simple, intvl 1s, length 20
八 查看虚拟IP
# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue
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: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
link/ether 00:0c:29:bf:c5:77 brd ff:ff:ff:ff:ff:ff
inet 192.168.88.130/24 brd 192.168.88.255 scope global eth0
inet 192.168.88.200/32 scope global eth0
inet6 fe80::20c:29ff:febf:c577/64 scope link
valid_lft forever preferred_lft forever
3: sit0: <NOARP> mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0
九 测试
# mysql -uroot -h192.168.88.200 -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| testA |
| testDB |
+--------------------+
5 rows in set (0.00 sec)
停掉主 数据库服务，测试
# service mysqld stop
# mysql -uuser -h "192.168.88.200" -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| testB |
| testDB |
+--------------------+
5 rows in set (0.02 sec)
到这里，测试完成
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
