Keepalived构建双主MySQL

文章转自：http://www.myoradba.com/?p=284

本文介绍，关于keepalive的原理请参考《Keepalived 原理与实战精讲及配置范例》
安装环境介绍
mysql.ymz.com 10.211.55.100
db01.ymz.com 10.211.55.5
db02.ymz.com 10.211.55.6
db01 ~ db02 主主复制

Keepalived 安装

1 2 3 4 5 6 7 8 9

# tar -xzvf /tmp/keepalived-1.2.13.tar.gz # ./configure --prefix=/usr/local/keepalived --disable-fwmark # make && make install   # cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/ # cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ # cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ # mkdir -p /etc/keepalived # cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

配置 keepalived.conf#

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

#vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs {    notification_email {        cloudhorizon @ 163.com    }    notification_email_from db01 @ ymz .com    smtp _server 127.0.0.1    smtp_connect _timeout 30    router_id mysql - ha } vrrp_script check_run {      script "/etc/keepalived/check_mysql.sh"      interval 5 } vrrp_sync_group VG1 {      group {            VI _1      } } vrrp_instance VI_1 {      state BACKUP      interface eth0      virtual_router_id 51      # db01为100，db02为99      priority 100      advert_int 1      authentication {          auth_type PASS          auth _pass 1111      }      track_script {        check _run      }      virtual_ipaddress {          10.211.55.100      } } virtual _server 10.211.55.100 3306 {      #每个2秒检查一次real_server状态      delay _loop 2         #LVS算法      lb_algo wrr      #LVS模式      lb_kind DR      #会话保持时间      persistence _timeout 60      protocol TCP      #此处M-S的IP应该不同      real _server 10.211.55.5 3306 {          weight 3          #检测到服务down后执行的脚本          notify_down / etc / keepalived / mysql_down .sh          TCP_CHECK {              #连接超时时间              connect _timeout 10              #重连次数              nb_get _retry 3              #重连间隔时间              delay_before _retry 3              #健康检查端口              connect _port 3306      }   }

配置mysql状态检测脚本 check_mysql.sh，主备相同

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

# vi /etc/keepalived/check_mysql.sh ! / bin / bash MYSQL = / usr / local / mysql / bin / mysql MYSQL_HOST = 10.211.55.5 MYSQL_USER = root MYSQL_PASSWORD = root CHECK_TIME = 3 #mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0 MYSQL_OK = 1   function check_mysql_helth ( ) {    $MYSQL - h $MYSQL_HOST - u $MYSQL_USER - p $ { MYSQL_PASSWORD } - e "show status;" > / dev / null 2 > & 1    if [ $ ? = 0 ] ; then    MYSQL_OK = 1    else    MYSQL_OK = 0    fi    return $MYSQL_OK }   while [ $CHECK_TIME - ne 0 ] do    let "CHECK_TIME -= 1"    check_mysql_helth    if [ $MYSQL_OK = 1 ] ; then      CHECK_TIME = 0      exit 0    fi    if [ $MYSQL_OK - eq 0 ] &&    [ $CHECK_TIME - eq 0 ]    then      / etc / init .d / keepalived stop      exit 1    fi    sleep 1 done

1	# chmod 755 /etc/keepalived/check_mysql.sh

## 检测数据库关闭时执行的脚本mysql_down.sh

1 2 3 4

# vi /etc/keepalived/mysql_down.sh ! / bin / bash service   keepalived   stop # chmod 755 /etc/keepalived/mysql_down.sh

此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服务状态，当发现real_server服务故障时，便触发此脚本;我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

## 启动db01的 keepalived

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

#/usr/local/keepalived/sbin/keepalived -D # ps -aux | grep keepalived   # tail -f /var/logs/messages Jul 15 00 : 19 : 55 db01 Keepalived [ 25463 ] : Stopping Keepalived v1 . 2.13 ( 07 / 09 , 2014 ) Jul 15 00 : 19 : 55 db01 Keepalived_vrrp [ 25465 ] : VRRP_Instance ( VI_1 ) sending 0 priority Jul 15 00 : 19 : 55 db01 Keepalived_vrrp [ 25465 ] : VRRP_Instance ( VI_1 ) removing protocol VIPs . Jul 15 00 : 19 : 55 db01 Keepalived_healthcheckers [ 25464 ] : Netlink reflector reports IP 10.211.55.100 removed Jul 15 00 : 19 : 55 db01 Keepalived_healthcheckers [ 25464 ] : Removing service [ 10.211.55.5 ] : 3306 from VS [ 10.211.55.100 ] : 3306 Jul 15 00 : 19 : 55 db01 avahi - daemon [ 2898 ] : Withdrawing address record for 10.211.55.100 on eth0 . Jul 15 00 : 19 : 57 db01 Keepalived [ 25482 ] : Starting Keepalived v1 . 2.13 ( 07 / 09 , 2014 ) Jul 15 00 : 19 : 57 db01 Keepalived [ 25483 ] : Starting Healthcheck child process , pid = 25484 Jul 15 00 : 19 : 57 db01 Keepalived [ 25483 ] : Starting VRRP child process , pid = 25485 Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Netlink reflector reports IP 10.211.55.5 added Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fed9 : 4db2 added Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Netlink reflector reports IP fe80 :: 21c : 42ff : fed9 : 4db2 added Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Registering Kernel netlink reflector Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Registering Kernel netlink command channel Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Opening file '/etc/keepalived/keepalived.conf' . Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Configuration is using : 11915 Bytes Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Using LinkWatch kernel netlink reflector . . . Jul 15 00 : 19 : 57 db01 Keepalived_healthcheckers [ 25484 ] : Activating healthchecker for service [ 10.211.55.5 ] : 3306 Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Netlink reflector reports IP 10.211.55.5 added Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fed9 : 4db2 added Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Netlink reflector reports IP fe80 :: 21c : 42ff : fed9 : 4db2 added Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Registering Kernel netlink reflector Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Registering Kernel netlink command channel Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Registering gratuitous ARP shared channel Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Opening file '/etc/keepalived/keepalived.conf' . Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Configuration is using : 67848 Bytes Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : Using LinkWatch kernel netlink reflector . . . Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) Entering BACKUP STATE Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : VRRP sockpool : [ ifindex ( 2 ) , proto ( 112 ) , unicast ( 0 ) , fd ( 10 , 11 ) ] Jul 15 00 : 19 : 57 db01 Keepalived_vrrp [ 25485 ] : VRRP_Script ( check_run ) succeeded Jul 15 00 : 20 : 01 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) Transition to MASTER STATE Jul 15 00 : 20 : 01 db01 Keepalived_vrrp [ 25485 ] : VRRP_Group ( VG1 ) Syncing instances to MASTER state Jul 15 00 : 20 : 02 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) Entering MASTER STATE Jul 15 00 : 20 : 02 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) setting protocol VIPs . Jul 15 00 : 20 : 02 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) Sending gratuitous ARPs on eth0 for 10.211.55.100 Jul 15 00 : 20 : 02 db01 Keepalived_vrrp [ 25485 ] : Netlink reflector reports IP 10.211.55.100 added Jul 15 00 : 20 : 02 db01 Keepalived_healthcheckers [ 25484 ] : Netlink reflector reports IP 10.211.55.100 added Jul 15 00 : 20 : 02 db01 avahi - daemon [ 2898 ] : Registering new address record for 10.211.55.100 on eth0 . Jul 15 00 : 20 : 07 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) Sending gratuitous ARPs on eth0 for 10.211.55.100

使用VIP 连接mysql

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

[ root @ db02 keepalived ] # mysql -uroot -proot -h 10.211.55.100 Welcome to the MySQL monitor .    Commands end with ; or \ g . Your MySQL connection id is 420 Server version : 5.6.18 - enterprise - commercial - advanced - log MySQL Enterprise Server - Advanced Edition ( Commercial )   Type 'help;' or '\h' for help . Type '\c' to clear the buffer .   mysql > \ s -- -- -- -- -- -- -- mysql   Ver 14.12 Distrib 5.0.77 , for redhat - linux - gnu ( x86_64 ) using readline 5.1   Connection id :          420 Current database :      Current user :          root @ 10.211.55.6 SSL :              Not in use Current pager :          stdout Using outfile :          '' Using delimiter :      ; Server version :          5.6.18 - enterprise - commercial - advanced - log MySQL Enterprise Server - Advanced Edition ( Commercial ) Protocol version :      10 Connection :          10.211.55.100 via TCP / IP Server characterset :      latin1 Db     characterset :      latin1 Client characterset :      latin1 Conn .    characterset :      latin1 TCP port :          3306 Uptime :              4 days 6 hours 59 min 5 sec   Threads : 6    Questions : 414    Slow queries : 5    Opens : 76    Flush tables : 1    Open tables : 69    Queries per second avg : 0.001

## 启动db02的 keepalived

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

# /usr/local/keepalived/sbin/keepalived -D # ps -aux | grep keepalived   # tail -f /var/logs/messages Jul 15 00 : 31 : 13 db02 Keepalived [ 27069 ] : Starting Keepalived v1 . 2.13 ( 07 / 14 , 2014 ) Jul 15 00 : 31 : 13 db02 Keepalived [ 27070 ] : Starting Healthcheck child process , pid = 27071 Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Netlink reflector reports IP 10.211.55.6 added Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fef9 : 58cf added Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Netlink reflector reports IP fe80 :: 21c : 42ff : fef9 : 58cf added Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Registering Kernel netlink reflector Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Registering Kernel netlink command channel Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Registering gratuitous ARP shared channel Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Opening file '/etc/keepalived/keepalived.conf' . Jul 15 00 : 31 : 13 db02 Keepalived [ 27070 ] : Starting VRRP child process , pid = 27073 Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Configuration is using : 67830 Bytes Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : Using LinkWatch kernel netlink reflector . . . Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : VRRP_Instance ( VI_1 ) Entering BACKUP STATE Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : VRRP sockpool : [ ifindex ( 2 ) , proto ( 112 ) , unicast ( 0 ) , fd ( 10 , 11 ) ] Jul 15 00 : 31 : 13 db02 kernel : IPVS : Registered protocols ( TCP , UDP , AH , ESP ) Jul 15 00 : 31 : 13 db02 kernel : IPVS : Connection hash table configured ( size = 4096 , memory = 64Kbytes ) Jul 15 00 : 31 : 13 db02 kernel : IPVS : ipvs loaded . Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Netlink reflector reports IP 10.211.55.6 added Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fef9 : 58cf added Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Netlink reflector reports IP fe80 :: 21c : 42ff : fef9 : 58cf added Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Registering Kernel netlink reflector Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Registering Kernel netlink command channel Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Opening file '/etc/keepalived/keepalived.conf' . Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Configuration is using : 11897 Bytes Jul 15 00 : 31 : 13 db02 kernel : IPVS : [ wrr ] scheduler registered . Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Using LinkWatch kernel netlink reflector . . . Jul 15 00 : 31 : 13 db02 Keepalived_healthcheckers [ 27071 ] : Activating healthchecker for service [ 10.211.55.6 ] : 3306 Jul 15 00 : 31 : 13 db02 Keepalived_vrrp [ 27073 ] : VRRP_Script ( check_run ) succeeded Jul 15 00 : 32 : 35 db02 Keepalived_healthcheckers [ 27071 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fef9 : 58cf added Jul 15 00 : 32 : 35 db02 Keepalived_vrrp [ 27073 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fef9 : 58cf added

停止db01的mysql，检查/var/log/messages,发现vip 已经转移到db02

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

[ root @ db01 ymz ] # tail -100f /var/log/messages Jul 15 00 : 39 : 56 db01 Keepalived_healthcheckers [ 25484 ] : TCP connection to [ 10.211.55.5 ] : 3306 failed ! ! ! Jul 15 00 : 39 : 56 db01 Keepalived_healthcheckers [ 25484 ] : Removing service [ 10.211.55.5 ] : 3306 from VS [ 10.211.55.100 ] : 3306 Jul 15 00 : 39 : 56 db01 Keepalived_healthcheckers [ 25484 ] : Executing [ / etc / keepalived / mysql_down .sh ] for service [ 10.211.55.5 ] : 3306 in VS [ 10.211.55.100 ] : 3306 Jul 15 00 : 39 : 56 db01 Keepalived_healthcheckers [ 25484 ] : Lost quorum 1 - 0 = 1 > 0 for VS [ 10.211.55.100 ] : 3306 Jul 15 00 : 39 : 56 db01 Keepalived_healthcheckers [ 25484 ] : Remote SMTP server [ 127.0.0.1 ] : 25 connected . Jul 15 00 : 39 : 56 db01 Keepalived [ 25483 ] : Stopping Keepalived v1 . 2.13 ( 07 / 09 , 2014 ) Jul 15 00 : 39 : 56 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) sending 0 priority Jul 15 00 : 39 : 56 db01 Keepalived_vrrp [ 25485 ] : VRRP_Instance ( VI_1 ) removing protocol VIPs . Jul 15 00 : 39 : 56 db01 avahi - daemon [ 2898 ] : Withdrawing address record for 10.211.55.100 on eth0 .   [ root @ db02 ymz ] # tail -100f /var/log/messages Jul 15 00 : 39 : 52 db02 Keepalived_vrrp [ 27073 ] : VRRP_Instance ( VI_1 ) Transition to MASTER STATE Jul 15 00 : 39 : 52 db02 Keepalived_vrrp [ 27073 ] : VRRP_Group ( VG1 ) Syncing instances to MASTER state Jul 15 00 : 39 : 53 db02 Keepalived_vrrp [ 27073 ] : VRRP_Instance ( VI_1 ) Entering MASTER STATE Jul 15 00 : 39 : 53 db02 Keepalived_vrrp [ 27073 ] : VRRP_Instance ( VI_1 ) setting protocol VIPs . Jul 15 00 : 39 : 53 db02 Keepalived_vrrp [ 27073 ] : VRRP_Instance ( VI_1 ) Sending gratuitous ARPs on eth0 for 10.211.55.100 Jul 15 00 : 39 : 53 db02 Keepalived_vrrp [ 27073 ] : Netlink reflector reports IP 10.211.55.100 added Jul 15 00 : 39 : 53 db02 Keepalived_healthcheckers [ 27071 ] : Netlink reflector reports IP 10.211.55.100 added Jul 15 00 : 39 : 53 db02 avahi - daemon [ 2808 ] : Registering new address record for 10.211.55.100 on eth0 . Jul 15 00 : 39 : 58 db02 Keepalived_vrrp [ 27073 ] : VRRP_Instance ( VI_1 ) Sending gratuitous ARPs on eth0 for 10.211.55.100 Jul 15 00 : 40 : 15 db02 Keepalived_healthcheckers [ 27071 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fef9 : 58cf added Jul 15 00 : 40 : 15 db02 Keepalived_vrrp [ 27073 ] : Netlink reflector reports IP fdb2 : 2c26 : f4e4 : 0 : 21c : 42ff : fef9 : 58cf added Jul 15 00 : 40 : 41 db02 dhclient : DHCPREQUEST on eth0 to 10.211.55.1 port 67 ( xid = 0x6b67ba4c ) Jul 15 00 : 40 : 41 db02 dhclient : DHCPACK from 10.211.55.1 ( xid = 0x6b67ba4c ) Jul 15 00 : 40 : 41 db02 dhclient : bound to 10.211.55.6 -- renewal in 781 seconds

异常处理
# 编译是报错 ./configure –prefix=/usr/local/keepalived
configure: error: No SO_MARK declaration in headers
增减 –disable-fwmark
./configure –prefix=/usr/local/keepalived –disable-fwmark

参考：

https://github.com/acassen/keepalived/blob/master/configure.in

http://mrhouzhibin.blog.163.com/blog/static/194596241201111109501168/