利用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日志的记录文件,如果都有,基本脚本就都执行了
现在可以考虑压测,数据丢失情况的测试