利用keepalive+mysql replication 实现数据库的高可用

利用mysql 自带的replication 和 keepalive 提供的虚拟IP和故障检查来实现数据库的高可用

原理如下：

       mysql 版本为5.5，设置为单向主从，开启半同步复制

       mysql 主从机器上都安装keepalived，并配置keepalived文件

       mysql 的检测通过：mysql_check.sh 实现(检测进程，确认mysql宕机，关闭keepalived)

       当keepalived停止后，执行stop.sh脚本(更改前端密码，设置参数，保证数据不丢失，查看是否有写操作，1分钟后，强制退出) ==> 这个脚本一般是手动停止keepalived时，执行的脚本

       当keepalive 的状态值为master时，执行master.sh脚本(判断同步是否执行完毕，等待1分钟，不管是否执行完毕，都停止主从，更改前端的程序连接的账号密码，并记录切换以后的日志和pos点)

       当keepalive 的状态值为backup时，执行backup.sh脚本

原理图如下：

项目实验环境：

       mysql version：mysql 5.5.37

       keepalived：keepalived-1.2.13

       mysql master：10.207.130.50

       mysql slave：10.207.130.60

       vip：10.207.130.100

项目实施：

mysql的主从配置，半同步复制就不演示，各位自行配置

下面主要配置keepalived和一些脚本(以下步骤，两台keepalived都需执行，这里只演示1台机器)

1.安装配置keepalived

[root @localhost keepalived-1.2.13]# ./configure

[root @localhost keepalived-1.2.13]# make && make install

[root @localhost keepalived-1.2.13]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
[root @localhost keepalived-1.2.13]# mkdir /etc/keepalived
[[email protected] keepalived-1.2.13]# cp /usr/local/etc/keepalived/keepalived.conf  /etc/keepalived/
[[email protected] keepalived-1.2.13]# cp /usr/local/sbin/keepalived /usr/sbin/

[[email protected] keepalived-1.2.13]# chkconfig --add keepalived
[[email protected] keepalived-1.2.13]# chkconfig --level 345 keepalived on

[[email protected] keepalived-1.2.13]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

[[email protected] keepalived-1.2.13]# mkdir /app/daily_test/shell

2.修改 主：keepalived的配置文件，指定运行脚本，虚拟ip等

[[email protected] keepalived-1.2.13]# vim /etc/keepalived/keepalived.conf

global_defs {
router_id KeepAlive_Mysql
}vrrp_script check_run {
script "/app/daily_test/shell/mysql_check.sh"
interval 300
}

vrrp_sync_group VG1 {
group {
VI_1
}
}

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
}
track_script {
check_run
}

notify_master /app/daily_test/shell/master.sh
notify_master /app/daily_test/shell/backup.sh
notify_master /app/daily_test/shell/stop.sh
virtual_ipaddress {
10.207.130.59
}

}

3.修改 从：keepalived的配置文件，指定运行脚本，虚拟ip等

[[email protected] keepalived-1.2.13]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
router_id KeepAlive_Mysql
}vrrp_script check_run {
script "/app/daily_test/shell/mysql_check.sh"
interval 300
}

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}

notify_master /app/daily_test/shell/master.sh
notify_master /app/daily_test/shell/backup.sh
notify_master /app/daily_test/shell/stop.sh
virtual_ipaddress {
10.207.130.59
}

}

4.编写在keepalived里指定需要执行的脚本

(1). mysql检测脚本：mysql_check.sh

[[email protected] shell]# vim mysql_check.sh

count=1
while true
do
mysql -e "show status;">/dev/null 2>&1
i=$?
ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
j=$?
if [ $i = 0 ] && [ $j = 0 ];then
exit 0
else
if [ $i = 1 ] && [ $j = 0 ];then
exit 0
else
if [ $count -gt 5 ];then
break
fi
let count++
continue
fi
fi
done
/etc/init.d/keepalived stop

(2).当mysql状态为master时的执行脚本：master.sh

[[email protected] shell]# vim master.sh

#!/bin/bash
master_Log_File=$(mysql -e "show slave status\G" |grep -w master_Log_File |awk -F":" '{print $2}')
Relay_Log_File=$(mysql -e "show slave status\G" |grep -w Relay_master_Log_File |awk -F":" '{print $2}')
Read_master_Log_Pos=$(mysql -e "show slave status\G" |grep -w Read_master_Log_Pos |awk -F":" '{print $2}')
Exec_master_Log_Pos=$(mysql -e "show slave status\G" |grep -w Exec_master_Log_Pos |awk -F":" '{print $2}')
i=1
while true
do
if [ $master_Log_File = $Relay_Log_File ] && [ $Read_master_Log_Pos = $Exec_master_Log_Pos ];then
echo "ok"
break
else
sleep 1
if [ $i -gt 60 ];then
break
fi
continue
let i++
fi
done
mysql -e "stop slave;"
mysql -e "set global innodb_support_xa=1;"
mysql -e "set global sync_binlog=0;"
mysql -e "set global innodb_flush_log_at_trx_commit=2;"
mysql -e "set global event_scheduler=1;"
mysql -e "flush logs;grant all privileges on *.* to 'admin'@'%' identified by '123456';flush privileges;"
mysql -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt

(3).当mysql状态为backup时的执行脚本：backup.sh

[[email protected] shell]# vim backup.sh

#!/bin/bash
mysql -e "grant all privileges on *.* to 'admin'@'%' identified by '123456';flush privileges;"
mysql -e "set global event_scheduler=0;"
mysql -e "set global innodb_support_xa=1;"
mysql -e "set global sync_binlog=0;"
mysql -e "set global innodb_flush_log_at_trx_commit=2;"

(4)当keepalived停止后，执行的脚本： stop.sh

[[email protected] shell]# vim stop.sh

#!/bin/bash
mysql -e "grant all privileges on *.* to 'admin'@'%' identified by '123456';flush privileges;"
mysql -e "set global innodb_support_xa=1;"
mysql -e "set global sync_binlog=1;"
mysql -e "set global innodb_flush_log_at_trx_commit=1;"
i=1
while true
do
M_File1=$(mysql -e "show master status\G" | grep -w File | awk -F":" '{print $2}')
M_Position1=$(mysql -e "show master status\G" | grep -w Position | awk -F":" '{print $2}')
sleep 1
M_File2=$(mysql -e "show master status\G" | grep -w File | awk -F":" '{print $2}')
M_Position2=$(mysql -e "show master status\G" | grep -w Position | awk -F":" '{print $2}')
if [ $M_File1 = $M_File2 ] && [ $M_Position1 = $M_Position2 ];then
echo "ok"
break
else
sleep 1
if [ $i -gt 60 ];then
break
fi
continue
let i++
fi
done

5.以上脚本，在主从机器上都需要有

6.测试的话，可以关掉mysql或者关掉keepalived，看利用虚拟IP是否可以访问，这里就不详细演示，有问题，可联系

建议关闭mysql，然后看keepalived是否停止(这个需要几秒钟)；然后，在新主的tmp目录下，是否有关master日志的记录文件，如果都有，基本脚本就都执行了

现在可以考虑压测，数据丢失情况的测试