Mysql HA-Install Keepalived + LVS + Mysql (dual master) On Redhat 6.3, keepalivedlvs

Mysql HA-Install Keepalived + LVS + Mysql (dual master) On Redhat 6.3, keepalivedlvs

Host information:

Scheduling node: Name: lvsOS: Redhat 6.3IP: rule: 192.168.56.236 data node 1: Name: rule: Redhat 6.3IP: 192.168.56.230 data node 2: Name: zbdba2OS: Redhat 6.3IP: 192.168.56.231 client: name: ogg2OS: Redhat 6.3IP: 192.168.56.231

Description: Data Node 1 and Data Node 2 are mutually active and standby. The application accesses the scheduling node from the client to access the database. Any downtime of Node 1 and node 2 does not affect services
LVS:
The LVS cluster uses IP Server Load balancer technology and content-based request distribution technology. The scheduler has a good throughput rate, which transfers requests evenly to different servers for execution, and the scheduler automatically shields server faults, thus, a group of servers are formed into a high-performance, high-availability virtual server. The entire server cluster structure is transparent to customers, and there is no need to modify the client and server programs. To this end, system transparency, scalability, high availability and ease of management must be considered during design.
Multiple structures of a scalable network service require a front-end Server Load balancer (or multiple Server Load balancer instances for master-slave backup ). First, we analyzed the main technologies for implementing virtual network services, and pointed out that IP Server Load balancer technology is the most efficient in the Implementation Technology of the Server Load balancer. In the existing IP Server Load balancer technology, Network Address Translation is used to construct a group of servers into a high-performance, high-availability virtual server, we call it VS/NAT technology (Virtual Server via Network Address Translation ). Based on analyzing the disadvantages of VS/NAT and the asymmetry of network services, we propose a Virtual Server (VS/TUN) (Virtual Server via IP Tunneling) method through IP Tunneling ), and Virtual Server VS/DR (Virtual Server via Direct Routing) through Direct Routing, which can greatly improve system scalability. VS/NAT, VS/TUN, and VS/DR are three IP load balancing technologies implemented in The LVS cluster.


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.

Procedure:
1. Install lvs + keepalived
2. configure data nodes
3. Client Test
4. downtime Test

1. Install lvs + keepalived:
Operate on the scheduling Node
Install the dependency package:

yum install popt-devel openssl*yum install libn

Install ipvsadm:

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

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

Configure keepalived:
For more information about the parameters, see the official documentation:

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. configure data nodes:
Operate on Data Node 1 and Data Node 2


Mysql installation and dual-master configuration are not described


Configure lvs nodes

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

Start the data node:

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

Start the scheduling node:

service keepalived start

View logs:

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

View ip Address:

[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)

View the lvs node:

[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      

View data nodes:

[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. Client Test
Mysql client installation is not detailed

[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)

Authorize on the data node:

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

Connect to mysql:

[Root @ ogg2 ~] # Mysql-uroot-pmysql-h192.168.56.236Welcome 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,201 3, 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. Configuration successful now

4. downtime test:
View the connected node:

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

Data Node 1 is connected. At this time, we disable mysql of Data Node 1.

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

View logs on the scheduling node:

[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

Removed
View 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   

Return to the client:

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)

The connection is reconnected at this time. Currently, data node 2 is connected.




So far, we have met our single point of failure requirements. But this is not limited. We can use lvs + keepalived to achieve Load Balancing from the slave database and load balancing from the NDB cluster. Here we will only introduce the basic usage of lvs + keepalived. With these two tools combined with other mysql architectures, high availability and high performance can be achieved.