基于Keepalived搭建MySQL的高可用集群

MySQL的高可用方案一般有如下几种：keepalived+双主，MHA，MMM，Heartbeat+DRBD，PXC，Galera Cluster比较常用的是keepalived+双主，MHA和PXC。对于小公司，一般推荐使用keepalived+双主，简单。下面来部署一下配置环境：角色主机IP 主机名操作系统版本软件版本VIP 192.168.244.10master1 192.168.244.145 master1 CentOS7.1 MySQL 5.6.26，Keepalived v1.2.13master2 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 TOMASTER_HOST="192.168.244.146",MASTER_USER="repl",MASTER_PASSWORD="mysql",MASTER_LOG_FILE="mysql-bin.000004",MASTER_LOG_POS=64729; master2上执行：CHANGE MASTER TOMASTER_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 statusG查看复制是否搭建成功。成功标准：Slave_IO_Running: YesSlave_SQL_Running: Yes二、配置Keepalived 1. 安装Keepalived # yum install -y keepalived 当然，也可直接编译官方的源码包。 2. 修改Keepalived的配置文件 master1 [root@master1 ~]# vim /etc/keepalived/keepalived.confvrrp_script chk_mysql {script "/etc/keepalived/check_mysql.sh"interval 30 #设置检查间隔时长，可根据自己的需求自行设定}vrrp_instance VI_1 {state BACKUP#通过下面的priority来区分MASTER和BACKUP，也只有如此，底下的nopreempt才有效interface eno16777736virtual_router_id 51priority 100advert_int 1nopreempt #防止切换到从库后，主keepalived恢复后自动切换回主库authentication {auth_type PASSauth_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 ];thenexit 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 " --------------------------------------------------------- " >> ${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 30excute_queryif [ $？ -ne 0 ];thenecho -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,";"） frominformation_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 " --------------------------------------------------------- " >> ${error_log}elseecho -e "`date "+%F%H:%M:%S"`-----query ok after 30s-----" >> ${error_log}echo -e " --------------------------------------------------------- " >> ${error_log}fi} ###检查开始: 执行查询excute_queryif [ $？ -ne 0 ];thenservice mysqld status &>/dev/nullif [ $？ -ne 0 ];thenservice_errorelsequery_errorfifi通过具体的查询语句来判断数据库服务的可用性，如果查询失败，则判断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 BACKUPinterface eno16777736virtual_router_id 51priority 90advert_int 1authentication {auth_type PASSauth_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 ];thenslave_stat=`${mysql_con} -e "show slave statusG;"|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 ];thenlog_file_pos=`${mysql_con} -e "show slave statusG;"|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 "--------------------------------------------------------- " >> $change_log} slave_info slave_statusif [ $Slave_SQL_Running = Yes ];theni=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 ] doif [ $i -lt 10 ];then#将等待exec_pos追上read_pos的时间限制为10secho -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_posi=$（（$i+1））sleep 1slave_info log_file poselseecho -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_logactionexit 0fidoneactionelseslave_info log_file posecho -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_logactionfi 整个脚本的逻辑是让从的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,";"） frominformation_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 statusG;"|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 ]doecho -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.logsleep 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 "-------------------------------------------------- " >> /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 state2. 在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.htmLVS+Keepalived实现四层负载及高可用 http://www.linuxidc.com/Linux/2015-02/112695.htmLVS+Keepalived高可用负载均衡集群架构实验 http://www.linuxidc.com/Linux/2015-01/112560.htmHeartbeat+LVS构建高可用负载均衡集群 http://www.linuxidc.com/Linux/2014-09/106964.htm本文永久更新链接地址