我们通常说的双机热备是指两台机器都在运行，但并不是两台机器都同时在提供服务。当提供服务的一台出现故障的时候，另外一台会马上自动接管并且提供服务，而且切换的时间非常短。MySQL双主复制，即互为Master-Slave(只有一个Master提供写操作)，可以实现数据库服务器的热备，但是一个Master宕机后不能实现动态切换。使用Keepalived，可以通过虚拟IP，实现双主对外的统一接口以及自动检查、失败切换机制，从而实现MySQL数据库的高可用方案。
|
1
2
3
4
5
6
|
Keepalived 保持存活，也就是所谓的高可用或热备，用来防止单点故障（单点故障是指一旦某一点出现故障就会导致
整个系统架构的不可用现象的发生，那说到keepalived必须提及一个协议VRRP协议，这个协议算是keepalived实现的基础。
1）Keepalived的工作原理是VRRP（Virtual Router Redundancy Protocol）虚拟路由冗余协议。在VRRP中有两组重要的概念：VRRP路由器和虚拟路由器，主控路由器和备份路由器。
2）VRRP路由器是指运行VRRP的路由器，是物理实体，虚拟路由器是指VRRP协议创建的，是逻辑概念。一组VRRP路由器协同工作，共同构成一台虚拟路由器。
Vrrp中存在着一种选举机制，用以选出提供服务的路由即主控路由，其他的则成了备份路由。当主控路由失效后，备份路由中会重新选举出一个主控路由，来继
续工作，来保障不间断服务。
|
过多内容在这里就不做详细介绍了，下面记录下Mysql+Keepalived双主热备的高可用方案的操作记录
1）先实施Master->Slave的主主同步。主主是数据双向同步，主从是数据单向同步。一般情况下，主库宕机后，需要手动将连接切换到从库上。（但是用keepalived就可以自动切换） 2）再结合Keepalived的使用，通过VIP实现Mysql双主对外连接的统一接口。即客户端通过Vip连接数据库；当其中一台宕机后，VIP会漂移到另一台上，这个过程对于客户端的数据连接来说几乎无感觉，从而实现高可用。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
环境描述：
mysql的安装可以参考：https://www.cnblogs.com/Camiluo/p/9923030.html
Centos7.6版本
Master1：192.168.49.13 安装mysql和keepalived
Master2: 192.168.49.128 安装mysql和keepalived
VIP：166.111.69.10
要实现主主同步，可以先实现主从同步，即master1->master2的主从同步，然后master2->master1的主从同步.
这样，双方就完成了主主同步。
注意下面几点：
1）要保证同步服务期间之间的网络联通。即能相互ping通，能使用对方授权信息连接到对方数据库（防火墙开放3306端口）。
2）关闭selinux.
3）同步前，双方数据库中需要同步的数据要保持一致。这样，同步环境实现后，再次更新的数据就会如期同步了。
|
一、MySQL 主主同步环境部署
1、修改配置文件
Master 1 的有关复制的配置如下
[mysqld] log-bin=mysql-bin server-id=1 log_slave_updates=1
Master 2 的有关复制的配置如下
[mysqld] log-bin=mysql-bin server-id=2 log_slave_updates=1 read_only=1
2.创建复制用户
Master 1 中创建：
CREATE USER 'repl'@'192.168.49.128' IDENTIFIED BY '123'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.49.128';
Master 2 中创建：
CREATE USER 'repl'@'192.168.49.13' IDENTIFIED BY '123'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.49.13';
3. 执行CHANGE MASTER TO语句
因是从头搭建MySQL主从复制集群，所以不需要获取全局读锁来得到二进制日志文件的位置，直接根据show master status的输出来确认。
Master1上执行：
(root@localhost) [(none)]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 2192
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Master2上执行：
(root@localhost) [(none)]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000008
Position: 1574
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Master 1 上 Change：
CHANGE MASTER TO MASTER_HOST='192.168.49.128', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=1574;
Master 2 上 Change：
CHANGE MASTER TO MASTER_HOST='192.168.49.13', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=2192;
4.分别在两个节点上执行start slave 语句，并通过show slave status \G 查看复制是否搭建成功。
成功标准：
Slave_IO_Running: Yes Slave_SQL_Running: Yes
二、配置MySQL + Keepalived 故障转移的高可用环境
1）安装keepalived并将其配置成系统服务。
Master1和Master2两台机器上同样进行如下操作：
[root@master1 ~]
# yum install -y openssl-devel
[root@master1 ~]
# cd /usr/local/src/
[root@master1 src]
# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
[root@master1 src]
# tar -zvxf keepalived-1.3.5.tar.gz
[root@master1 src]
# cd keepalived-1.3.5
[root@master1 keepalived-1.3.5]
# ./configure --prefix=/usr/local/keepalived
[root@master1 keepalived-1.3.5]
# make && make install
[root@master1 keepalived-1.3.5]
# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
[root@master1 keepalived-1.3.5]
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@master1 keepalived-1.3.5]
# mkdir /etc/keepalived/
[root@master1 keepalived-1.3.5]
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@master1 keepalived-1.3.5]
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@master1 keepalived-1.3.5]
# echo "/etc/init.d/keepalived start" >> /etc/rc.local
2）master1机器上的keepalived.conf配置。（下面配置中没有使用lvs的负载均衡功能，所以不需要配置虚拟服务器virtual server）
[root@master1 ~]
# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@master1 ~]
# vim /etc/keepalived/keepalived.conf ＃清空默认内容，直接采用下面配置：
vrrp_script chk_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 30 #设置检查间隔时长，可根据自己的需求自行设定
}
vrrp_instance VI_1 {
state BACKUP #通过下面的priority来区分MASTER和BACKUP，也只有如此，底下的nopreempt才有效
interface eno16777736
virtual_router_id 51
priority 100
advert_int 1
nopreempt #防止切换到从库后，主keepalived恢复后自动切换回主库
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_mysql
}
virtual_ipaddress {
166.111.69.100/24
}
}
其中 /etc/keepalived/check_mysql.sh内容如下：
#!/bin/bash
###判断如果上次检查的脚本还没执行完，则退出此次执行
if [ `ps -ef|grep -w "$0"|grep -v "grep"|wc -l` -gt 2 ];then
exit 0
fi
mysql_con='mysql -uroot -p123456'
error_log="/etc/keepalived/logs/check_mysql.err"
###定义一个简单判断mysql是否可用的函数
function excute_query {
${mysql_con} -e "select 1;" 2>> ${error_log}
}
###定义无法执行查询，且mysql服务异常时的处理函数
function service_error {
echo -e "`date "+%F %H:%M:%S"` -----mysql service error，now stop keepalived-----" >> ${error_log}
service keepalived stop &>> ${error_log}
echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理！" slowtech@126.com 2>> ${error_log}
echo -e "\n---------------------------------------------------------\n" >> ${error_log}
}
###定义无法执行查询,但mysql服务正常的处理函数
function query_error {
echo -e "`date "+%F %H:%M:%S"` -----query error, but mysql service ok, retry after 30s-----" >> ${error_log}
sleep 30
excute_query
if [ $? -ne 0 ];then
echo -e "`date "+%F %H:%M:%S"` -----still can't execute query-----" >> ${error_log}
###对DB1设置read_only属性
echo -e "`date "+%F %H:%M:%S"` -----set read_only = 1 on DB1-----" >> ${error_log}
mysql_con -e "set global read_only = 1;" 2>> ${error_log}
###kill掉当前客户端连接
echo -e "`date "+%F %H:%M:%S"` -----kill current client thread-----" >> ${error_log}
rm -f /tmp/kill.sql &>/dev/null
###这里其实是一个批量kill线程的小技巧
mysql_con -e 'select concat("kill ",id,";") from information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
mysql_con -e "source /tmp/kill.sql"
sleep 2 ###给kill一个执行和缓冲时间
###关闭本机keepalived
echo -e "`date "+%F %H:%M:%S"` -----stop keepalived-----" >> ${error_log}
service keepalived stop &>> ${error_log}
echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理！" slowtech@126.com 2>> ${error_log}
echo -e "\n---------------------------------------------------------\n" >> ${error_log}
else
echo -e "`date "+%F %H:%M:%S"` -----query ok after 30s-----" >> ${error_log}
echo -e "\n---------------------------------------------------------\n" >> ${error_log}
fi
}
###检查开始: 执行查询
excute_query
if [ $? -ne 0 ];then
service mysqld status &>/dev/null
if [ $? -ne 0 ];then
service_error
else
query_error
fi
fi
通过具体的查询语句来判断数据库服务的可用性，如果查询失败，则判断mysqld进程本身的状态，如果不正常，则直接停止当前节点的keepalived，将VIP转移到另外一个节点，如果正常，则等待30s，再次执行查询语句，还是失败，则将当前的master节点设置为read_only，并kill掉当前的客户端连接，然后停止当前的keepalived。
Master 2
[root@master2 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
vrrp_instance VI_1 {
state BACKUP
interface eno16777736
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
notify_master /etc/keepalived/notify_master_mysql.sh #此条指令告诉keepalived发现自己转为MASTER后执行的脚本
virtual_ipaddress {
166.111.49.100/24
}
}
其中，/etc/keepalived/notify_master_mysql.sh的内容如下：
#!/bin/bash
###当keepalived监测到本机转为MASTER状态时，执行该脚本
change_log=/etc/keepalived/logs/state_change.log
mysql_con='mysql -uroot -p888888'
echo -e "`date "+%F %H:%M:%S"` -----keepalived change to MASTER-----" >> $change_log
slave_info() {
###统一定义一个函数取得slave的position、running、和log_file等信息
###根据函数后面所跟参数来决定取得哪些数据
if [ $1 = slave_status ];then
slave_stat=`${mysql_con} -e "show slave status\G;"|egrep -w "Slave_IO_Running|Slave_SQL_Running"`
Slave_IO_Running=`echo $slave_stat|awk '{print $2}'`
Slave_SQL_Running=`echo $slave_stat|awk '{print $4}'`
elif [ $1 = log_file -a $2 = pos ];then
log_file_pos=`${mysql_con} -e "show slave status\G;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
fi
}
action() {
###经判断'应该&可以'切换时执行的动作
echo -e "`date "+%F %H:%M:%S"` -----set read_only = 0 on DB2-----" >> $change_log
###解除read_only属性
${mysql_con} -e "set global read_only = 0;" 2>> $change_log
echo "DB2 keepalived转为MASTER状态，线上数据库切换至DB2"|mail -s "DB2 keepalived change to MASTER"\
slowtech@126.com 2>> $change_log
echo -e "---------------------------------------------------------\n" >> $change_log
}
slave_info slave_status
if [ $Slave_SQL_Running = Yes ];then
i=0 #一个计数器
slave_info log_file pos
###判断从master接收到的binlog是否全部在本地执行(这样仍无法完全确定从库已追上主库，因为无法完全保证io_thread没有延时(由网络传输问题导致的从库落后的概率很小)
until [ $Master_Log_File = $Relay_Master_Log_File -a $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
do
if [ $i -lt 10 ];then #将等待exec_pos追上read_pos的时间限制为10s
echo -e "`date "+%F %H:%M:%S"` -----Relay_Master_Log_File=$Relay_Master_Log_File,Exec_Master_Log_Pos=$Exec_Master_Log_Pos is behind Master_Log_File=$Master_Log_File,Read_Master_Log_Pos=$Read_Master_Log_Pos, wait......" >> $change_log #输出消息到日志，等待exec_pos=read_pos
i=$(($i+1))
sleep 1
slave_info log_file pos
else
echo -e "The waits time is more than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
action
exit 0
fi
done
action
else
slave_info log_file pos
echo -e "DB2's slave status is wrong,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
action
fi
整个脚本的逻辑是让从的Exec_Master_Log_Pos尽可能的追上Read_Master_Log_Pos，它给了10s的限制，如果还是没有追上，则直接将master2设置为主（通过解除read_only属性），其实这里面还是有待商榷的，譬如10s的限制是否合理，还是一定需要Exec_Master_Log_Pos=Read_Master_Log_Pos才切换。
DB2上手动切换回DB1的脚本change_to_backup.sh：
#!/bin/bash
###手动执行将主库切换回DB1的操作
mysql_con='mysql -uroot -p123456'
echo -e "`date "+%F %H:%M:%S"` -----change to BACKUP manually-----" >> /etc/keepalived/logs/state_change.log
echo -e "`date "+%F %H:%M:%S"` -----set read_only = 1 on DB2-----" >> /etc/keepalived/logs/state_change.log
$mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/state_change.log
###kill掉当前客户端连接
echo -e "`date "+%F %H:%M:%S"` -----kill current client thread-----" >> /etc/keepalived/logs/state_change.log
rm -f /tmp/kill.sql &>/dev/null
###这里其实是一个批量kill线程的小技巧
$mysql_con -e 'select concat("kill ",id,";") from information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
$mysql_con -e "source /tmp/kill.sql" 2>> /etc/keepalived/logs/state_change.log
sleep 2 ###给kill一个执行和缓冲时间
###确保DB1已经追上了,下面的repl为复制所用的账户，-h后跟DB1的内网IP
log_file_pos=`mysql -urepl -pmysql -h192.168.244.145 -e "show slave status\G;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
until [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos -a $Master_Log_File = $Relay_Master_Log_File ]
do
echo -e "`date "+%F %H:%M:%S"` -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......" >> /etc/keepalived/logs/state_change.log
sleep 1
done
###然后解除DB1的read_only属性
echo -e "`date "+%F %H:%M:%S"` -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.log
ssh 192.168.244.145 'mysql -uroot -p123456 -e "set global read_only = 0;" && /etc/init.d/keepalived start' 2>> /etc/keepalived/logs/state_change.log
###重启DB2的keepalived使VIP漂移到DB1
echo -e "`date "+%F %H:%M:%S"` -----make VIP move to DB1-----" >> /etc/keepalived/logs/state_change.log
/sbin/service keepalived restart &>> /etc/keepalived/logs/state_change.log
echo "DB2 keepalived转为BACKUP状态，线上数据库切换至DB1"|mail -s "DB2 keepalived change to BACKUP" slowtech@126.com 2>> /etc/keepalived/logs/state_change.log
echo -e "--------------------------------------------------\n" >> /etc/keepalived/logs/state_change.log
Error：在测试过程中，可能遇到keepalived 启动失败的情况
上面这种情况是找不到PID 文件而报错,可手动指定PID文件的位置
查看keepalived.service
# vi /lib/systemd/system/keepalived.service [Unit] Description=LVS and VRRP High Availability Monitor After=syslog.target network-online.target [Service] Type=forking PIDFile=/usr/local/keepalived/var/run/keepalived.pid KillMode=process EnvironmentFile=-/usr/local/keepalived/etc/sysconfig/keepalived ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS ExecReload=/bin/kill -HUP $MAINPID [Install] WantedBy=multi-user.target
发现 PIDFile=/usr/local/keepalived/var/run/keepalived.pid 这个文件的路径并不存在
[root@centos02_stb keepalived]# find / -name keepalived.pid /run/keepalived.pid
修改为 /run/keepalived.pid
运行命令
# systemctl daemon-reload #重新载入 systemd，扫描新的或有变动的单元
再运行就Ok了
service keepalived start Starting keepalived (via systemctl): [ OK ]