mysql启用keepalive_利用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/rc.d/init.d/keepalived /etc/rc.d/init.d/

[root@localhost keepalived-1.2.13]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/

[root@localhost keepalived-1.2.13]# mkdir /etc/keepalived

[root@localhost keepalived-1.2.13]# cp /usr/local/etc/keepalived/keepalived.conf  /etc/keepalived/

[root@localhost keepalived-1.2.13]# cp /usr/local/sbin/keepalived /usr/sbin/

[root@localhost keepalived-1.2.13]# chkconfig –add keepalived

[root@localhost keepalived-1.2.13]# chkconfig –level 345 keepalived on

[root@localhost keepalived-1.2.13]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

[root@localhost keepalived-1.2.13]# mkdir /app/daily_test/shell

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

[root@localhost 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等

[root@localhost 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

[root@localhost 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

[root@localhost 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

[root@localhost 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

[root@localhost 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日志的记录文件，如果都有，基本脚本就都执行了

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