Keepalived构建双主MySQL

pom

　　文章转自：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 安装
123456789# 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#
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465#vi /etc/keepalived/keepalived.conf!ConfigurationFileforkeepalivedglobal_defs{  notification_email{      cloudhorizon@163.com  }  notification_email_fromdb01@ymz.com  smtp_server127.0.0.1  smtp_connect_timeout30  router_idmysql-ha}vrrp_scriptcheck_run{    script"/etc/keepalived/check_mysql.sh"    interval5}vrrp_sync_groupVG1{    group{          VI_1    }}vrrp_instanceVI_1{    stateBACKUP    interfaceeth0    virtual_router_id51    #db01为100，db02为99    priority100    advert_int1    authentication{        auth_typePASS        auth_pass1111    }    track_script{      check_run    }    virtual_ipaddress{        10.211.55.100    }}virtual_server10.211.55.1003306{    #每个2秒检查一次real_server状态    delay_loop2      #LVS算法    lb_algowrr    #LVS模式    lb_kindDR    #会话保持时间    persistence_timeout60    protocolTCP    #此处M-S的IP应该不同    real_server10.211.55.53306{        weight3        #检测到服务down后执行的脚本        notify_down/etc/keepalived/mysql_down.sh        TCP_CHECK{            #连接超时时间            connect_timeout10            #重连次数            nb_get_retry3            #重连间隔时间            delay_before_retry3            #健康检查端口            connect_port3306    } }　　配置mysql状态检测脚本 check_mysql.sh，主备相同
1234567891011121314151617181920212223242526272829303132333435# vi /etc/keepalived/check_mysql.sh!/bin/bashMYSQL=/usr/local/mysql/bin/mysqlMYSQL_HOST=10.211.55.5MYSQL_USER=rootMYSQL_PASSWORD=rootCHECK_TIME=3#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0MYSQL_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 1done1# chmod 755 /etc/keepalived/check_mysql.sh　　## 检测数据库关闭时执行的脚本mysql_down.sh
1234# vi /etc/keepalived/mysql_down.sh!/bin/bashservice  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
12345678910111213141516171819202122232425262728293031323334353637383940414243#/usr/local/keepalived/sbin/keepalived-D# ps -aux | grep keepalived # tail -f /var/logs/messagesJul1500:19:55db01Keepalived[25463]:StoppingKeepalivedv1.2.13(07/09,2014)Jul1500:19:55db01Keepalived_vrrp[25465]:VRRP_Instance(VI_1)sending0priorityJul1500:19:55db01Keepalived_vrrp[25465]:VRRP_Instance(VI_1)removingprotocolVIPs.Jul1500:19:55db01Keepalived_healthcheckers[25464]:NetlinkreflectorreportsIP10.211.55.100removedJul1500:19:55db01Keepalived_healthcheckers[25464]:Removingservice[10.211.55.5]:3306fromVS[10.211.55.100]:3306Jul1500:19:55db01avahi-daemon[2898]:Withdrawingaddressrecordfor10.211.55.100oneth0.Jul1500:19:57db01Keepalived[25482]:StartingKeepalivedv1.2.13(07/09,2014)Jul1500:19:57db01Keepalived[25483]:StartingHealthcheckchildprocess,pid=25484Jul1500:19:57db01Keepalived[25483]:StartingVRRPchildprocess,pid=25485Jul1500:19:57db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIP10.211.55.5addedJul1500:19:57db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fed9:4db2addedJul1500:19:57db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIPfe80::21c:42ff:fed9:4db2addedJul1500:19:57db01Keepalived_healthcheckers[25484]:RegisteringKernelnetlinkreflectorJul1500:19:57db01Keepalived_healthcheckers[25484]:RegisteringKernelnetlinkcommandchannelJul1500:19:57db01Keepalived_healthcheckers[25484]:Openingfile'/etc/keepalived/keepalived.conf'.Jul1500:19:57db01Keepalived_healthcheckers[25484]:Configurationisusing:11915BytesJul1500:19:57db01Keepalived_healthcheckers[25484]:UsingLinkWatchkernelnetlinkreflector...Jul1500:19:57db01Keepalived_healthcheckers[25484]:Activatinghealthcheckerforservice[10.211.55.5]:3306Jul1500:19:57db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIP10.211.55.5addedJul1500:19:57db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fed9:4db2addedJul1500:19:57db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIPfe80::21c:42ff:fed9:4db2addedJul1500:19:57db01Keepalived_vrrp[25485]:RegisteringKernelnetlinkreflectorJul1500:19:57db01Keepalived_vrrp[25485]:RegisteringKernelnetlinkcommandchannelJul1500:19:57db01Keepalived_vrrp[25485]:RegisteringgratuitousARPsharedchannelJul1500:19:57db01Keepalived_vrrp[25485]:Openingfile'/etc/keepalived/keepalived.conf'.Jul1500:19:57db01Keepalived_vrrp[25485]:Configurationisusing:67848BytesJul1500:19:57db01Keepalived_vrrp[25485]:UsingLinkWatchkernelnetlinkreflector...Jul1500:19:57db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)EnteringBACKUPSTATEJul1500:19:57db01Keepalived_vrrp[25485]:VRRPsockpool:[ifindex(2),proto(112),unicast(0),fd(10,11)]Jul1500:19:57db01Keepalived_vrrp[25485]:VRRP_Script(check_run)succeededJul1500:20:01db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)TransitiontoMASTERSTATEJul1500:20:01db01Keepalived_vrrp[25485]:VRRP_Group(VG1)SyncinginstancestoMASTERstateJul1500:20:02db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)EnteringMASTERSTATEJul1500:20:02db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)settingprotocolVIPs.Jul1500:20:02db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.211.55.100Jul1500:20:02db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIP10.211.55.100addedJul1500:20:02db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIP10.211.55.100addedJul1500:20:02db01avahi-daemon[2898]:Registeringnewaddressrecordfor10.211.55.100oneth0.Jul1500:20:07db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.211.55.100　　使用VIP 连接mysql
1234567891011121314151617181920212223242526272829[iyunv@db02 keepalived]# mysql -uroot -proot -h 10.211.55.100Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 420Server 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:        420Current database:    Current user:        root@10.211.55.6SSL:            Not in useCurrent pager:        stdoutUsing outfile:        ''Using delimiter:    ;Server version:        5.6.18-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)Protocol version:    10Connection:        10.211.55.100 via TCP/IPServer characterset:    latin1Db     characterset:    latin1Client characterset:    latin1Conn.  characterset:    latin1TCP port:        3306Uptime:            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
12345678910111213141516171819202122232425262728293031323334# /usr/local/keepalived/sbin/keepalived-D# ps -aux | grep keepalived # tail -f /var/logs/messagesJul1500:31:13db02Keepalived[27069]:StartingKeepalivedv1.2.13(07/14,2014)Jul1500:31:13db02Keepalived[27070]:StartingHealthcheckchildprocess,pid=27071Jul1500:31:13db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIP10.211.55.6addedJul1500:31:13db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfaddedJul1500:31:13db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfe80::21c:42ff:fef9:58cfaddedJul1500:31:13db02Keepalived_vrrp[27073]:RegisteringKernelnetlinkreflectorJul1500:31:13db02Keepalived_vrrp[27073]:RegisteringKernelnetlinkcommandchannelJul1500:31:13db02Keepalived_vrrp[27073]:RegisteringgratuitousARPsharedchannelJul1500:31:13db02Keepalived_vrrp[27073]:Openingfile'/etc/keepalived/keepalived.conf'.Jul1500:31:13db02Keepalived[27070]:StartingVRRPchildprocess,pid=27073Jul1500:31:13db02Keepalived_vrrp[27073]:Configurationisusing:67830BytesJul1500:31:13db02Keepalived_vrrp[27073]:UsingLinkWatchkernelnetlinkreflector...Jul1500:31:13db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)EnteringBACKUPSTATEJul1500:31:13db02Keepalived_vrrp[27073]:VRRPsockpool:[ifindex(2),proto(112),unicast(0),fd(10,11)]Jul1500:31:13db02kernel:IPVS:Registeredprotocols(TCP,UDP,AH,ESP)Jul1500:31:13db02kernel:IPVS:Connectionhashtableconfigured(size=4096,memory=64Kbytes)Jul1500:31:13db02kernel:IPVS:ipvsloaded.Jul1500:31:13db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIP10.211.55.6addedJul1500:31:13db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfaddedJul1500:31:13db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfe80::21c:42ff:fef9:58cfaddedJul1500:31:13db02Keepalived_healthcheckers[27071]:RegisteringKernelnetlinkreflectorJul1500:31:13db02Keepalived_healthcheckers[27071]:RegisteringKernelnetlinkcommandchannelJul1500:31:13db02Keepalived_healthcheckers[27071]:Openingfile'/etc/keepalived/keepalived.conf'.Jul1500:31:13db02Keepalived_healthcheckers[27071]:Configurationisusing:11897BytesJul1500:31:13db02kernel:IPVS:[wrr]schedulerregistered.Jul1500:31:13db02Keepalived_healthcheckers[27071]:UsingLinkWatchkernelnetlinkreflector...Jul1500:31:13db02Keepalived_healthcheckers[27071]:Activatinghealthcheckerforservice[10.211.55.6]:3306Jul1500:31:13db02Keepalived_vrrp[27073]:VRRP_Script(check_run)succeededJul1500:32:35db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfaddedJul1500:32:35db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfadded　　停止db01的mysql，检查/var/log/messages,发现vip 已经转移到db02
1234567891011121314151617181920212223242526[iyunv@db01ymz]#tail -100f /var/log/messagesJul1500:39:56db01Keepalived_healthcheckers[25484]:TCPconnectionto[10.211.55.5]:3306failed!!!Jul1500:39:56db01Keepalived_healthcheckers[25484]:Removingservice[10.211.55.5]:3306fromVS[10.211.55.100]:3306Jul1500:39:56db01Keepalived_healthcheckers[25484]:Executing[/etc/keepalived/mysql_down.sh]forservice[10.211.55.5]:3306inVS[10.211.55.100]:3306Jul1500:39:56db01Keepalived_healthcheckers[25484]:Lostquorum1-0=1>0forVS[10.211.55.100]:3306Jul1500:39:56db01Keepalived_healthcheckers[25484]:RemoteSMTPserver[127.0.0.1]:25connected.Jul1500:39:56db01Keepalived[25483]:StoppingKeepalivedv1.2.13(07/09,2014)Jul1500:39:56db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)sending0priorityJul1500:39:56db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)removingprotocolVIPs.Jul1500:39:56db01avahi-daemon[2898]:Withdrawingaddressrecordfor10.211.55.100oneth0. [iyunv@db02ymz]#tail -100f /var/log/messagesJul1500:39:52db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)TransitiontoMASTERSTATEJul1500:39:52db02Keepalived_vrrp[27073]:VRRP_Group(VG1)SyncinginstancestoMASTERstateJul1500:39:53db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)EnteringMASTERSTATEJul1500:39:53db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)settingprotocolVIPs.Jul1500:39:53db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.211.55.100Jul1500:39:53db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIP10.211.55.100addedJul1500:39:53db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIP10.211.55.100addedJul1500:39:53db02avahi-daemon[2808]:Registeringnewaddressrecordfor10.211.55.100oneth0.Jul1500:39:58db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.211.55.100Jul1500:40:15db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfaddedJul1500:40:15db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfaddedJul1500:40:41db02dhclient:DHCPREQUESToneth0to10.211.55.1port67(xid=0x6b67ba4c)Jul1500:40:41db02dhclient:DHCPACKfrom10.211.55.1(xid=0x6b67ba4c)Jul1500:40:41db02dhclient:boundto10.211.55.6--renewalin781seconds　　异常处理
　　# 编译是报错 ./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/