mysql 主从数据加keepalive

12312

  接着上一篇《配置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 ”


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

vim /var/lib/mysql/killkeepalived.sh
      
#!/bin/sh  
pkill keepalived
      
chmod +x /var/lib/mysql/killkeepalived.sh



3、测试是否可用
创建一个测试用户 ：
mysql> grant all privileges on *.* to 'test'@'%' identified by '123456';
mysql> flush privileges;



在MySQL-01上多开一个终端，tailf /var/log/messages,然后再另一个终端启动keepalived服务service keepalived start。
在MySQL-02同样打开一个新的终端查看日志信息,然后启动keepalived
可以通过 ip addr 命令查看keepalived其的VIP是  192.168.8.243

[iyunv@mysql-master ~]# 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 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地址还是可用的。如下所示：

[iyunv@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>