你的位置:首页 > 数据库

[数据库]基于keepalived搭建MySQL的高可用集群

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-binserver-id=1log_slave_updates=1

     master2

[mysqld]log-bin=mysql-binserver-id=2log_slave_updates=1read_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: YesSlave_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 0fi 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_queryif [ $? -ne 0 ];then  service mysqld status &>/dev/null  if [ $? -ne 0 ];then    service_error  else    query_error  fifi

通过具体的查询语句来判断数据库服务的可用性,如果查询失败,则判断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.logmysql_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_statusif [ $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  actionfi

 

整个脚本的逻辑是让从的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.logecho -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.logrm -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.logsleep 2  ###给kill一个执行和缓冲时间 ###确保DB1已经追上了,下面的repl为复制所用的账户,-h后跟DB1的内网IPlog_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 1done ###然后解除DB1的read_only属性echo -e "`date "+%F %H:%M:%S"`  -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.logssh 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漂移到DB1echo -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 STATEMay 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