方案简介
由keepalive实现VIP转移,从而实现高可用
环境规划
角色
IP
用途
Master
A
192.168.56.101
MySQL
DB
Master
B
192.168.56.102
VIP
192.168.56.110
KeepAlived
VIP
软件环境
OS Version:Red Hat
Enterprise Linux Server release 6.3 (Santiago)
MySQL Version:MySQL-server-5.6.21-1.el6.x86_64
KeepAlived Version:keepalived-1.2.13
部署步骤
1)配置MySQL主主
详细步骤省略…………..
Master
A>show processlist\G
***************************
1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 9654
State: Slave has read all relay log; waiting
for the slave I/O thread to update it
Info: NULL
***************************
2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 12773
State: Waiting for master to send event
Info: NULL
***************************
3. row ***************************
Id: 1677
User: slave
Host: 192.168.56.102:34325
db: NULL
Command:
Binlog Dump
Time: 9678
State: Master has sent all binlog to slave;
waiting for binlog to be updated
Info: NULL
Master
B>show processlist \G
***************************
1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 9771
State: Waiting for master to send event
Info: NULL
***************************
2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 9770
State: Slave has read all relay log; waiting
for the slave I/O thread to update it
Info: NULL
***************************
3. row ***************************
Id: 12
User: slave
Host: 192.168.56.101:46709
db: NULL
Command: Binlog
Dump
Time: 9746
State: Master has sent all binlog to slave;
waiting for binlog to be updated
Info: NULL
2)安装KeepAlived
#A和B主机都安装
]#
tar -zxvf keepalived-1.2.13.tar.gz -C /opt/
]# cd
/opt/keepalived-1.2.13/
]#
mkdir /usr/local/keepalived
]#
./configure --prefix=/usr/local/keepalived/
]#
make;make install
问题:configure报错
configure: error:
!!! OpenSSL is not properly installed on your
system. !!!
!!! Can not include OpenSSL headers
files. !!!
]# rpm -ivh
keyutils-libs-devel-1.4-4.el6.x86_64.rpm
]# rpm -ivh
libcom_err-devel-1.41.12-12.el6.x86_64.rpm
]# rpm -ivh
pkgconfig-0.23-9.1.el6.x86_64.rpm
]# rpm -ivh
libselinux-devel-2.0.94-5.3.el6.x86_64.rpm
]# rpm -ivh
krb5-devel-1.9-33.el6.x86_64.rpm
]# rpm -ivh
zlib-devel-1.2.3-27.el6.x86_64.rpm
]# rpm -ivh
openssl-devel-1.0.0-20.el6_2.5.x86_64.rpm
3)配置KeepAlived
Create
Control File
默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
#A主机和B主机都操作
]#
mkdir /etc/keepalived
#cp安装生成的conf模板到/etc/keepalived
]# cp
/usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
Master
A配置
a)Master A配置文件
#Master A配置文件
~]#
vim /etc/keepalived/keepalived.conf
! Configuration
File for keepalived
#全局配置
global_defs {
#表示keepalived在发生诸如切换操作时发送Email给哪些地址,邮件地址可以多个,每行一个
notification_email {
jixiang.yu@trekiz.com
}
#表示发送通知邮件时邮件源地址是谁
#表示发送email时使用的smtp服务器地址,这里可以用本地的sendmail来实现
smtp_server 127.0.0.1
#连接smtp连接超时时间
smtp_connect_timeout 30
#机器标识
router_id MySQL_HA
}
vrrp_instance
MySQL-HA{
state
BACKUP #state指定instance的初始状态,但这里指定的不算,还是得通过优先级竞选来确定。两台配置此处均是BACKUP。
interface eth1#实例绑定的网卡,因为在配置虚拟IP的时候必须是在已有的网卡上添加的
virtual_router_id
51#这里设置VRID,这里非常重要,相同的VRID为一个组,他将决定多播的MAC地址
priority
100 #设置本节点的优先级,优先级高的为master,如另外一个节点配置为90,那此节点就是master
advert_int 1 #检查间隔,默认为1秒
nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress { #这里设置的就是VIP,也就是虚拟IP地址
192.168.56.111
}
}
virtual_server
192.168.56.111 3306{
delay_loop 2#每个2秒检查一次real_server状态
lb_algo wrr
lb_kind DR
persistence_timeout 50#会话保持时间
protocol TCP
real_server 192.168.56.101 3306{
weight 3
notify_down
/usr/local/MySQL/bin/MySQL.sh#检测到服务down后执行的脚本
TCP_CHECK {
connect_timeout 3#连接超时时间
nb_get_retry 3#重连次数
delay_before_retry 3#重连间隔时间
connect_port 3306#健康检查端口
}
}
}
b)Master A编写notify_down脚本
~]#
mkdir -p /usr/local/MySQL/bin/
~]#
vim /usr/local/MySQL/bin/MySQL.sh
#!/bin/sh
pkill keepalived
注:此脚本是上面配置文件notify_down选项所用到的,keepalived使用notify_down选项来检查real_server的服务状态,当发现real_server服务故障时,便触发此脚本;我们可以看到,脚本就一个命令,通过pkill keepalived强制杀死keepalived进程,从而实现了MySQL故障自动转移。另外,我们不用担心两个MySQL会同时提供数据更新操作,因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP,而不是两台MySQL的IP+VIP
c)Master A启动KeepAlived服务
~]#
/usr/local/keepalived/sbin/keepalived –D
~]#
ps aux|grep keep
root 29423
0.0 0.1 39484
764 ? Ss 20:27
0:00 /usr/local/keepalived/sbin/keepalived -D
root 29424
0.0 0.4 43660
2056 ? S 20:27
0:00 /usr/local/keepalived/sbin/keepalived -D
root 29425
0.0 0.2 43660
1368 ? S 20:27
0:00 /usr/local/keepalived/sbin/keepalived -D
root 29439
0.0 0.1 103240 840 pts/2
S+ 20:27 0:00 grep keep
#可以看到VIP已经飘在eth1上
~]#
ip a|grep eth1
3: eth1:
mtu 1500 qdisc pfifo_fast state UP qlen
1000
inet 192.168.56.101/24 brd 192.168.56.255
scope global eth1
inet 192.168.56.111/32 scope global eth1
d)Master A测试keepalived服务
#停止MySQL服务
~]#
/etc/init.d/mysql stop
Shutting down
MySQL.....
[ OK ]
#查看vip,发现VIP已经不存在
~]#
ip a|grep eth1
3: eth1:
mtu 1500 qdisc pfifo_fast state UP qlen
1000
inet 192.168.56.101/24 brd 192.168.56.255
scope global eth1
#查看keepalive进程消失
~]#
ps -ef|grep keep
root 29537
4894 0 20:34 pts/2 00:00:00 grep keep
#监控日志
~]#
tail -f /var/log/messages
Dec 3 20:34:08 MySQL1
Keepalived_healthcheckers[29424]: TCP connection to [192.168.56.101]:3306
failed !!!
Dec 3 20:34:08 MySQL1
Keepalived_healthcheckers[29424]: Removing service [192.168.56.101]:3306 from
VS [192.168.56.111]:3306
Dec 3 20:34:08 MySQL1
Keepalived_healthcheckers[29424]: Executing [/usr/local/MySQL/bin/MySQL.sh] for
service [192.168.56.101]:3306 in VS [192.168.56.111]:3306
Dec 3 20:34:08 MySQL1
Keepalived_healthcheckers[29424]: Lost quorum 1-0=1 > 0 for VS
[192.168.56.111]:3306
Dec 3 20:34:08 MySQL1
Keepalived_healthcheckers[29424]: Remote SMTP server [127.0.0.1]:25 connected.
Dec 3 20:34:09 MySQL1 Keepalived[29423]: Stopping
Keepalived v1.2.13 (12/03,2014)
Dec 3 20:34:09 MySQL1 Keepalived_vrrp[29425]:
VRRP_Instance(MySQL-HA{) sending 0 priority
Dec 3 20:34:09 MySQL1 Keepalived_vrrp[29425]:
VRRP_Instance(MySQL-HA{) removing protocol VIPs.
Dec 3 20:34:09 MySQL1
Keepalived_healthcheckers[29424]: Netlink reflector reports IP 192.168.56.111
removed
Dec 3 20:34:09 MySQL1 avahi-daemon[1430]: Server
startup complete. Host name is MySQL1-62.local. Local service cookie is
1197773774.
Dec 3 20:34:09 MySQL1 avahi-daemon[1430]:
Withdrawing address record for 192.168.56.111 on eth1.
Dec 3 20:34:10 MySQL1 avahi-daemon[1430]: Service
"MySQL1-62" (/services/ssh.service) successfully established.
Master
B配置
a)Master B配置文件
#跟DB1基本一致,但有三个地方不同:优先级为90、无抢占设置、real_server为本机IP
DB2~]#
vim /etc/keepalived/keepalived.conf
! Configuration
File for keepalived
global_defs {
notification_email {
jixiang.yu@trekiz.com
}
notification_email_from
jixiang.yu@trekiz.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL_HA
}
vrrp_instance
MySQL-HA{
state BACKUP
interface eth1
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.111
}
}
virtual_server
192.168.56.111 3306{
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 192.168.56.102
3306{
weight 3
notify_down
/usr/local/MySQL/bin/MySQL.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306 #健康检查端口
}
}
}
b)Master B编写notify_down脚本
~]#
mkdir -p /usr/local/MySQL/bin/
~]#
vim /usr/local/MySQL/bin/MySQL.sh
#!/bin/sh
pkill keepalived
c)Master B启动KeepAlived服务
~]#
/usr/local/keepalived/sbin/keepalived –D
~]#
ps aux|grep keep
root 2855
0.0 0.1 39484
764 ? Ss 09:56
0:00 /usr/local/keepalive/sbin/keepalived -D
root 2856
0.0 0.3 41588
1992 ? S 09:56
0:00 /usr/local/keepalive/sbin/keepalived -D
root 2857
0.0 0.2 41588
1288 ? S 09:56
0:00 /usr/local/keepalive/sbin/keepalived -D
root 2865
0.0 0.1 103240 840 pts/2
S+ 09:56 0:00 grep keep
#可以看到VIP已经飘在eth1上
~]#
ip a|grep eth1
3: eth1:
mtu 1500 qdisc pfifo_fast state UP qlen
1000
inet 192.168.56.102/24 brd 192.168.56.255
scope global eth1
inet 192.168.56.111/32 scope global eth1
d)Master B测试keepalived服务
#停止MySQL服务
~]#
/etc/init.d/mysql stop
Shutting down
MySQL.....
[ OK ]
#查看vip,发现VIP已经不存在
~]#
ip a|grep eth1
3: eth1:
mtu 1500 qdisc pfifo_fast state UP qlen
1000
inet 192.168.56.101/24 brd 192.168.56.255
scope global eth1
#查看keepalive进程消失
~]#
ps -ef|grep keep
root 29537
4894 0 20:34 pts/2 00:00:00 grep keep
#监控日志
~]#
tail -f /var/log/messages
Dec 4 10:35:53 MySQL2
Keepalived_healthcheckers[6733]: TCP connection to [192.168.56.102]:3306 failed
!!!
Dec 4 10:35:53 MySQL2
Keepalived_healthcheckers[6733]: Removing service [192.168.56.102]:3306 from VS
[192.168.56.111]:3306
Dec 4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]:
Executing [/usr/local/MySQL/bin/MySQL.sh] for service [192.168.56.102]:3306 in
VS [192.168.56.111]:3306
Dec 4 10:35:53 MySQL2
Keepalived_healthcheckers[6733]: Lost quorum 1-0=1 > 0 for VS
[192.168.56.111]:3306
Dec 4 10:35:53 MySQL2
Keepalived_healthcheckers[6733]: Remote SMTP server [127.0.0.1]:25 connected.
Dec 4 10:35:53 MySQL2 Keepalived[6732]: Stopping
Keepalived v1.2.13 (11/24,2014)
Dec 4 10:35:53 MySQL2 Keepalived_vrrp[6734]:
VRRP_Instance(MySQL-HA{) sending 0 priority
Dec 4 10:35:53 MySQL2 Keepalived_vrrp[6734]:
VRRP_Instance(MySQL-HA{) removing protocol VIPs.
Dec 4 10:35:53 MySQL2
Keepalived_healthcheckers[6733]: Netlink reflector reports IP 192.168.56.111
removed
Dec 4 10:35:54 MySQL2 avahi-daemon[1387]:
Withdrawing address record for 192.168.56.111 on eth1.
4)MySQL Client使用VIP连接数据库
~]#
mysql -u trekiz -h 192.168.56.111 -p
Enter password:
Welcome to the
MySQL monitor. Commands end with ; or
\g.
Your MySQL
connection id is 2622
Server version:
5.6.21-log MySQL Community Server (GPL)
Copyright (c) 2000,
2014, 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>
5)配置init.d/keepalived脚本
cp
/usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp
/usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp
/usr/local/keepalived/sbin/keepalived /sbin/
~]#
/etc/init.d/keepalived -h
Usage:
/etc/init.d/keepalived {start|stop|reload|restart|condrestart|status}
6)KeepAlived Failover Test
a)
Master A,B都启动keepalived,vip在A上,此时A主机pkill keepalived
mysql>
show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
|
master_slave.000031 | 532 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00
sec)
mysql>
show master status;
ERROR 2013 (HY000):
Lost connection to MySQL server during query
mysql>
show master status;
ERROR 2006 (HY000):
MySQL server has gone away
No connection.
Trying to reconnect...
Connection id: 168
Current database:
*** NONE ***
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
|
master_a.000021 | 532 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00
sec)
结果
VIP就会切换到Master B上面去
b)
启动Master
A的keepalived,vip在B上,B主机pkill keepalived
mysql>
show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
|
master_a.000021 | 532 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00
sec)
mysql>
show master status;
ERROR 2013 (HY000):
Lost connection to MySQL server during query
mysql>
show master status;
ERROR 2006 (HY000):
MySQL server has gone away
No connection.
Trying to reconnect...
Connection id: 2845
Current database:
*** NONE ***
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
|
master_slave.000031 | 532 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.08
sec)
结果
VIP就会切换到Master A上面去
c)
Master A,B都启动keepalived,vip在A上,此时A主机stop mysql
结果
VIP就会切换到Master B上面去
d)
启动Master
A的keepalived,vip在B上,B主机stop mysql
结果
VIP就会切换到Master A上面去