Mysql HA-Install Keepalived+LVS+Mysql（双主） On Redhat 6.3

浏览数：19 / 时间：2015年06月12日

主机信息：

调度节点：
Name：lvs
OS:Redhat 6.3
IP:192.168.56.235
VIP:192.168.56.236

数据节点1：
Name：zbdba1
OS:Redhat 6.3
IP:192.168.56.230

数据节点2：
Name：zbdba2
OS:Redhat 6.3
IP:192.168.56.231

客户端：
Name：ogg2
OS:Redhat 6.3
IP: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.conf
global_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 start
RealServer Start OK
[root@zbdba2 init.d]# ./realserver.sh start
RealServer Start OK

启动调度节点：

service keepalived start

查看日志：

tail -100f /var/log/messages
Jan 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=2808
Jan 27 02:48:26 lvs Keepalived[2806]: Starting VRRP child process, pid=2809
Jan 27 02:48:26 lvs Keepalived_vrrp[2809]: Interface queue is empty
Jan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Interface queue is empty
Jan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Netlink reflector reports IP 192.168.56.235 added
Jan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Netlink reflector reports IP fe80::a00:27ff:fe9e:856c added
Jan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Registering Kernel netlink reflector
Jan 27 02:48:26 lvs Keepalived_healthcheckers[2808]: Registering Kernel netlink command channel
Jan 27 02:48:26 lvs Keepalived_vrrp[2809]: Netlink reflector reports IP 192.168.56.235 added
Jan 27 02:48:26 lvs Keepalived_vrrp[2809]: Netlink reflector reports IP fe80::a00:27ff:fe9e:856c added
Jan 27 02:48:26 lvs Keepalived_vrrp[2809]: Registering Kernel netlink reflector
Jan 27 02:48:26 lvs Keepalived_vrrp[2809]: Registering Kernel netlink command channel
Jan 27 02:48:26 lvs Keepalived_vrrp[2809]: Registering gratuitous ARP shared channel
Jan 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 Bytes
Jan 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 Bytes
Jan 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]:3306
Jan 27 02:48:46 lvs Keepalived_healthcheckers[2808]: Activating healthchecker for service [192.168.56.231]:3306
Jan 27 02:48:47 lvs Keepalived_vrrp[2809]: VRRP_Instance(VI_MYSQL) Transition to MASTER STATE
Jan 27 02:48:48 lvs Keepalived_vrrp[2809]: VRRP_Instance(VI_MYSQL) Entering MASTER STATE
Jan 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.236
Jan 27 02:48:48 lvs Keepalived_vrrp[2809]: VRRP_Group(VGM) Syncing instances to MASTER state
Jan 27 02:48:48 lvs Keepalived_healthcheckers[2808]: Netlink reflector reports IP 192.168.56.236 added
Jan 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 a
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 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]# ifconfig
eth0      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:1

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: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]# ipvsadm
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  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 a
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
    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 forever
2: 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]# ifconfig
eth0      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 a
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
    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 forever
2: 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]# ifconfig
eth0      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 ~]# ifconfig
eth0      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.236
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3247
Server 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 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>

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 stop
Shutting down MySQL............                            [  OK  ]

在调度节点查看日志：

[root@lvs ~]# tail -100f /var/log/messages
Jan 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]# ipvsadm
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  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 away
No connection. Trying to reconnect...
Connection id:    294
Current database: *** NONE ***

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| log_error     | /mysql/data/zbdba2.err |
+---------------+------------------------+
1 row in set (10.02 sec)