摘要：双主是一个比较简单的高可用架构，适用于中小集群，今天就说说怎么用做的高可用。缺点也比较明显，就是增加从节点的情况下，从节点不会主动切换同步对象，而且脚本需要自己实现，有一定风险。
双主 + keepalived 是一个比较简单的 MySQL 高可用架构，适用于中小 MySQL 集群，今天就说说怎么用 keepalived 做 MySQL 的高可用。
1 概述 1.1 keepalived 简介简单地说，keepalived 就是通过管理 VIP 来实现机器的高可用的，在使用 keepalived 的情况下，只有一台服务器能够提供服务(通过 VIP 来实现)，当 Master 主机宕机后，VIP 会自动飘移到另一台服务器
keepalived 采用 Master/Slave 模式, 在 Master 上设置配置文件的 VIP，当 Master 宕机后，VIP 自动漂移到另一台 keepalived 服务器上
keepalived 可以用来做各种软件的高可用集群，它会一直检测服务器的状态，如果有一台服务器宕机，或工作出现故障，keepalived 将检测到，并将有故障的服务器从系统中剔除，同时使用其他服务器代替该服务器的工作，当服务器工作正常后 keepalived 自动将服务器加入到服务器群中。
1.2 keepalived 配合双主keepalived 使用默认配置只能做到主机级别的高可用，但是我们的 MySQL 要做高可用至少要增加以下功能
能够检测 MySQL 服务状态
主节点 read_only=0，备节点 read_only=1
切换时，备节点要等待主节点同步完成
所以，keepalived 实现 MySQL 高可用需要使用自定义脚本来进行扩展
2 环境准备 2.1 数据库环境操作前已经准备好了一套主主架构数据库，搭建方法参考 MySQL集群搭建(2)-主主从模式
节点信息| IP | 系统 | 端口 | MySQL版本 | 节点 | 读写 | 说明 |
|---|---|---|---|---|---|---|
| 10.0.0.247 | Centos6.5 | 3306 | 5.7.9 | Master | 读写 | 主节点 |
| 10.0.0.248 | Centos6.5 | 3306 | 5.7.9 | Standby | 只读,可切换为读写 | 备主节点 |
| 简称 | VIP | 类型 |
|---|---|---|
| RW-VIP | 10.0.0.237 | 读写VIP |
[client] port = 3306 default-character-set=utf8mb4 socket = /data/mysql_db/test_db/mysql.sock [mysqld] datadir = /data/mysql_db/test_db basedir = /usr/local/mysql57 tmpdir = /tmp socket = /data/mysql_db/test_db/mysql.sock pid-file = /data/mysql_db/test_db/mysql.pid skip-external-locking = 1 skip-name-resolve = 1 port = 3306 server_id = 2473306 default-storage-engine = InnoDB character-set-server = utf8mb4 default_password_lifetime=0 auto_increment_offset = 1 auto_increment_increment = 2 #### log #### log_timestamps=system log_bin = /data/mysql_log/test_db/mysql-bin log_bin_index = /data/mysql_log/test_db/mysql-bin.index binlog_format = row relay_log_recovery=ON relay_log=/data/mysql_log/test_db/mysql-relay-bin relay_log_index=/data/mysql_log/test_db/mysql-relay-bin.index log_error = /data/mysql_log/test_db/mysql-error.log #### replication #### log_slave_updates = 1 replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.% #### semi sync replication settings ##### plugin_dir=/usr/local/mysql57/lib/plugin plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1Slave 参考配置
[client] port = 3306 default-character-set=utf8mb4 socket = /data/mysql_db/test_db/mysql.sock [mysqld] datadir = /data/mysql_db/test_db basedir = /usr/local/mysql57 tmpdir = /tmp socket = /data/mysql_db/test_db/mysql.sock pid-file = /data/mysql_db/test_db/mysql.pid skip-external-locking = 1 skip-name-resolve = 1 port = 3306 server_id = 2483306 default-storage-engine = InnoDB character-set-server = utf8mb4 default_password_lifetime=0 auto_increment_offset = 2 auto_increment_increment = 2 #### log #### log_timestamps=system log_bin = /data/mysql_log/test_db/mysql-bin log_bin_index = /data/mysql_log/test_db/mysql-bin.index binlog_format = row relay_log_recovery=ON relay_log=/data/mysql_log/test_db/mysql-relay-bin relay_log_index=/data/mysql_log/test_db/mysql-relay-bin.index log_error = /data/mysql_log/test_db/mysql-error.log #### replication #### log_slave_updates = 1 replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.% #### semi sync replication settings ##### plugin_dir=/usr/local/mysql57/lib/plugin plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 12.2 创建监控用的账号
- 由于是测试环境，账号密码设置比较随便 create user monitor@"localhost" identified by "monitor"; grant all on *.* to monitor@"localhost"; flush privileges;2.3 安装 keepalived
我们在 Master 和 Slave 上部署 keepalived
1). yum 安装如果有对应的 yum 源，直接安装就可以了
yum install -y keepalived2). 源码安装
下载安装包, 下载地址 keepalived, 使用 1.2.24 版本举例
# 安装依赖 yum install -y gcc popt-devel openssl openssl-devel libssl-dev libnl-devel popt-devel libnfnetlink-devel # 下载包 wget http://www.keepalived.org/software/keepalived-1.2.24.tar.gz # 解压安装 tar -xvz -f keepalived-1.2.24.tar.gz cd keepalived-1.2.24 ./configure --prefix=/usr/local/keepalived make && make install cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ mkdir /etc/keepalived/ cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/3 配置高可用 3.1 keepalived 配置
打开 /etc/keepalived/keepalived.conf 文件, 按照实际情况加上下面的配置
global_defs {
router_id MYSQL_MM # 标识
vrrp_skip_check_adv_addr
vrrp_strict # 严格执行 VRRP 协议规范
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_script check_mysql {
script "/bin/sh /etc/keepalived/keepalived_mysql_check.sh" # 检查脚本
interval 10 # 检查周期
}
vrrp_instance MYSQL_MM {
state BACKUP # 都设为 BACKUP，避免起来后抢占
interface eth0 # 网卡名称，根据实际情况填写
virtual_router_id 243 # 用来区分 VRRP 组播的标记，取值 0-255
priority 100
advert_int 1
nopreempt # 设为非抢占
authentication {
auth_type PASS
auth_pass 1111
}
# Master 节点可以注释掉下面语句，防止启动 keepalived 的时候执行脚本
notify_master "/bin/sh /etc/keepalived/keepalived_mysql_start.sh" # 变为 MASTER 时执行
virtual_ipaddress {
10.0.0.237
}
# Slave 节点可以注释下面检查脚本，Slave 没有必要一直检查
track_script {
check_mysql
}
}
3.2 配置检查脚本
打开 /etc/keepalived/keepalived_mysql_check.sh， 写入检测脚本
#!/bin/sh
# @Author: chengqm
# MySQL 检测脚本
MyPath=$(cd $(dirname $0); pwd)
cd $MyPath
ThisTime=`date "+%F %T"`
log_file="/var/log/keepalived_mysql.log"
# MySQL 连接方式，根据实际情况调整
export MYSQL_PWD="monitor"
MYSQL_USER="monitor"
MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock"
mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} "
# 美化输出
function techo() {
message=$1
message_level=$2
if [ -e $message_level ];then
message_level="info"
fi
echo "`date "+%F %T"` - [${message_level}] $message" >> $log_file
}
# 检查函数, 正常返回 0
function check {
ret=`$mysql_connect -N -e "select 1 as value"`
if [ $? -ne 0 ] || [ $ret -ne "1" ];then
return 1
else
return 0
fi
}
function read_only {
param=$1
$mysql_connect -e "set global read_only = ${param}"
techo "设置是否只读 read_only ${param}"
}
# 失效转移
function failover {
techo "开始执行失效转移"
# 1. 停止 keepalived
killall keepalived
# 2. 如果还能执行的话，设为 read_only
read_only 1
if [ $? -eq 0 ];then
# 3. 如果还能执行，kill 所有的连接
$mysql_connect -e "select concat("KILL ",id,";") from information_schema.processlist where user!="root" AND db is not null into outfile "/tmp/kill.txt.${ThisTime}";"
if [ $? -eq 0 ];then
$mysql_connect -e "source /tmp/kill.txt.${ThisTime};"
fi
fi
# 4. 其他操作，比如说自动关机
techo "失效转移执行成功，当前数据库关闭访问"
}
# 有问题检查 4 次
for ((i=1; i<=4; i ++))
do
check
if [ $? -eq 0 ];then
techo "MySQL is ok"
# 正常退出脚本
exit 0
else
techo "Connection failed $i time(s)"
sleep 1
fi
done
techo "无法连接当前数据库"
# 失效转移
failover
注意：脚本没有经过严格测试，需要根据实际情况调整
3.3 配置提升为 Master 时执行的脚本打开 /bin/sh /etc/keepalived/keepalived_mysql_start.sh", 写入脚本内容
#!/bin/sh
# @Author: chengqm
# keepalived 变为 Master 时执行
MyPath=$(cd $(dirname $0); pwd)
cd $MyPath
ThisTime=`date "+%F %T"`
log_file="/var/log/keepalived_mysql.log"
# MySQL 连接方式，根据实际情况调整
export MYSQL_PWD="monitor"
MYSQL_USER="monitor"
MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock"
mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} "
# 美化输出
function techo() {
message=$1
message_level=$2
if [ -e $message_level ];then
message_level="info"
fi
echo "`date "+%F %T"` - [${message_level}] $message" >> $log_file
}
# 检查函数, 正常返回 0
function check {
ret=`$mysql_connect -N -e "select 1 as value"`
if [ $? -ne 0 ] || [ $ret -ne "1" ];then
return 1
else
return 0
fi
}
# 获取 slave status 的信息
function slave_info() {
tmp_file=/tmp/slave_info.tmp
$mysql_connect -e "show slave statusG" > /tmp/slave_info.tmp
slave_sql=`grep "Slave_SQL_Running:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"`
seconds_behind_master=`grep "Seconds_Behind_Master:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"`
master_log_file=`grep "Master_Log_File:" $tmp_file | head -1 | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"`
master_log_pos=`grep "Read_Master_Log_Pos:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"`
relay_master_log_file=`grep "Relay_Master_Log_File:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"`
exec_master_log_pos=`grep "Exec_Master_Log_Pos:" $tmp_file | sed "s/s*//g" | tr "A-Z" "a-z" | awk -F":" "{print $2}"`
}
# 设置是否可读
function read_only {
param=$1
$mysql_connect -e "set global read_only = ${param}"
techo "设置是否只读 read_only ${param}"
}
# 处理数据同步
function sync_master_log() {
# 如果是数据一致性优先，等待同步完毕。如果是服务可用性优先，可以注销下面的代码
slave_info
if [ $slave_sql == "yes" ];then
techo "当前同步位置 Master ${master_log_file} ${master_log_pos}"
techo "等待同步到 Master ${master_log_file} ${master_log_pos}"
$mysql_connect -e "select master_pos_wait("$master_log_file", $master_log_pos);" > /dev/null
techo "同步完毕"
fi
}
techo "当前数据库提升为主库"
check
if [ $? -ne 0 ];then
techo "无法连接当前数据库"
exit 1
fi
# 等待同步
sync_master_log
# 设为可写
read_only 0
注意：脚本没有经过严格测试，需要根据实际情况调整
3.4 启动 keepalived由于配置了 BACKUP 模式，所以两个 keepalived 先起来的是主，先后在主备节点执行
/etc/init.d/keepalived start
检查 /var/log/message 日志，确认 keepalived 没有报错
检查 Master IP 状态, 确认设置了 VIP
