mysql bingip_mysql 主从数据加keepalive

接着上一篇《配置mysql数据库的主从同步实验》，本文主要从应用的角度，在两台mysql服务器上部署keepalived服务，可以做到在任意一台mysql服务器故障的情况下，不影响mysql数据库的使用。

应用服务器配置的mysql数据库的地址是一个VIP(业务虚拟地址)，这个VIP作为keepalived中的virtual_server地址，keepalived的real_server地址分别是两台mysql数据库服务器的地址。

1、安装keepalived 。在两台服务器依次做以下操作

yum install keepalived -y

2、对keepalived配置文件进行配置。在MySQL-01上进行操作

先将/etc/keepalived/keepalived.conf文件清空，然后将下面内容复制进去：

! Configuration File for keepalived

global_defs {

notification_email {

yuwb@zycsy.cn

}

notification_email_from yuwb@zycsy.cn

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MySQL-ha

}

vrrp_instance VI_1 {

state master

interface eth0

virtual_router_id 51

priority 100

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.8.243

}

}

virtual_server 192.168.8.243 3306 {

delay_loop 6

lb_algo wrr

lb_kind DR

persistence_timeout 50

protocol TCP

real_server 192.168.8.241 3306 {

weight 3

notify_down /var/lib/mysql/killkeepalived.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

在MySQL-02上进行操作：

先将/etc/keepalived/keepalived.conf文件清空，将上面的内容中的：

“    real_server 192.168.8.241 3306 ”  改为：

“   real_server 192.168.8.242 3306 ”

在两台服务器做以下操作 ：

3、测试是否可用

创建一个测试用户 ：

在MySQL-01上多开一个终端，tailf /var/log/messages,然后再另一个终端启动keepalived服务service keepalived start。

在MySQL-02同样打开一个新的终端查看日志信息,然后启动keepalived

可以通过 ip addr 命令查看keepalived其的VIP是  192.168.8.243

[root@mysql-master ~]# ip addr

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 08:00:27:a3:63:54 brd ff:ff:ff:ff:ff:ff

inet 192.168.8.241/24 brd 192.168.8.255 scope global eth0

inet 192.168.8.243/32 scope global eth0

inet6 fe80::a00:27ff:fea3:6354/64 scope link

valid_lft forever preferred_lft forever

在MySQL-01上手动停掉MySQL服务。同时得到日志信息:

Feb 24 09:22:45 localhost Keepalived_healthcheckers[31710]: TCP connection to [192.168.8.241]:3306 failed !!!

Feb 24 09:22:45 localhost Keepalived_healthcheckers[31710]: Removing service [192.168.8.241]:3306 from VS [192.168.8.243]:3306

Feb 24 09:22:45 localhost Keepalived_healthcheckers[31710]: Executing [/var/lib/mysql/killkeepalived.sh] for service [192.168.8.241]:3306 in VS [192.168.8.243]:3306

Feb 24 09:22:45 localhost Keepalived_healthcheckers[31710]: Lost quorum 1-0=1 > 0 for VS [192.168.8.243]:3306

Feb 24 09:22:45 localhost Keepalived_healthcheckers[31710]: Remote SMTP server [127.0.0.1]:25 connected.

Feb 24 09:22:45 localhost kernel: IPVS: __ip_vs_del_service: enter

Feb 24 09:22:45 localhost Keepalived[31708]: Stopping Keepalived v1.2.13 (03/19,2015)

Feb 24 09:22:45 localhost Keepalived_vrrp[31711]: VRRP_Instance(VI_1) sending 0 priority

Feb 24 09:22:45 localhost Keepalived_vrrp[31711]: VRRP_Instance(VI_1) removing protocol VIPs.

Feb 24 09:22:45 localhost Keepalived_healthcheckers[31710]: Netlink reflector reports IP 192.168.8.243 removed

在MySQL-02上的日志信息如下：

Feb 24 09:22:46 localhost Keepalived_vrrp[31896]: VRRP_Instance(VI_1) Transition to MASTER STATE

Feb 24 09:22:47 localhost Keepalived_vrrp[31896]: VRRP_Instance(VI_1) Entering MASTER STATE

Feb 24 09:22:47 localhost Keepalived_vrrp[31896]: VRRP_Instance(VI_1) setting protocol VIPs.

Feb 24 09:22:47 localhost Keepalived_vrrp[31896]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.8.243

Feb 24 09:22:47 localhost Keepalived_healthcheckers[31894]: Netlink reflector reports IP 192.168.8.243 added

Feb 24 09:22:52 localhost Keepalived_vrrp[31896]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.8.243

在两台服务器上执行ip addr命令查看，发现VIP地址192.168.8.243已经切换到了MySQL-02上。因此也就实现了应用连接数据库的自动切换。

此时可以使用连接MySQL的工具测试一下，虚拟ip地址还是可用的。如下所示：

[root@mysql-master ~]# mysql -u test -p123456 -h 192.168.8.243

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 243

Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, 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>