Mysql HA-Install Keepalived+LVS+Mysql（雙主） On Redhat 6.3，keepalivedlvs

Mysql HA-Install Keepalived+LVS+Mysql（雙主） On Redhat 6.3，keepalivedlvs

主機資訊：

調度節點：Name：lvsOS:Redhat 6.3IP:192.168.56.235VIP:192.168.56.236資料節點1：Name：zbdba1OS:Redhat 6.3IP:192.168.56.230資料節點2：Name：zbdba2OS:Redhat 6.3IP:192.168.56.231用戶端：Name：ogg2OS:Redhat 6.3IP:192.168.56.231

描述：資料節點1跟資料節點2互為主從 ，應用從用戶端訪問調度節點從而訪問資料庫。節點1和節點2任意一個宕機不影響業務
LVS：
LVS叢集採用IP負載平衡技術和基於內容請求分發技術。調度器具有很好的吞吐率，將請求均衡地轉移到不同的伺服器上執行，且調度器自動屏蔽掉伺服器的故障，從而將一組伺服器構成一個高效能的、高可用的虛擬伺服器。整個伺服器叢集的結構對客戶是透明的，而且無需修改用戶端和伺服器端的程式。為此，在設計時需要考慮系統的透明性、延展性、高可用性和易管理性。
     可伸縮網路服務的幾種結構，它們都需要一個前端的負載調度器（或者多個進行主從備份）。我們先分析實現虛擬網路服務的主要技術，指出IP負載平衡技術是在負載調度器的實現技術中效率最高的。在已有的IP負載平衡技術中，主要有通過網路位址轉譯（Network Address Translation）將一組伺服器構成一個高效能的、高可用的虛擬伺服器，我們稱之為VS/NAT技術（Virtual Server via Network Address Translation）。在分析VS/NAT的缺點和網路服務的非對稱性的基礎上，我們提出了通過IP隧道實現虛擬伺服器的方法VS/TUN （Virtual Server via IP Tunneling），和通過直接路由實現虛擬伺服器的方法VS/DR（Virtual Server via Direct Routing），它們可以極大地提高系統的伸縮性。VS/NAT、VS/TUN和VS/DR技術是LVS叢集中實現的三種IP負載平衡技術。


Keepalived：
Keepalived is a routing software written in C. The main goal of this project is to provide simple and robust facilities for loadbalancing and high-availability to Linux system and Linux based infrastructures. Loadbalancing framework relies on well-known and widely used Linux Virtual Server (IPVS) kernel module providing Layer4 loadbalancing. Keepalived implements a set of checkers to dynamically and adaptively maintain and manage loadbalanced server pool according their health. On the other hand high-availability is achieved by VRRP protocol. VRRP is a fundamental brick for router failover. In addition, Keepalived implements a set of hooks to the VRRP finite state machine providing low-level and high-speed protocol interactions. Keepalived frameworks can be used independently or all together to provide resilient infrastructures.

具體步驟：
1、安裝lvs+keepalived
2、配置資料節點
3、用戶端測試
4、宕機測試

1、安裝lvs+keepalived：
在調度節點上操作
安裝依賴包：

yum install popt-devel openssl*yum install libn

安裝ipvsadm：

rpm -ivh popt-static-1.13-7.el6.x86_64.rpm [root@lvs ipvsadm-1.26]#make && make install 

安裝keepalived:

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/mkdir /etc/keepalived

配置keepalived:
具體參數解釋，可參見官方文檔：

http://www.keepalived.org/pdf/UserGuide.pdf[root@lvs keepalived]# cat keepalived.confglobal_defs {     router_id HaMySQL_1 } vrrp_sync_group VGM {     group {         VI_MYSQL     } } vrrp_instance VI_MYSQL {     state MASTER     interface eth0    virtual_router_id 100     priority 100     advert_int 1     authentication {         auth_type PASS         auth_pass 1111     }     virtual_ipaddress {         192.168.56.236/24 dev eth0 label eth0:1     }}  virtual_server 192.168.56.236 3306 {     delay_loop 6    lb_algo wlc    lb_kind DR     persistence_timeout 50     protocol TCP     nat_mask 255.255.255.0    real_server 192.168.56.230 3306 {         weight 3         TCP_CHECK {         connect_timeout 3         nb_get_retry 3         delay_before_retry 3         connect_port 3306         }     }      real_server 192.168.56.231 3306 {         weight 3         TCP_CHECK {         connect_timeout 3         nb_get_retry 3         delay_before_retry 3         connect_port 3306         }     } }

2、配置資料節點：
在資料節點1和資料節點2上進行操作


mysql的安裝以及雙主配置不再說明


配置lvs節點

vi /etc/rc.d/init.d/realserver.sh[root@zbdba1 init.d]# cat realserver.sh#!/bin/bash # description: Config realserver lo and apply noarp SNS_VIP=192.168.56.236. /etc/rc.d/init.d/functions case "$1" in start)        ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP        /sbin/route add -host $SNS_VIP dev lo:0        echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore        echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce        echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore        echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce        sysctl -p >/dev/null 2>&1        echo "RealServer Start OK"        ;; stop)        ifconfig lo:0 down        route del $SNS_VIP >/dev/null 2>&1        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce        echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore        echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce        echo "RealServer Stoped"        ;; *)        echo "Usage: $0 {start|stop}"        exit 1 esac exit 0[root@zbdba2 init.d]# cat realserver.sh#!/bin/bash # description: Config realserver lo and apply noarp SNS_VIP=192.168.56.236. /etc/rc.d/init.d/functions case "$1" in start)        ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP        /sbin/route add -host $SNS_VIP dev lo:0        echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore        echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce        echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore        echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce        sysctl -p >/dev/null 2>&1        echo "RealServer Start OK"        ;; stop)        ifconfig lo:0 down        route del $SNS_VIP >/dev/null 2>&1        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore        echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce        echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore        echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce        echo "RealServer Stoped"        ;; *)        echo "Usage: $0 {start|stop}"        exit 1 esac exit 0 chmod +x realserver.sh

啟動資料節點：

[root@zbdba1 init.d]# ./realserver.sh startRealServer Start OK[root@zbdba2 init.d]# ./realserver.sh startRealServer Start OK

啟動調度節點：

service keepalived start

查看日誌：

tail -100f /var/log/messagesJan 27 02:48:26 lvs Keepalived[2805]: Starting Keepalived v1.2.4 (01/22,2015)Jan 27 02:48:26 lvs Keepalived[2806]: Starting Healthcheck child process, pid=2808Jan 27 02:48:26 lvs Keepalived[2806]: Starting VRRP child process, pid=2809Jan 27 02:48:26 lvs Keepalived_vrrp[2809]: Interface queue is emptyJan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Interface queue is emptyJan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Netlink reflector reports IP 192.168.56.235 addedJan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Netlink reflector reports IP fe80::a00:27ff:fe9e:856c addedJan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Registering Kernel netlink reflectorJan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Registering Kernel netlink command channelJan 27 02:48:26 lvs Keepalived_vrrp[2809]: Netlink reflector reports IP 192.168.56.235 addedJan 27 02:48:26 lvs Keepalived_vrrp[2809]: Netlink reflector reports IP fe80::a00:27ff:fe9e:856c addedJan 27 02:48:26 lvs Keepalived_vrrp[2809]: Registering Kernel netlink reflectorJan 27 02:48:26 lvs Keepalived_vrrp[2809]: Registering Kernel netlink command channelJan 27 02:48:26 lvs Keepalived_vrrp[2809]: Registering gratuitous ARP shared channelJan 27 02:48:46 lvs Keepalived_healthcheckers[2808]: Opening file '/etc/keepalived/keepalived.conf'.Jan 27 02:48:46 lvs Keepalived_healthcheckers[2808]: Configuration is using : 12606 BytesJan 27 02:48:46 lvs Keepalived_vrrp[2809]: Opening file '/etc/keepalived/keepalived.conf'.Jan 27 02:48:46 lvs Keepalived_vrrp[2809]: Configuration is using : 63868 BytesJan 27 02:48:46 lvs Keepalived_vrrp[2809]: Using LinkWatch kernel netlink reflector...Jan 27 02:48:46 lvs Keepalived_vrrp[2809]: VRRP sockpool: [ifindex(2), proto(112), fd(10,11)]Jan 27 02:48:46 lvs Keepalived_healthcheckers[2808]: Using LinkWatch kernel netlink reflector...Jan 27 02:48:46 lvs Keepalived_healthcheckers[2808]: Activating healthchecker for service [192.168.56.230]:3306Jan 27 02:48:46 lvs Keepalived_healthcheckers[2808]: Activating healthchecker for service [192.168.56.231]:3306Jan 27 02:48:47 lvs Keepalived_vrrp[2809]: VRRP_Instance(VI_MYSQL) Transition to MASTER STATEJan 27 02:48:48 lvs Keepalived_vrrp[2809]: VRRP_Instance(VI_MYSQL) Entering MASTER STATEJan 27 02:48:48 lvs Keepalived_vrrp[2809]: VRRP_Instance(VI_MYSQL) setting protocol VIPs.Jan 27 02:48:48 lvs Keepalived_vrrp[2809]: VRRP_Instance(VI_MYSQL) Sending gratuitous ARPs on eth0 for 192.168.56.236Jan 27 02:48:48 lvs Keepalived_vrrp[2809]: VRRP_Group(VGM) Syncing instances to MASTER stateJan 27 02:48:48 lvs Keepalived_healthcheckers[2808]: Netlink reflector reports IP 192.168.56.236 addedJan 27 02:48:53 lvs Keepalived_vrrp[2809]: VRRP_Instance(VI_MYSQL) Sending gratuitous ARPs on eth0 for 192.168.56.236

查看ip：

[root@lvs keepalived]# ip a1: 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 forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000    link/ether 08:00:27:9e:85:6c brd ff:ff:ff:ff:ff:ff    inet 192.168.56.235/24 brd 192.168.56.255 scope global eth0    inet 192.168.56.236/32 scope global eth0    inet6 fe80::a00:27ff:fe9e:856c/64 scope link       valid_lft forever preferred_lft forever[root@lvs keepalived]# ifconfigeth0      Link encap:Ethernet  HWaddr 08:00:27:9E:85:6C           inet addr:192.168.56.235  Bcast:192.168.56.255  Mask:255.255.255.0          inet6 addr: fe80::a00:27ff:fe9e:856c/64 Scope:Link          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1          RX packets:26130 errors:0 dropped:0 overruns:0 frame:0          TX packets:50826 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:1000          RX bytes:20095690 (19.1 MiB)  TX bytes:3705309 (3.5 MiB)eth0:1    Link encap:Ethernet  HWaddr 08:00:27:9E:85:6C           inet addr:192.168.56.236  Bcast:0.0.0.0  Mask:255.255.255.0          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1lo        Link encap:Local Loopback           inet addr:127.0.0.1  Mask:255.0.0.0          inet6 addr: ::1/128 Scope:Host          UP LOOPBACK RUNNING  MTU:16436  Metric:1          RX packets:268 errors:0 dropped:0 overruns:0 frame:0          TX packets:268 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:0          RX bytes:21264 (20.7 KiB)  TX bytes:21264 (20.7 KiB)

查看lvs節點：

[root@lvs keepalived]# ipvsadmIP Virtual Server version 1.2.1 (size=4096)Prot LocalAddress:Port Scheduler Flags  -> RemoteAddress:Port           Forward Weight ActiveConn InActConnTCP  192.168.56.236:mysql wlc  -> 192.168.56.230:mysql         Route   3      0          0          -> 192.168.56.231:mysql         Route   3      0          0      

查看資料節點：

[root@zbdba1 init.d]# ip a1: 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    inet 192.168.56.236/32 brd 192.168.56.236 scope global lo:0    inet6 ::1/128 scope host       valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000    link/ether 08:00:27:7d:74:19 brd ff:ff:ff:ff:ff:ff    inet 192.168.56.230/24 brd 192.168.56.255 scope global eth0    inet6 fe80::a00:27ff:fe7d:7419/64 scope link       valid_lft forever preferred_lft forever[root@zbdba1 init.d]# ifconfigeth0      Link encap:Ethernet  HWaddr 08:00:27:7D:74:19           inet addr:192.168.56.230  Bcast:192.168.56.255  Mask:255.255.255.0          inet6 addr: fe80::a00:27ff:fe7d:7419/64 Scope:Link          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1          RX packets:12835 errors:0 dropped:0 overruns:0 frame:0          TX packets:7778 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:1000          RX bytes:924234 (902.5 KiB)  TX bytes:896034 (875.0 KiB)lo        Link encap:Local Loopback           inet addr:127.0.0.1  Mask:255.0.0.0          inet6 addr: ::1/128 Scope:Host          UP LOOPBACK RUNNING  MTU:16436  Metric:1          RX packets:32 errors:0 dropped:0 overruns:0 frame:0          TX packets:32 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:0          RX bytes:16477 (16.0 KiB)  TX bytes:16477 (16.0 KiB)lo:0      Link encap:Local Loopback           inet addr:192.168.56.236  Mask:255.255.255.255          UP LOOPBACK RUNNING  MTU:16436  Metric:1[root@zbdba2 init.d]# ip a1: 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    inet 192.168.56.236/32 brd 192.168.56.236 scope global lo:0    inet6 ::1/128 scope host       valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000    link/ether 08:00:27:a9:80:da brd ff:ff:ff:ff:ff:ff    inet 192.168.56.231/24 brd 192.168.56.255 scope global eth0    inet6 fe80::a00:27ff:fea9:80da/64 scope link       valid_lft forever preferred_lft forever[root@zbdba2 init.d]# ifconfigeth0      Link encap:Ethernet  HWaddr 08:00:27:A9:80:DA           inet addr:192.168.56.231  Bcast:192.168.56.255  Mask:255.255.255.0          inet6 addr: fe80::a00:27ff:fea9:80da/64 Scope:Link          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1          RX packets:12155 errors:0 dropped:0 overruns:0 frame:0          TX packets:6832 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:1000          RX bytes:863609 (843.3 KiB)  TX bytes:626772 (612.0 KiB)lo        Link encap:Local Loopback           inet addr:127.0.0.1  Mask:255.0.0.0          inet6 addr: ::1/128 Scope:Host          UP LOOPBACK RUNNING  MTU:16436  Metric:1          RX packets:29 errors:0 dropped:0 overruns:0 frame:0          TX packets:29 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:0          RX bytes:2167 (2.1 KiB)  TX bytes:2167 (2.1 KiB)lo:0      Link encap:Local Loopback           inet addr:192.168.56.236  Mask:255.255.255.255          UP LOOPBACK RUNNING  MTU:16436  Metric:1

3、用戶端測試
mysql用戶端安裝不再詳訴

[root@ogg2 ~]# ifconfigeth0      Link encap:Ethernet  HWaddr 08:00:27:41:2D:0B           inet addr:192.168.56.13  Bcast:192.168.56.255  Mask:255.255.255.0          inet6 addr: fe80::a00:27ff:fe41:2d0b/64 Scope:Link          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1          RX packets:330 errors:0 dropped:0 overruns:0 frame:0          TX packets:380 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:1000          RX bytes:34424 (33.6 KiB)  TX bytes:32970 (32.1 KiB)lo        Link encap:Local Loopback           inet addr:127.0.0.1  Mask:255.0.0.0          inet6 addr: ::1/128 Scope:Host          UP LOOPBACK RUNNING  MTU:16436  Metric:1          RX packets:87 errors:0 dropped:0 overruns:0 frame:0          TX packets:87 errors:0 dropped:0 overruns:0 carrier:0          collisions:0 txqueuelen:0          RX bytes:7656 (7.4 KiB)  TX bytes:7656 (7.4 KiB)

在資料節點進行授權：

mysql> grant all on *.* to 'root'@'192.168.56.13' identified by 'mysql' with grant option;Query OK, 0 rows affected (0.00 sec)

串連mysql：

[root@ogg2 ~]# mysql -uroot -pmysql -h192.168.56.236Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3247Server version: 5.6.12-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>ok，至此配置成功 

4、宕機測試：
查看當前連的哪個節點：

mysql> show variables like 'log_error';+---------------+------------------------+| Variable_name | Value                  |+---------------+------------------------+| log_error     | /mysql/data/zbdba1.err |+---------------+------------------------+1 row in set (0.01 sec)

發現串連的是資料節點1，這時候我們關閉資料節點1的mysql

[root@zbdba1 init.d]# service mysql stopShutting down MySQL............                            [  OK  ]

在調度節點查看日誌：

[root@lvs ~]# tail -100f /var/log/messagesJan 27 02:58:24 lvs Keepalived_healthcheckers[2808]: TCP connection to [192.168.56.230]:3306 failed !!!Jan 27 02:58:24 lvs Keepalived_healthcheckers[2808]: Removing service [192.168.56.230]:3306 from VS [192.168.56.236]:3306

發現已經被移除
查看lvs

[root@lvs keepalived]# ipvsadmIP Virtual Server version 1.2.1 (size=4096)Prot LocalAddress:Port Scheduler Flags  -> RemoteAddress:Port           Forward Weight ActiveConn InActConnTCP  192.168.56.236:mysql wlc persistent 50  -> 192.168.56.231:mysql         Route   3      0          0   

再回到用戶端：

mysql> show variables like 'log_error';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    294Current database: *** NONE ***+---------------+------------------------+| Variable_name | Value                  |+---------------+------------------------+| log_error     | /mysql/data/zbdba2.err |+---------------+------------------------+1 row in set (10.02 sec)

發現此時串連重連，目前串連的是資料節點2。




至此我們已經達到我們單點故障的要求了。但是不僅僅限於此。我們利用lvs+keepalived 可以實現 從庫負載平衡，NDB叢集負載平衡。這裡只是介紹lvs+keepalived基本使用。有了這兩個工具再結合其他mysql其他架構，可實現高可用、高效能。