keepalived mysql 主主_MySQL主主+Keepalived高可用(一)：解决单点故障

方案简介

由keepalive实现VIP转移，从而实现高可用

环境规划

角色

IP

用途

Master

A

192.168.56.101

MySQL

DB

Master

B

192.168.56.102

VIP

192.168.56.110

KeepAlived

VIP

软件环境

OS Version：Red Hat

Enterprise Linux Server release 6.3 (Santiago)

MySQL Version:MySQL-server-5.6.21-1.el6.x86_64

KeepAlived Version:keepalived-1.2.13

部署步骤

1)配置MySQL主主

详细步骤省略…………..

Master

A>show processlist\G

***************************

1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 9654

State: Slave has read all relay log; waiting

for the slave I/O thread to update it

Info: NULL

***************************

2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 12773

State: Waiting for master to send event

Info: NULL

***************************

3. row ***************************

Id: 1677

User: slave

Host: 192.168.56.102:34325

db: NULL

Command:

Binlog Dump

Time: 9678

State: Master has sent all binlog to slave;

waiting for binlog to be updated

Info: NULL

Master

B>show processlist \G

***************************

1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 9771

State: Waiting for master to send event

Info: NULL

***************************

2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 9770

State: Slave has read all relay log; waiting

for the slave I/O thread to update it

Info: NULL

***************************

3. row ***************************

Id: 12

User: slave

Host: 192.168.56.101:46709

db: NULL

Command: Binlog

Dump

Time: 9746

State: Master has sent all binlog to slave;

waiting for binlog to be updated

Info: NULL

2)安装KeepAlived

#A和B主机都安装

]#

tar -zxvf keepalived-1.2.13.tar.gz -C /opt/

]# cd

/opt/keepalived-1.2.13/

]#

mkdir /usr/local/keepalived

]#

./configure --prefix=/usr/local/keepalived/

]#

make;make install

问题:configure报错

configure: error:

!!! OpenSSL is not properly installed on your

system. !!!

!!! Can not include OpenSSL headers

files.            !!!

]# rpm -ivh

keyutils-libs-devel-1.4-4.el6.x86_64.rpm

]# rpm -ivh

libcom_err-devel-1.41.12-12.el6.x86_64.rpm

]# rpm -ivh

pkgconfig-0.23-9.1.el6.x86_64.rpm

]# rpm -ivh

libselinux-devel-2.0.94-5.3.el6.x86_64.rpm

]# rpm -ivh

krb5-devel-1.9-33.el6.x86_64.rpm

]# rpm -ivh

zlib-devel-1.2.3-27.el6.x86_64.rpm

]# rpm -ivh

openssl-devel-1.0.0-20.el6_2.5.x86_64.rpm

3)配置KeepAlived

Create

Control File

默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件

#A主机和B主机都操作

]#

mkdir /etc/keepalived

#cp安装生成的conf模板到/etc/keepalived

]# cp

/usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

Master

A配置

a)Master A配置文件

#Master A配置文件

~]#

vim /etc/keepalived/keepalived.conf

! Configuration

File for keepalived

#全局配置

global_defs {

#表示keepalived在发生诸如切换操作时发送Email给哪些地址，邮件地址可以多个，每行一个

notification_email {

jixiang.yu@trekiz.com

}

#表示发送通知邮件时邮件源地址是谁

#表示发送email时使用的smtp服务器地址，这里可以用本地的sendmail来实现

smtp_server 127.0.0.1

#连接smtp连接超时时间

smtp_connect_timeout 30

#机器标识

router_id MySQL_HA

}

vrrp_instance

MySQL-HA{

state

BACKUP  #state指定instance的初始状态，但这里指定的不算，还是得通过优先级竞选来确定。两台配置此处均是BACKUP。

interface eth1#实例绑定的网卡，因为在配置虚拟IP的时候必须是在已有的网卡上添加的

virtual_router_id

51#这里设置VRID，这里非常重要，相同的VRID为一个组，他将决定多播的MAC地址

priority

100 #设置本节点的优先级，优先级高的为master，如另外一个节点配置为90，那此节点就是master

advert_int 1 #检查间隔，默认为1秒

nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {         #这里设置的就是VIP，也就是虚拟IP地址

192.168.56.111

}

}

virtual_server

192.168.56.111 3306{

delay_loop 2#每个2秒检查一次real_server状态

lb_algo wrr

lb_kind DR

persistence_timeout 50#会话保持时间

protocol TCP

real_server 192.168.56.101 3306{

weight 3

notify_down

/usr/local/MySQL/bin/MySQL.sh#检测到服务down后执行的脚本

TCP_CHECK {

connect_timeout 3#连接超时时间

nb_get_retry 3#重连次数

delay_before_retry 3#重连间隔时间

connect_port 3306#健康检查端口

}

}

}

b)Master A编写notify_down脚本

~]#

mkdir -p /usr/local/MySQL/bin/

~]#

vim /usr/local/MySQL/bin/MySQL.sh

#!/bin/sh

pkill keepalived

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

c)Master A启动KeepAlived服务

~]#

/usr/local/keepalived/sbin/keepalived –D

~]#

ps aux|grep keep

root     29423

0.0  0.1  39484

764 ?        Ss   20:27

0:00 /usr/local/keepalived/sbin/keepalived -D

root     29424

0.0  0.4  43660

2056 ?        S    20:27

0:00 /usr/local/keepalived/sbin/keepalived -D

root     29425

0.0  0.2  43660

1368 ?        S    20:27

0:00 /usr/local/keepalived/sbin/keepalived -D

root     29439

0.0  0.1 103240   840 pts/2

S+   20:27   0:00 grep keep

#可以看到VIP已经飘在eth1上

~]#

ip a|grep eth1

3: eth1:

mtu 1500 qdisc pfifo_fast state UP qlen

1000

inet 192.168.56.101/24 brd 192.168.56.255

scope global eth1

inet 192.168.56.111/32 scope global eth1

d)Master A测试keepalived服务

#停止MySQL服务

~]#

/etc/init.d/mysql stop

Shutting down

MySQL.....

[  OK  ]

#查看vip，发现VIP已经不存在

~]#

ip a|grep eth1

3: eth1:

mtu 1500 qdisc pfifo_fast state UP qlen

1000

inet 192.168.56.101/24 brd 192.168.56.255

scope global eth1

#查看keepalive进程消失

~]#

ps -ef|grep keep

root     29537

4894  0 20:34 pts/2    00:00:00 grep keep

#监控日志

~]#

tail -f /var/log/messages

Dec  3 20:34:08 MySQL1

Keepalived_healthcheckers[29424]: TCP connection to [192.168.56.101]:3306

failed !!!

Dec  3 20:34:08 MySQL1

Keepalived_healthcheckers[29424]: Removing service [192.168.56.101]:3306 from

VS [192.168.56.111]:3306

Dec  3 20:34:08 MySQL1

Keepalived_healthcheckers[29424]: Executing [/usr/local/MySQL/bin/MySQL.sh] for

service [192.168.56.101]:3306 in VS [192.168.56.111]:3306

Dec  3 20:34:08 MySQL1

Keepalived_healthcheckers[29424]: Lost quorum 1-0=1 > 0 for VS

[192.168.56.111]:3306

Dec  3 20:34:08 MySQL1

Keepalived_healthcheckers[29424]: Remote SMTP server [127.0.0.1]:25 connected.

Dec  3 20:34:09 MySQL1 Keepalived[29423]: Stopping

Keepalived v1.2.13 (12/03,2014)

Dec  3 20:34:09 MySQL1 Keepalived_vrrp[29425]:

VRRP_Instance(MySQL-HA{) sending 0 priority

Dec  3 20:34:09 MySQL1 Keepalived_vrrp[29425]:

VRRP_Instance(MySQL-HA{) removing protocol VIPs.

Dec  3 20:34:09 MySQL1

Keepalived_healthcheckers[29424]: Netlink reflector reports IP 192.168.56.111

removed

Dec  3 20:34:09 MySQL1 avahi-daemon[1430]: Server

startup complete. Host name is MySQL1-62.local. Local service cookie is

1197773774.

Dec  3 20:34:09 MySQL1 avahi-daemon[1430]:

Withdrawing address record for 192.168.56.111 on eth1.

Dec  3 20:34:10 MySQL1 avahi-daemon[1430]: Service

"MySQL1-62" (/services/ssh.service) successfully established.

Master

B配置

a)Master B配置文件

#跟DB1基本一致，但有三个地方不同：优先级为90、无抢占设置、real_server为本机IP

DB2~]#

vim /etc/keepalived/keepalived.conf

! Configuration

File for keepalived

global_defs {

notification_email {

jixiang.yu@trekiz.com

}

notification_email_from

jixiang.yu@trekiz.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MySQL_HA

}

vrrp_instance

MySQL-HA{

state BACKUP

interface eth1

virtual_router_id 51

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.56.111

}

}

virtual_server

192.168.56.111 3306{

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 50

protocol TCP

real_server 192.168.56.102

3306{

weight 3

notify_down

/usr/local/MySQL/bin/MySQL.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306   #健康检查端口

}

}

}

b)Master B编写notify_down脚本

~]#

mkdir -p /usr/local/MySQL/bin/

~]#

vim /usr/local/MySQL/bin/MySQL.sh

#!/bin/sh

pkill keepalived

c)Master B启动KeepAlived服务

~]#

/usr/local/keepalived/sbin/keepalived –D

~]#

ps aux|grep keep

root      2855

0.0  0.1  39484

764 ?        Ss   09:56

0:00 /usr/local/keepalive/sbin/keepalived -D

root      2856

0.0  0.3  41588

1992 ?        S    09:56

0:00 /usr/local/keepalive/sbin/keepalived -D

root      2857

0.0  0.2  41588

1288 ?        S    09:56

0:00 /usr/local/keepalive/sbin/keepalived -D

root      2865

0.0  0.1 103240   840 pts/2

S+   09:56   0:00 grep keep

#可以看到VIP已经飘在eth1上

~]#

ip a|grep eth1

3: eth1:

mtu 1500 qdisc pfifo_fast state UP qlen

1000

inet 192.168.56.102/24 brd 192.168.56.255

scope global eth1

inet 192.168.56.111/32 scope global eth1

d)Master B测试keepalived服务

#停止MySQL服务

~]#

/etc/init.d/mysql stop

Shutting down

MySQL.....

[  OK  ]

#查看vip，发现VIP已经不存在

~]#

ip a|grep eth1

3: eth1:

mtu 1500 qdisc pfifo_fast state UP qlen

1000

inet 192.168.56.101/24 brd 192.168.56.255

scope global eth1

#查看keepalive进程消失

~]#

ps -ef|grep keep

root     29537

4894  0 20:34 pts/2    00:00:00 grep keep

#监控日志

~]#

tail -f /var/log/messages

Dec  4 10:35:53 MySQL2

Keepalived_healthcheckers[6733]: TCP connection to [192.168.56.102]:3306 failed

!!!

Dec  4 10:35:53 MySQL2

Keepalived_healthcheckers[6733]: Removing service [192.168.56.102]:3306 from VS

[192.168.56.111]:3306

Dec  4 10:35:53 MySQL2 Keepalived_healthcheckers[6733]:

Executing [/usr/local/MySQL/bin/MySQL.sh] for service [192.168.56.102]:3306 in

VS [192.168.56.111]:3306

Dec  4 10:35:53 MySQL2

Keepalived_healthcheckers[6733]: Lost quorum 1-0=1 > 0 for VS

[192.168.56.111]:3306

Dec  4 10:35:53 MySQL2

Keepalived_healthcheckers[6733]: Remote SMTP server [127.0.0.1]:25 connected.

Dec  4 10:35:53 MySQL2 Keepalived[6732]: Stopping

Keepalived v1.2.13 (11/24,2014)

Dec  4 10:35:53 MySQL2 Keepalived_vrrp[6734]:

VRRP_Instance(MySQL-HA{) sending 0 priority

Dec  4 10:35:53 MySQL2 Keepalived_vrrp[6734]:

VRRP_Instance(MySQL-HA{) removing protocol VIPs.

Dec  4 10:35:53 MySQL2

Keepalived_healthcheckers[6733]: Netlink reflector reports IP 192.168.56.111

removed

Dec  4 10:35:54 MySQL2 avahi-daemon[1387]:

Withdrawing address record for 192.168.56.111 on eth1.

4)MySQL Client使用VIP连接数据库

~]#

mysql -u trekiz -h 192.168.56.111 -p

Enter password:

Welcome to the

MySQL monitor.  Commands end with ; or

\g.

Your MySQL

connection id is 2622

Server version:

5.6.21-log MySQL Community Server (GPL)

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>

5)配置init.d/keepalived脚本

cp

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

cp

/usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp

/usr/local/keepalived/sbin/keepalived /sbin/

~]#

/etc/init.d/keepalived -h

Usage:

/etc/init.d/keepalived {start|stop|reload|restart|condrestart|status}

6)KeepAlived Failover Test

a)

Master A,B都启动keepalived,vip在A上,此时A主机pkill keepalived

mysql>

show master status;

+---------------------+----------+--------------+------------------+-------------------+

| File                | Position | Binlog_Do_DB |

Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+----------+--------------+------------------+-------------------+

|

master_slave.000031 |      532 |              |                  |                   |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00

sec)

mysql>

show master status;

ERROR 2013 (HY000):

Lost connection to MySQL server during query

mysql>

show master status;

ERROR 2006 (HY000):

MySQL server has gone away

No connection.

Trying to reconnect...

Connection id:    168

Current database:

*** NONE ***

+-----------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB |

Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+----------+--------------+------------------+-------------------+

|

master_a.000021 |      532 |              |                  |                   |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00

sec)

结果

VIP就会切换到Master B上面去

b)

启动Master

A的keepalived,vip在B上,B主机pkill keepalived

mysql>

show master status;

+-----------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB |

Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+----------+--------------+------------------+-------------------+

|

master_a.000021 |      532 |              |                  |                   |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00

sec)

mysql>

show master status;

ERROR 2013 (HY000):

Lost connection to MySQL server during query

mysql>

show master status;

ERROR 2006 (HY000):

MySQL server has gone away

No connection.

Trying to reconnect...

Connection id:    2845

Current database:

*** NONE ***

+---------------------+----------+--------------+------------------+-------------------+

| File                | Position | Binlog_Do_DB |

Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+----------+--------------+------------------+-------------------+

|

master_slave.000031 |      532 |              |                  |                   |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.08

sec)

结果

VIP就会切换到Master A上面去

c)

Master A,B都启动keepalived,vip在A上,此时A主机stop mysql

结果

VIP就会切换到Master B上面去

d)

启动Master

A的keepalived,vip在B上,B主机stop mysql

结果

VIP就会切换到Master A上面去