共计 10845 个字符,预计需要花费 28 分钟才能阅读完成。
MySQL 的高可用方案一般有如下几种:
keepalived+ 双主,MHA,MMM,Heartbeat+DRBD,PXC,Galera Cluster
比较常用的是 keepalived+ 双主,MHA 和 PXC。
对于小公司,一般推荐使用 keepalived+ 双主,简单。
下面来部署一下
配置环境:
角色 主机 IP 主机名 操作系统版本 软件版本
VIP 192.168.244.10
master1 192.168.244.145 master1 CentOS7.1 MySQL 5.6.26,Keepalived v1.2.13
master2 192.168.244.146 master2 CentOS7.1 MySQL 5.6.26,Keepalived v1.2.13
一、配置 MySQL 双主复制环境
1. 修改配置文件
master1 中有关复制的配置如下:
[mysqld]
log-bin=mysql-bin
server-id=1
log_slave_updates=1
master2
[mysqld]
log-bin=mysql-bin
server-id=2
log_slave_updates=1
read_only=1
2. 创建复制用户
master1 中创建:
CREATE USER 'repl'@'192.168.244.146' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.146';
master2 中创建:
CREATE USER 'repl'@'192.168.244.145' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.145';
3. 执行 CHANGE MASTER TO 语句
因是从头搭建 MySQL 主从复制集群,所以不需要获取全局读锁来得到二进制日志文件的位置,直接根据 show master status 的输出来确认。
master1 上执行:
CHANGE MASTER TO
MASTER_HOST='192.168.244.146',
MASTER_USER='repl',
MASTER_PASSWORD='mysql',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=64729;
master2 上执行:
CHANGE MASTER TO
MASTER_HOST='192.168.244.145',
MASTER_USER='repl',
MASTER_PASSWORD='mysql',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=68479;
4. 分别在两个节点上执行 start slave 语句并通过 show slave status\G 查看复制是否搭建成功。
成功标准:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
二、配置 Keepalived
1. 安装 Keepalived
# yum install -y keepalived
当然,也可直接编译官方的源码包。
2. 修改 Keepalived 的配置文件
master1
[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 {192.168.244.10/24
}
}
关于 keepalived 的参数的详细介绍,可参考:LVS+Keepalived 搭建 MyCAT 高可用负载均衡集群
其中,/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。
master2
[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 {192.168.244.10/24
}
}
其中,/etc/keepalived/notify_master_mysql.sh 的内容如下:
#!/bin/bash
### 当 keepalived 监测到本机转为 MASTER 状态时,执行该脚本
change_log=/etc/keepalived/logs/state_change.log
mysql_con='mysql -uroot -p123456'
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 才切换。
当原主恢复正常后,如何将 VIP 从 master2 切回到 master1 中呢?
#!/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
总结:
1. /etc/keepalived/check_mysql.sh 和 /etc/keepalived/notify_master_mysql.sh 必须加可执行权限。
如果前者没有加可执行权限,则 master1 上将不会绑定 VIP,日志直接提示如下信息:
May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Entering BACKUP STATE
May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
May 25 14:37:50 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Now in FAULT state
2. 在 Keepalived 中有两种模式,分别是 master->backup 模式和 backup->backup 模式,这两种模式有什么区别呢?
在 master->backup 模式下,一旦主库宕掉,虚拟 IP 会自动漂移到从库,当主库修复后,keepalived 启动后,还会把虚拟 IP 抢过来,即使你设置 nopreempt(不抢占)的方式抢占 IP 的动作也会发生。在 backup->backup 模式下,当主库宕掉后虚拟 IP 会自动漂移到从库上,当原主恢复之后重启 keepalived 服务,并不会抢占新主的虚拟 IP,即使是优先级高于从库的优先级别,也不会抢占 IP。为了减少 IP 的漂移次数,生产中我们通常是把修复好的主库当做新主库的备库。
3. 本文是在 MySQL 主库高可用 — 双主单活故障自动切换方案 基础上,结合自己对于 MySQL 的理解整理的。原文的脚本直接执行有点问题,思路有有点瑕疵,于是结合自己的实际环境,重新修改了一把。
4. 在测试的过程中,有以下几点需要注意:
1> master1 检测脚本的逻辑是如果 MySQL 的服务不可用,则通过 service keepalived stop 命令来关闭 keepalived,但在实际测试的过程中,却出现了即便执行了 service keepalived stop 命令,keepalived 进程依然没有停止,导致 MySQL 的服务虽然不可用了,但 VIP 并不没有漂移到 master2 上。
优化方案:在执行 service keepalived stop 后,等待 5s,再次检测 keepalived 的状态,如果 keepalived 没有关闭,则直接 kill 掉。
2> keepalived 的日志默认是输出到 /var/log/messages 中,这样不便于查看。如何自定义 keepalived 的日志输出文件呢?
如果是用 service 启动的,修改 /etc/sysconfig/keepalived 文件
KEEPALIVED_OPTIONS="-D -d -S 0"
如果不是,则启动的时候指定以上参数,如:
/usr/local/keepalived/sbin/keepalived -d -D -S 0
修改 /etc/syslog.conf
# keepalived -S 0
local0.* /var/log/keepalived.log
重启 syslog
RHEL 5&6��service syslog restart
RHEL 7:service rsyslog restart
LVS/DR + Keepalived 搭建负载均衡集群 http://www.linuxidc.com/Linux/2015-06/118647.htm
LVS+Keepalived 实现四层负载及高可用 http://www.linuxidc.com/Linux/2015-02/112695.htm
LVS+Keepalived 高可用负载均衡集群架构实验 http://www.linuxidc.com/Linux/2015-01/112560.htm
Heartbeat+LVS 构建高可用负载均衡集群 http://www.linuxidc.com/Linux/2014-09/106964.htm
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-06/132703.htm