一簡介
Keepalived+lvs+mysql主主復制是比較常用的一種Mysql高可用方案，其中lvs 提供讀負載均衡，Keepalived通過虛擬vip漂移實現故障自動轉移，而Mysql雖然配置成主主復制，但是一般都是采用單點寫入來保證數據一致性。
整個方案實現非常簡單，僅需兩台服務器即可，安裝配置也相對簡單，但需要自己寫腳本實現keepalived服務的開啟和關閉，適用於只有兩台數據庫服務器並且未實現讀寫分離的場景。系統整體結構框圖如下
二環境搭建
2.1 環境配置如下
Keepalived
Server
LVS
Server
LVS
Realserver
MySQL
Master-Master
Write VIP
Read VIP
OS
MySQL
Master
10.0.11.243
10.0.11.243
10.0.11.243
10.0.11.243
10.0.11.190
10.0.11.191
Centos6
Percona5.6
Backup
10.0.11.244
10.0.11.244
10.0.11.244
10.0.11.244
10.0.11.190
10.0.11.191
Centos6
Percona5.6
2.2 mysql master-master復制配置
主主復制配置本質上就是配置兩台服務器互相的主從，實現比較簡單，大致步驟如下
2.2.1 在Master上創建一個復制賬戶
mysql>grantreplication slave on *.* to rep71@10.0.11.244identified by‘123456’;
mysql>flushprivileges;
2.2.2 修改兩台mysql配置文件my.cnf如下
##Replication General Config (both master and slave) server-id= 101 log-bin= mysql-log-bin auto-increment-increment= 2 auto-increment-offset= 1 binlog_format= row relay_log= mysql-relay-bin
2.2.3 在Master服務器上執行show master status，記錄下File和Position
mysql>show master status\G; ***************************1. row *************************** File:mysql-log-bin.000005 Position:327933 Binlog_Do_DB: Binlog_Ignore_DB:test,mysql,information_schema,performance_schema
2.2.4 在Slave服務上執行
mysql> slave stop; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to -> master_host='10.0.11.243', -> master_user='rep71', -> master_password='123456', -> master_log_file=' mysql-log-bin.000005', -> master_log_pos=327933; Query OK, 0 rows affected (0.01 sec) mysql> start slave;
2.2.5重復以上四個步驟，在Slave服務器上創建賬戶rep72，show master status，在Master上start slave，這樣mysql的主主同步就配置好了，分別在兩台服務器上運行show slave status，如果結果都顯示Slave_IO_Running: Yes，Slave_SQL_Running: Yes，則證明同步正常。
2.3 Keepalived的安裝
2.3.1 在 master、backup 服務器都進行安裝：
wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz sudotar zxvf ipvsadm-1.24.tar.gz cdipvsadm-1.24 sudoln -s /usr/src/kernels/2.6.32-358.el6.x86_64/ /usr/src/linux make sudomake install
2.4 Keepalived 的安裝
2.4.1在 master、backup 服務器都進行安裝：
wget http://www.keepalived.org/software/keepalived-1.1.19.tar.gz tarzxvf keepalived-1.1.19.tar.gz cdkeepalived-1.1.19 yuminstall -y openssl openssl-devel sudoyum install popt-devel –y ./configure--sysconf=/etc/ --with-kernel-dir=/usr/src/kernels/2.6.18-308.el5-x86_64/ make sudomake install ln -s /usr/local/sbin/keepalived /sbin/
2.4.2 Master上的Keepalived 的配置
global_defs{
router_id mysqlmha1
}
vrrp_scriptcheck_run {
script"/etc/keepalived/check_mysqll.sh"
interval 1
}
vrrp_instanceVI_1 {
state MASTER
interface em1
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}
virtual_ipaddress {
10.0.11.190 dev em1 label em1:1
10.0.11.191 dev em1 label em1:2
}
}
virtual_server10.0.11.190 3306 {
delay_loop6
lb_algorr
lb_kindDR
persistence_timeout20
protocolTCP
sorry_server10.0.11.244 3306
real_server10.0.11.243 3306 {
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port3306
}
}
}
virtual_server10.0.11.191 3306 {
delay_loop6
lb_algorr
lb_kindDR
#persistence_timeout20
protocolTCP
real_server10.0.11.243 3306 {
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port3306
}
}
real_server10.0.11.244 3306 {
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port3306
}
}
}
2.4.3 Backup 的 keepalived 的配置
global_defs {
router_id mysqlmha1
}
vrrp_script check_run {
script "/etc/keepalived/check_mysqll.sh"
interval 1
}
vrrp_instance VI_1 {
state BACKUP
interface em1
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}
virtual_ipaddress {
10.0.11.190 dev em1 label em1:1
10.0.11.191 dev em1 label em1:2
}
}
virtual_server 10.0.11.190 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
persistence_timeout 20
protocol TCP
sorry_server 10.0.11.244 3306
real_server 10.0.11.243 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
virtual_server 10.0.11.191 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
#persistence_timeout 20
protocol TCP
real_server 10.0.11.243 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
real_server 10.0.11.244 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
2.5 Master 和 backup 的 realserver 的配置
對於 realserver 的配置 master 和 backup 是一致的，腳本內容如下：
#!/bin/bash
#description: Config realserver lo and apply noarp
SNS_VIP=10.1.11.190
SNS_VIP2=10.0.11.191
/etc/rc.d/init.d/functions
case"$1" in
start)
ifconfiglo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
ifconfiglo:1 $SNS_VIP2 netmask 255.255.255.255 broadcast $SNS_VIP2
/sbin/routeadd -host $SNS_VIP dev lo:0
/sbin/routeadd -host $SNS_VIP2 dev lo:1
echo"1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo"2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo"1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo"2" >/proc/sys/net/ipv4/conf/all/arp_announce
echo"1" >/proc/sys/net/ipv4/conf/em1/arp_ignore
echo"2" >/proc/sys/net/ipv4/conf/em1/arp_announce
sysctl-p >/dev/null 2>&1
echo"RealServer Start OK"
;;
stop)
ifconfiglo:0 down
ifconfiglo:1 down
routedel $SNS_VIP >/dev/null 2>&1
routedel $SNS_VIP2 >/dev/null 2>&1
echo"0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo"0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo"0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo"0" >/proc/sys/net/ipv4/conf/all/arp_announce
echo"0" >/proc/sys/net/ipv4/conf/em1/arp_ignore
echo"0" >/proc/sys/net/ipv4/conf/em1/arp_announce
echo"RealServer Stoped"
;;
*)
echo"Usage: $0 {start|stop}"
exit1
esac
exit0
2.6 編輯腳本控制Keepalived的開啟和停止
大體意思是只要檢測到mysql服務停止keepalived服務也停止 ，因為keepalived是通過組播方式告訴本網段自己還活著當mysql服務停止後keepalived還依然運行 這時就需要停止keepalived讓另一個主機獲得虛擬IP，可以在後台運行這個腳本 也可以在keepalived配置文件加入這個腳本，master和slave節點都需要，只是數據庫用戶名和密碼有區別
[root@244 ~]#more /etc/keepalived/check_mysql.sh
#20140722
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=10.0.11.243
MYSQL_USER=root
MYSQL_PASSWORD=mysql
CHECK_TIME=3
#mysql isworking 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 "showstatus;" >/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
pkill keepalived
exit 1
fi
sleep 1
done
