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

!ConfigurationFileforkeepalived

global_defs{

  notification_email{

      [e-mail]

  }

  [e-mail]

  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，主备相同

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

Jul1500:19:55db01Keepalived[25463]:StoppingKeepalivedv1.2.13(07/09,2014)

Jul1500:19:55db01Keepalived_vrrp[25465]:VRRP_Instance(VI_1)sending0priority

Jul1500:19:55db01Keepalived_vrrp[25465]:VRRP_Instance(VI_1)removingprotocolVIPs.

Jul1500:19:55db01Keepalived_healthcheckers[25464]:NetlinkreflectorreportsIP10.211.55.100removed

Jul1500:19:55db01Keepalived_healthcheckers[25464]:Removingservice[10.211.55.5]:3306fromVS[10.211.55.100]:3306

Jul1500:19:55db01avahi-daemon[2898]:Withdrawingaddressrecordfor10.211.55.100oneth0.

Jul1500:19:57db01Keepalived[25482]:StartingKeepalivedv1.2.13(07/09,2014)

Jul1500:19:57db01Keepalived[25483]:StartingHealthcheckchildprocess,pid=25484

Jul1500:19:57db01Keepalived[25483]:StartingVRRPchildprocess,pid=25485

Jul1500:19:57db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIP10.211.55.5added

Jul1500:19:57db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fed9:4db2added

Jul1500:19:57db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIPfe80::21c:42ff:fed9:4db2added

Jul1500:19:57db01Keepalived_healthcheckers[25484]:RegisteringKernelnetlinkreflector

Jul1500:19:57db01Keepalived_healthcheckers[25484]:RegisteringKernelnetlinkcommandchannel

Jul1500:19:57db01Keepalived_healthcheckers[25484]:Openingfile'/etc/keepalived/keepalived.conf'.

Jul1500:19:57db01Keepalived_healthcheckers[25484]:Configurationisusing:11915Bytes

Jul1500:19:57db01Keepalived_healthcheckers[25484]:UsingLinkWatchkernelnetlinkreflector...

Jul1500:19:57db01Keepalived_healthcheckers[25484]:Activatinghealthcheckerforservice[10.211.55.5]:3306

Jul1500:19:57db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIP10.211.55.5added

Jul1500:19:57db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fed9:4db2added

Jul1500:19:57db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIPfe80::21c:42ff:fed9:4db2added

Jul1500:19:57db01Keepalived_vrrp[25485]:RegisteringKernelnetlinkreflector

Jul1500:19:57db01Keepalived_vrrp[25485]:RegisteringKernelnetlinkcommandchannel

Jul1500:19:57db01Keepalived_vrrp[25485]:RegisteringgratuitousARPsharedchannel

Jul1500:19:57db01Keepalived_vrrp[25485]:Openingfile'/etc/keepalived/keepalived.conf'.

Jul1500:19:57db01Keepalived_vrrp[25485]:Configurationisusing:67848Bytes

Jul1500:19:57db01Keepalived_vrrp[25485]:UsingLinkWatchkernelnetlinkreflector...

Jul1500:19:57db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)EnteringBACKUPSTATE

Jul1500:19:57db01Keepalived_vrrp[25485]:VRRPsockpool:[ifindex(2),proto(112),unicast(0),fd(10,11)]

Jul1500:19:57db01Keepalived_vrrp[25485]:VRRP_Script(check_run)succeeded

Jul1500:20:01db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)TransitiontoMASTERSTATE

Jul1500:20:01db01Keepalived_vrrp[25485]:VRRP_Group(VG1)SyncinginstancestoMASTERstate

Jul1500:20:02db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)EnteringMASTERSTATE

Jul1500:20:02db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)settingprotocolVIPs.

Jul1500:20:02db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.211.55.100

Jul1500:20:02db01Keepalived_vrrp[25485]:NetlinkreflectorreportsIP10.211.55.100added

Jul1500:20:02db01Keepalived_healthcheckers[25484]:NetlinkreflectorreportsIP10.211.55.100added

Jul1500:20:02db01avahi-daemon[2898]:Registeringnewaddressrecordfor10.211.55.100oneth0.

Jul1500:20:07db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.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

Jul1500:31:13db02Keepalived[27069]:StartingKeepalivedv1.2.13(07/14,2014)

Jul1500:31:13db02Keepalived[27070]:StartingHealthcheckchildprocess,pid=27071

Jul1500:31:13db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIP10.211.55.6added

Jul1500:31:13db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfadded

Jul1500:31:13db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfe80::21c:42ff:fef9:58cfadded

Jul1500:31:13db02Keepalived_vrrp[27073]:RegisteringKernelnetlinkreflector

Jul1500:31:13db02Keepalived_vrrp[27073]:RegisteringKernelnetlinkcommandchannel

Jul1500:31:13db02Keepalived_vrrp[27073]:RegisteringgratuitousARPsharedchannel

Jul1500:31:13db02Keepalived_vrrp[27073]:Openingfile'/etc/keepalived/keepalived.conf'.

Jul1500:31:13db02Keepalived[27070]:StartingVRRPchildprocess,pid=27073

Jul1500:31:13db02Keepalived_vrrp[27073]:Configurationisusing:67830Bytes

Jul1500:31:13db02Keepalived_vrrp[27073]:UsingLinkWatchkernelnetlinkreflector...

Jul1500:31:13db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)EnteringBACKUPSTATE

Jul1500: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.6added

Jul1500:31:13db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfadded

Jul1500:31:13db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfe80::21c:42ff:fef9:58cfadded

Jul1500:31:13db02Keepalived_healthcheckers[27071]:RegisteringKernelnetlinkreflector

Jul1500:31:13db02Keepalived_healthcheckers[27071]:RegisteringKernelnetlinkcommandchannel

Jul1500:31:13db02Keepalived_healthcheckers[27071]:Openingfile'/etc/keepalived/keepalived.conf'.

Jul1500:31:13db02Keepalived_healthcheckers[27071]:Configurationisusing:11897Bytes

Jul1500:31:13db02kernel:IPVS:[wrr]schedulerregistered.

Jul1500:31:13db02Keepalived_healthcheckers[27071]:UsingLinkWatchkernelnetlinkreflector...

Jul1500:31:13db02Keepalived_healthcheckers[27071]:Activatinghealthcheckerforservice[10.211.55.6]:3306

Jul1500:31:13db02Keepalived_vrrp[27073]:VRRP_Script(check_run)succeeded

Jul1500:32:35db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfadded

Jul1500:32:35db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfadded

停止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@db01ymz]# tail -100f /var/log/messages

Jul1500: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]:3306

Jul1500:39:56db01Keepalived_healthcheckers[25484]:Executing[/etc/keepalived/mysql_down.sh]forservice[10.211.55.5]:3306inVS[10.211.55.100]:3306

Jul1500:39:56db01Keepalived_healthcheckers[25484]:Lostquorum1-0=1>0forVS[10.211.55.100]:3306

Jul1500: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)sending0priority

Jul1500:39:56db01Keepalived_vrrp[25485]:VRRP_Instance(VI_1)removingprotocolVIPs.

Jul1500:39:56db01avahi-daemon[2898]:Withdrawingaddressrecordfor10.211.55.100oneth0.

 

[root@db02ymz]# tail -100f /var/log/messages

Jul1500:39:52db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)TransitiontoMASTERSTATE

Jul1500:39:52db02Keepalived_vrrp[27073]:VRRP_Group(VG1)SyncinginstancestoMASTERstate

Jul1500:39:53db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)EnteringMASTERSTATE

Jul1500:39:53db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)settingprotocolVIPs.

Jul1500:39:53db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.211.55.100

Jul1500:39:53db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIP10.211.55.100added

Jul1500:39:53db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIP10.211.55.100added

Jul1500:39:53db02avahi-daemon[2808]:Registeringnewaddressrecordfor10.211.55.100oneth0.

Jul1500:39:58db02Keepalived_vrrp[27073]:VRRP_Instance(VI_1)SendinggratuitousARPsoneth0for10.211.55.100

Jul1500:40:15db02Keepalived_healthcheckers[27071]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfadded

Jul1500:40:15db02Keepalived_vrrp[27073]:NetlinkreflectorreportsIPfdb2:2c26:f4e4:0:21c:42ff:fef9:58cfadded

Jul1500: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/