Keepalived MySQL master-master配置高可用

一、軟體需求：

1.環境描述

操 作 系 統： CentOS 6.5 X64

MySQL資料庫： MySQL 5.1.71.1

keepalived角色劃分：

MASTER：10.8.1.11

BACKUP：10.8.1.12

VIP：10.8.1.13

2.系統包安裝

1）gcc\keepalived相關

yum install -y pcre-devel openssl-devel popt-devel

2）MySQL相關

yum install -y mysql-server mysql

二、配置MySQL資料庫Dual-Master

MySQL伺服器配置

同一時刻隻有一台keepalived角色MASTER STATE的MySQL資料庫主機提供讀寫服務。

1）mdb01

[root@mdb01 ~]# more /etc/my.cnf 

[mysqld]

server-id = 1

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema

auto-increment-increment = 2

auto-increment-offset = 1

slave-skip-errors = all

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@mdb01 ~]#

2）mdb02

[root@mdb02 ~]# more /etc/my.cnf 

server-id = 2

[root@mdb02 ~]# 

2.複制配置

1）複制使用者設定

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.8.1.%' IDENTIFIED BY 'replication';

flush privileges;

2）配置複制點

change  master to

master_host='10.8.1.12',(Master IP)

master_user='replication',

master_password='replication',

master_log_file='mysql-bin.000001',(Master binlog)

master_log_pos=106; (Master binlog position)

3）啟動複制程序

start  slave;  

4）檢視複制狀态

mysql> show slave status\G;

 ....

 Slave_IO_Running: Yes

 Slave_SQL_Running: Yes

5)驗證配置

在兩個資料庫端分别建立資料庫、表，插入記錄，如果在兩邊用戶端均可見即成功。

三、安裝配置keepalived

1.安裝keepalived

[root@mdb01 keepalived-1.2.7]# tar zxvf keepalived-1.2.7.tar.gz

[root@mdb01 keepalived-1.2.7]# cd keepalived-1.2.7

[root@mdb01 keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived

Keepalived configuration

------------------------

Keepalived version       : 1.2.7

Compiler                 : gcc

Compiler flags           : -g -O2

Extra Lib                : -lpopt -lssl -lcrypto 

Use IPVS Framework       : Yes

IPVS sync daemon support : Yes

IPVS use libnl           : No

Use VRRP Framework       : Yes

Use VRRP VMAC            : Yes

SNMP support             : No

Use Debug flags          : No

[root@mdb01 keepalived-1.2.7]#

[root@mdb01 keepalived-1.2.7]# make 

[root@mdb01 keepalived-1.2.7]# make install

2.複制相關檔案

# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

# mkdir /etc/keepalived/

# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

3.設定啟動項

啟動優先級：啟動MySQL之後啟動keepalived

4.keepalived配置檔案

[root@mdb01 ~]# more /etc/keepalived/keepalived.conf 

! Configuration File forkeepalived

global_defs {

notification_email {

[email protected]

 }

notification_email_from  [email protected]

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MYSQL_HA      

}

vrrp_instance VI_1 {

 state BACKUP          

 interface eth0

 virtual_router_id 51      

 priority 100         

 advert_int 1

 nopreempt  

 authentication {

 auth_type PASS

 auth_pass 1111

 virtual_ipaddress {

 10.8.1.13

virtual_server 10.8.1.13 3306 {

 delay_loop 2

 #lb_algo rr              

 #lb_kind DR              

 persistence_timeout 50  

 protocol TCP

 real_server 10.8.1.11 3306 {   

 weight 3

 notify_down /usr/local/keepalived/mysql.sh    

 TCP_CHECK {

 connect_timeout 3    

 nb_get_retry 3       

 delay_before_retry 3 

  }

[root@mdb01 ~]# 

[root@mdb02 ~]# more /etc/keepalived/keepalived.conf 

 priority 90         

 advert_int 1 

 real_server 10.8.1.12 3306 {   

5.相關切換腳本

當檢測到MySQL程序端口不存在時執行以下腳本mysql.sh，

停止本機keepalived程序及移除VIP，備機狀态由Backup轉換 

Master State同時擷取VIP。

[root@mdb01 ~]# more /usr/local/keepalived/mysql.sh 

#!/bin/bash

#pkill keepalived（此行可能會出現VIP漂移過程中兩台都有VIP的現象，殺程序不徹底）

/etc/init.d/keepalived stop

6.啟動keepalived

檢視VIP綁定情況

四.切換測試

1.停止master state主機MySQL資料庫

[root@mdb02 ~]# /etc/init.d/mysqld stop

Stopping mysqld:  [  OK  ]

2.mdb02移除VIP和停止keepalived程序

1）mdb02移除 protocol VIPs.

注意指令行輸出中已無VIP顯示

[root@mdb02 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> 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: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

    link/ether 00:0c:29:ec:6e:29 brd ff:ff:ff:ff:ff:ff

    inet 10.8.1.12/16 brd 10.8.255.255 scope global eth0

    inet6 fe80::20c:29ff:feec:6e29/64 scope link 

Keepalived日志輸出：

[root@mdb02 ~]# tail -f /var/log/messages 

Dec  1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: TCP connection to [10.8.1.12]:3306 failed !!!

Dec  1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Removing service [10.8.1.12]:3306 from VS [10.8.1.13]:3306

Dec  1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: IPVS: Service not defined

Dec  1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Executing [/usr/local/keepalived/mysql.sh] for service [10.8.1.12]:3306 in VS [10.8.1.13]:3306

Dec  1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Lost quorum 1-0=1 > 0 for VS [10.8.1.13]:3306

Dec  1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Remote SMTP server [127.0.0.1]:25 connected.

Dec  1 22:01:05 mdb02 Keepalived[1118]: Stopping Keepalived v1.2.7 (12/01,2015)

Dec  1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: IPVS: No such service

Dec  1 22:01:05 mdb02 Keepalived_vrrp[1122]: VRRP_Instance(VI_1) sending 0 priority

Dec  1 22:01:05 mdb02 Keepalived_vrrp[1122]: VRRP_Instance(VI_1) removing protocol VIPs.

2）keepalived程序消失

[root@mdb02 ~]# /etc/init.d/keepalived status

keepalived is stopped

3.mdb01 綁定VIP

[root@mdb01 ~]# ip addr

    link/ether 00:0c:29:02:3a:68 brd ff:ff:ff:ff:ff:ff

    inet 10.8.1.11/16 brd 10.8.255.255 scope global eth0

    inet 10.8.1.13/32 scope global eth0

    inet6 fe80::20c:29ff:fe02:3a68/64 scope link 

4.mdb01轉換為MASTER STATE

[root@mdb01 ~]# tail -f /var/log/messages 

Dec  1 22:06:34 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Transition to MASTER STATE

Dec  1 22:06:35 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Entering MASTER STATE

Dec  1 22:06:35 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) setting protocol VIPs.

Dec  1 22:06:35 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 10.8.1.13

Dec  1 22:06:35 mdb01 Keepalived_healthcheckers[1136]: Netlink reflector reports IP 10.8.1.13 added

Dec  1 22:06:40 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 10.8.1.13

5.mdb02啟動MySQL和Keepalived

MySQL和Keepalived服務均啟動後，主機轉換為BACKUP STATE

[root@mdb02 ~]# tail -f /var/log/messages

Dec  1 22:10:54 mdb02 Keepalived[2484]: Starting Keepalived v1.2.7 (12/01,2015)

Dec  1 22:10:54 mdb02 Keepalived[2485]: Starting Healthcheck child process, pid=2487

Dec  1 22:10:54 mdb02 Keepalived[2485]: Starting VRRP child process, pid=2488

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Interface queue is empty

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Netlink reflector reports IP 10.8.1.12 added

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Netlink reflector reports IP fe80::20c:29ff:feec:6e29 added

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Registering Kernel netlink reflector

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Registering Kernel netlink command channel

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Registering gratuitous ARP shared channel

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Opening file '/etc/keepalived/keepalived.conf'.

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Interface queue is empty

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Netlink reflector reports IP 10.8.1.12 added

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Netlink reflector reports IP fe80::20c:29ff:feec:6e29 added

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Registering Kernel netlink reflector

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Registering Kernel netlink command channel

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Opening file '/etc/keepalived/keepalived.conf'.

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Configuration is using : 62808 Bytes

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: Using LinkWatch kernel netlink reflector...

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Configuration is using : 11379 Bytes

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: VRRP_Instance(VI_1) Entering BACKUP STATE

Dec  1 22:10:54 mdb02 Keepalived_vrrp[2488]: VRRP sockpool: [ifindex(2), proto(112), fd(11,12)]

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: IPVS: Scheduler not found

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: IPVS: No such process

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Using LinkWatch kernel netlink reflector...

Dec  1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Activating healthchecker for service [10.8.1.12]:3306

Dec  1 22:10:54 mdb02 kernel: IPVS: Scheduler module ip_vs_ not found

Dec  1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: TCP connection to [10.8.1.12]:3306 failed !!!

Dec  1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Removing service [10.8.1.12]:3306 from VS [10.8.1.13]:3306

Dec  1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: IPVS: Service not defined

Dec  1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Executing [/usr/local/keepalived/mysql.sh] for service [10.8.1.12]:3306 in VS [10.8.1.13]:3306

Dec  1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Lost quorum 1-0=1 > 0 for VS [10.8.1.13]:3306

Dec  1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Remote SMTP server [127.0.0.1]:25 connected.

Dec  1 22:10:55 mdb02 Keepalived[2485]: Stopping Keepalived v1.2.7 (12/01,2015)

Dec  1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: IPVS: No such service

VIP不做漂移

五、總結：

正常情況下兩台Master機器的MySQL及Keepalived均為啟動狀态：

1.當角色為MASTER STATE的MySQL資料庫主機3306端口無法連接配接時即發生切換

1）停止keepalived程序。

2）移除VIP，

3）主機為故障狀态，需要人工修複。

2.BACKUP STATE狀态主機切換過程：

1）.轉變角色為MASTER STATE。

向224.0.0.18發送VRRP心跳資訊。

[root@mdb02 ~]# tcpdump vrrp

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode

listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes

22:24:42.550593 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:43.563523 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:44.565224 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:45.567882 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:46.570211 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:47.572696 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:48.575324 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:49.578470 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:50.580935 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

2）.綁定VIP，對外提供讀寫服務。

3.正常情況下，步驟1的故障主機修複後需要繼續對外服務，啟動需按照如下順序：

1）啟動MySQL資料庫。

2）啟動keepalived程序。

3）角色為BACKUP STATE。

4）接收vrrp心跳資訊。

root@mdb01 ~]# tcpdump vrrp

22:29:35.056846 IP 10.8.1.12 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:29:36.058852 IP 10.8.1.12 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:29:37.061641 IP 10.8.1.12 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

5）不對外提供讀寫服務。

注意事項：

1.當兩台伺服器MySQL及Keepalived均為啟動狀态時，當發送故障切換，

VIP在故障機與備份機漂移需要适當時間間隔，一般10-20秒左右。

2.授權兩台Mysql伺服器允許root遠端登入，用于在其他伺服器登陸測試！

mysql> grant all on *.* to'root'@'10.8.1.%' identified by '123456';

mysql> flush privileges;

本文轉自 pgmia 51CTO部落格，原文連結:http://blog.51cto.com/heyiyi/1718363