利用Keepalived實現MySQL高可用

使用MySQL雙master+keepalived是一種很好的HA解決方案，在MySQL-HA環境中，MySQL互為主從關係，這樣就保證了兩臺MySQL資料的一致性，然後用keepalived實現虛擬IP，通過keepalived自帶的服務監控功能來實現MySQL故障時自動切換。

MySQL雙主配置

master1 : 192.168.90.128
master2 : 192.168.90.129
mysql-vip: 192.168.90.200

keepalived安裝及配置

安裝依賴包
shell> rpm -q openssl openssl-devel popt 
openssl-1.0.0-20.el6.i686
openssl-devel-1.0.0-20.el6.i686
popt-1.13-7.el6.i686

安裝keepalived
shell> tar -zxvf keepalived-1.2.14
shell> cd keepalived-1.2.14/

shell> ./configure --prefix=/usr/local/keepalived 
·
·
Keepalived configuration
------------------------
Keepalived version       : 1.2.14
Compiler                 : gcc
Compiler flags           : -g -O2
Extra Lib                : -lssl -lcrypto -lcrypt 
Use IPVS Framework       : Yes
IPVS sync daemon support : Yes
IPVS use libnl           : No
fwmark socket support    : Yes
Use VRRP Framework       : Yes
Use VRRP VMAC            : Yes
SNMP support             : No
SHA1 support             : No
Use Debug flags          : No

shell> make && make install

shell> ln -s /usr/local/keepalived/sbin/keepalived /usr/local/bin/keepalived

配置keepalived
shell> mkdir -p /etc/keepalived/
shell> vi /etc/keepalived/keepalived.conf
=========================================================================
#全域性配置
global_defs {
  #表示keepalived在發生諸如切換操作時傳送Email給哪些地址，郵件地址可以多個，每行一個
  notification_email {
    test@admin.com
  }

  #表示傳送通知郵件時郵件源地址是誰
  notification_email_from admin@aidmin.com
  #表示傳送email時使用的smtp伺服器地址，這裡可以用本地的sendmail來實現
  smtp_server 127.0.0.1
  #連線smtp連線超時時間
  smtp_connect_timeout 30
  #機器標識
  router_id MySQL-ha
}

vrrp_instance VI_1 {
  state BACKUP   #兩臺配置此處均是BACKUP
  interface eth0
  virtual_router_id 51
  priority 100   #優先順序，另一臺改為90
  #檢查間隔，預設為1秒
  advert_int 1
  nopreempt  #不搶佔，只在優先順序高的機器上設定即可，優先順序低的機器不設定
  authentication {
    auth_type PASS
    auth_pass 1111
  }

  #這裡設定的就是VIP，也就是虛擬IP地址
  virtual_ipaddress {
    192.168.90.200
  }
}

virtual_server 192.168.90.200 3306 {
  delay_loop 2   #每個2秒檢查一次real_server狀態
  lb_algo wrr   #LVS演算法
  lb_kind DR    #LVS模式
  persistence_timeout 60   #會話保持時間
  protocol TCP
  real_server 192.168.90.130 3306 {
    weight 3
    notify_down /db/mysql/mysql.sh  #檢測到服務down後執行的指令碼
    TCP_CHECK {
      connect_timeout 10     #連線超時時間
      nb_get_retry 3         #重連次數
      delay_before_retry 3   #重連間隔時間
      connect_port 3306      #健康檢查埠
    }
  }
}

=========================================================================
主負載均衡器與備負載均衡器配置檔案的差異：
vrrp_instance的優先順序 priority、virtual_server的 real_server、vrrp_instance的 nopreempt

shell> vi /db/mysql/mysql.sh
=========================================================================
#!/usr/bin/env bash
pkill keepalived
/etc/init.d/network restart
=========================================================================

shell> chmod u+x /db/mysql/mysql.sh

shell> keepalived –D  
檢視keepalived日誌
shell> tail -f /var/log/messages
Feb  5 23:14:32 rac1 kernel: IPVS: [wrr] scheduler registered.
Feb  5 23:14:32 rac1 Keepalived_healthcheckers[3931]: Using LinkWatch kernel netlink reflector...
Feb  5 23:14:32 rac1 Keepalived_healthcheckers[3931]: Activating healthchecker for service [192.168.90.128]:3306
Feb  5 23:14:35 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Transition to MASTER STATE
Feb  5 23:14:36 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Entering MASTER STATE
Feb  5 23:14:36 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) setting protocol VIPs.
Feb  5 23:14:36 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200
Feb  5 23:14:36 rac1 avahi-daemon[1314]: Registering new address record for 192.168.90.200 on eth0.IPv4.
Feb  5 23:14:36 rac1 Keepalived_healthcheckers[3931]: Netlink reflector reports IP 192.168.90.200 added
Feb  5 23:14:41 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200

測試驗證

遠端通過VIP登入測試

shell> mysql -h 192.168.90.200 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 556
Server version: 5.6.19-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

shell> ps -ef |grep keepalived
root      3930     1  0 23:14 ?        00:00:00 keepalived -D
root      3931  3930  0 23:14 ?        00:00:00 keepalived -D
root      3932  3930  0 23:14 ?        00:00:00 keepalived -D
root      4069  4019  0 23:46 pts/2    00:00:00 grep keepalived
shell> pkill keepalived
shell> ps -ef |grep keepalived
root      4073  4019  0 23:46 pts/2    00:00:00 grep keepalived

【db1】
Feb  5 23:46:24 rac1 Keepalived[3930]: Stopping Keepalived v1.2.14 (02/05,2015)
Feb  5 23:46:24 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) sending 0 priority
Feb  5 23:46:24 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) removing protocol VIPs.
Feb  5 23:46:24 rac1 Keepalived_healthcheckers[3931]: Removing service [192.168.90.128]:3306 from VS [192.168.1.200]:3306
Feb  5 23:46:24 rac1 avahi-daemon[1314]: Withdrawing address record for 192.168.90.200 on eth0.

【db2】
Feb  5 23:46:20 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Transition to MASTER STATE
Feb  5 23:46:21 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Entering MASTER STATE
Feb  5 23:46:21 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) setting protocol VIPs.
Feb  5 23:46:21 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200
Feb  5 23:46:21 rac2 avahi-daemon[1322]: Registering new address record for 192.168.90.200 on eth0.IPv4.
Feb  5 23:46:21 rac2 Keepalived_healthcheckers[2242]: Netlink reflector reports IP 192.168.90.200 added
Feb  5 23:46:26 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200

可以看出VIP可以自動切換，keepalived切換速度還是非常塊的

【db1】
手動關閉db1資料庫，模擬當機
shell> mysqladmin -u root -p shutdown
shell>  tail -f /var/log/messages
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: TCP connection to [192.168.90.129]:3306 failed !!!
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Removing service [192.168.90.129]:3306 from VS [192.168.90.200]:3306
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Executing [/db/mysql/mysql.sh] for service 
[192.168.90.129]:3306 in VS [192.168.90.200]:3306
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Lost quorum 1-0=1 > 0 for VS [192.168.90.200]:3306
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Remote SMTP server [127.0.0.1]:25 connected.
Feb  6 01:05:59 rac2 Keepalived[2537]: Stopping Keepalived v1.2.13 (08/07,2014)
Feb  6 01:05:59 rac2 Keepalived_vrrp[2539]: VRRP_Instance(VI_1) sending 0 priority
Feb  6 01:05:59 rac2 Keepalived_vrrp[2539]: VRRP_Instance(VI_1) removing protocol VIPs.
Feb  6 01:05:59 rac2 avahi-daemon[1322]: Withdrawing address record for 192.168.90.200 on eth0.
Feb  6 01:05:59 rac2 avahi-daemon[1322]: Withdrawing address record for 192.168.90.129 on eth0.
Feb  6 01:05:59 rac2 avahi-daemon[1322]: Leaving mDNS multicast group on interface eth0.IPv4 with address 192.168.90.129.
Feb  6 01:06:00 rac2 avahi-daemon[1322]: Interface eth0.IPv4 no longer relevant for mDNS.
Feb  6 01:06:00 rac2 avahi-daemon[1322]: Withdrawing address record for fe80::20c:29ff:fe34:8dbe on eth0.
Feb  6 01:06:01 rac2 kernel: lo: Disabled Privacy Extensions
Feb  6 01:06:01 rac2 kernel: eth0: link up
Feb  6 01:06:04 rac2 avahi-daemon[1322]: Registering new address record for fe80::20c:29ff:fe34:8dbe on eth0.*.
Feb  6 01:06:04 rac2 avahi-daemon[1322]: Joining mDNS multicast group on interface eth0.IPv4 with address 192.168.90.129.
Feb  6 01:06:05 rac2 avahi-daemon[1322]: New relevant interface eth0.IPv4 for mDNS.
Feb  6 01:06:05 rac2 avahi-daemon[1322]: Registering new address record for 192.168.90.129 on eth0.IPv4.
150206 01:06:10 mysqld_safe mysqld from pid file /mysql/mysql/rac2.pid ended

shell> ps -ef |grep mysql |grep -v grep
root      2316  2296  0 Feb05 pts/2    00:00:00 mysql -u root -p
shell> ps -ef |grep keepalived |grep -v grep

【db2】
shell>  tail -f /var/log/messages
Feb  6 01:06:04 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Transition to MASTER STATE
Feb  6 01:06:05 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Entering MASTER STATE
Feb  6 01:06:05 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) setting protocol VIPs.
Feb  6 01:06:05 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200
Feb  6 01:06:05 rac1 avahi-daemon[1314]: Registering new address record for 192.168.90.200 on eth0.IPv4.
Feb  6 01:06:05 rac1 Keepalived_healthcheckers[5183]: Netlink reflector reports IP 192.168.90.200 added
Feb  6 01:06:10 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200

shell> ip a
1: lo:  mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0:  mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
    link/ether 00:0c:29:2d:c1:03 brd ff:ff:ff:ff:ff:ff
    inet 192.168.90.128/24 brd 192.168.90.255 scope global eth0
    inet 192.168.90.200/32 scope global eth0
    inet6 fe80::20c:29ff:fe2d:c103/64 scope link 
       valid_lft forever preferred_lft forever

keepalived只能做到對資料庫埠的健康檢查，但是做不到比如像MySQL複製中的slave-SQL、slave-IO程式的檢查。所以要想做到一些細緻的健康檢查，還得需要藉助額外的監控工具，比如nagios，然後用nagios實現簡訊、郵件報警，從而能夠有效地解決問題。

整理自網路

Svoid
2015-02-05