mysql 主從資料加keepalive

標籤：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 {  
    [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 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

[[email protected] ~]# 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地址還是可用的。如下所示：

[[email protected] ~]# 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>

本文出自 “yuweibing的技術部落格” 部落格，請務必保留此出處http://yuweibing.blog.51cto.com/3879355/1744722

mysql 主從資料加keepalive