MySQL primary master replication +lvs+keepalived for MySQL high availability

MySQL primary master replication +lvs+keepalived for MySQL high availability

MySQL replication can ensure the redundancy of the data and read and write separation to share the system pressure, if the primary master replication can also be good to avoid the primary node single point of failure. But there are some problems with MySQL master replication that do not meet our real needs: A unified access Portal is not provided for load balancing, and if Master is down, it needs to be manually switched to another master instead of automatically switching.

This article describes how to achieve high availability of MySQL by lvs+keepalived, while solving the above problems.

Introduction to Keepalived and LVS

Keepalived is a software solution based on VRRP (Virtual Routing Redundancy Protocol) that can be used to achieve high availability of services to avoid a single point of failure. Keepalived is generally used for lightweight high availability, and does not require shared storage, typically used between two nodes, common with lvs+keepalived, nginx+keepalived combinations.

LVS (Linux virtual Server) is a highly available virtual server cluster system. Founded in May 1998 by Dr. Zhangwensong, this project is one of the earliest free software projects in China.

LVS is mainly used for multi-server load balancing for the network layer. In a server cluster system built by LVS, the load balancing layer of the front end is called the director server, and the server group layer that serves the backend is called real server. Get an overview of the LVS infrastructure.

LVS has three modes of operation, namely Dr (direct Routing), TUN (tunneling IP Tunneling), NAT (network address translation). Where the Tun mode can support more real servers, but requires all server support IP Tunneling Protocol, Dr can also support the equivalent of real server, but need to ensure that the Director Server virtual network card and physical network card in the same network segment; Nat extensibility is limited, It is not possible to support more real servers because all request packages and reply packets require the Director Server to parse and regenerate, affecting efficiency. At the same time, LVS load balancer has 10 scheduling algorithms, namely RR, WRR, LC, WLC, LBLC, LBLCR, dh, sh, sed, NQ

For detailed LVS instructions, see Portal

In this paper, we will use LVS to realize MySQL read-write load balancing, keepalived Avoid single point of failure of nodes.

Lvs+keepalived Configuring the Environment preparation

lvs1:192.168.1.2

lvs2:192.168.1.11

MySQL server1:192.168.1.5

MySQL server2:192.168.1.6

vip:192.168.1.100

Os:centos 6.4

KeepAlive Installation

Keepalived

The following packages need to be installed

# yum install-y kernel-devel OpenSSL openssl-devel

Unzip keepalived to/usr/local/and go to directory to perform configuration compilation

--prefix=/usr/local/keepalived--with-kernel-dir=/usr/src/kernels/2.6.32-431.5.1.el6.x86_64/ keepalived configuration------------------------keepalived version       : 1.2.13Compiler                 : Gcccompiler Flags           :-g-o2extra Lib use                 IPVS Framework       sync use LIBNL           No fwmark Socket Support    : Yes with VRRP Framework       : Yes use VRRP VMAC            : Yessnmp Support             noSHA1 support             no use Debug flags          No Install 

By default, Keepalived starts by going to the/etc/keepalived directory to find the configuration file, copying the desired profile to the specified location

# cp/usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/ # cp/usr/local/keepalived/etc/sysconfig/keepalived/etc/sysconfig/ # cp/usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/ # cp/usr/local/keepalived/sbin/keepalived/usr/sbin/ # chkconfig Mysqld on # chkconfig keepalived on

LVS Installation

Ipvsadm

The following packages need to be installed

# yum Install-y libnl* popt*

To see if the LVS module is loaded

# modprobe-l |grep Ipvs

Unzip the installation

# ln-s/usr/src/kernels/2.6-431.5. 1. el6.x86_64//usr/src/linux # TAR-ZXVF ipvsadm-1.26. tar.gz # make && make install

LVS installation Complete, view current LVS cluster

# ipvsadm-l-N 1.2 . 1 (size=4096) Prot localaddress:port Scheduler flags-> remoteaddress:port           Forward Weight activeconn inactconn

lvs+keepalived configuration to build MySQL master master replication

Do not repeat here, please refer to MySQL replication

Configure keepalived

The following is the keepalived configuration on the LVS1 node (keepalived master node), LVS2 similar

# vim/etc/keepalived/keepalived.conf! Configuration File forkeepalivedglobal_defs {router_id lvs1}vrrp_instance vi_1 {State MASTER#指定instance初始状态, the actual decision is based on the priority. Backup node is differentInterface eth0#虚拟IP所在网virtual_router_idWuyi #VRID, the same vrid as a group that determines the multicast MAC addressPriority - #优先级, the other one is changed to a . Backup node is not the sameAdvert_int1  #检查间隔Authentication {Auth_type PASS#认证方式, can be pass or haAuth_pass1111  #认证密码} virtual_ipaddress {192.168. 1.  #VIP}}virtual_server192.168. 1. 3306{Delay_loop6  #服务轮询的时间间隔Lb_algo WRR#加权轮询调度, LVS scheduling algorithm Rr|wrr|lc|wlc|lblc|sh|shLb_kind DR#LVS集群模式 nat| dr| TUN, where the DR mode requires that the load Balancer NIC must have a piece of the same network segment as the physical NIC    #nat_mask 255.255. 255. 0 Persistence_timeout -  #会话保持时间Protocol TCP#健康检查协议     # # Real server settings,3306 is MySQL connection portReal_server192.168. 1. 5 3306{Weight3  # #权重Tcp_check {connect_timeout3Nb_get_retry3Delay_before_retry3Connect_port3306}} Real_server192.168. 1. 6 3306{Weight3Tcp_check {connect_timeout3Nb_get_retry3Delay_before_retry3Connect_port3306}    }}

Configuring LVS

Writing the LVs startup script/etc/init.d/realserver

#!/bin/shVIP=192.168.1. -. /etc/rc.d/init.d/functions Case "$" inch# Disable local ARP requests, bind the ground loopback addressStart)/sbin/ifconfig Lo down/sbin/ifconfig lo up 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/sbin/sysctl-p >/dev/null2>&1/sbin/ifconfig Lo:0$VIPNetmask255.255.255.255Up#在回环地址上绑定VIP, set the mask to maintain communication with the IP on the direct Server (itself)/sbin/route Add-host $VIPDev Lo:0Echo"LVS-DR Real server starts successfully.\n";; Stop)/sbin/ifconfig lo:0Down/sbin/route del $VIP>/dev/null2>&1Echo"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_announceecho"LVS-DR Real server stopped.\n";; Status) isloon= '/sbin/ifconfig lo:0| Grep"$VIP"' Isroon= '/bin/netstat-rn | Grep"$VIP"`if["$isLoON"==""-A"$isRoOn"==""]; Then Echo"LVS-DR Real server has run yet."    ElseEcho"LVS-DR Real server is running."Fi exit3;; *) echo"Usage: $ {start|stop|status}"Exit1Esacexit0

Adding LVS scripts to boot

# chmod +x/etc/init. D "/etc/init.d/realserver" >>/ETC/RC. d/RC. Local

Start LVs and keepalived separately

# service Realserver start # service keepalived start

Note that at this time the network card changes, you can see the virtual network card has been assigned to the Realserver.

At this time to view the LVS cluster status, you can see the cluster has two real servers, scheduling algorithms, weights and other information. Activeconn represents active connections for the current real server

# Ipvsadm-ln 1.2 . 1 (size=4096) Prot localaddress:port Scheduler Flags-  remoteaddress:port           Forward Weight activeconn inactconntcp  192.168 . 1 .:3306  192.168. 1. 5:3306             Route   3      4          1           192.168 . 1 . 6:3306             Route   3      0          2

The Lvs+keepalived+mysql master replication is now complete.

Test Validation Functional validation

Close MySQL Server2

# Service Mysqld Stop

In the LVS1 view/var/log/messages about keepalived log, LVS1 detected MySQL Server2 down, while the LVS cluster automatically rejected the fault node

Sep  9:192.168. 1. 2 keepalived_ healthcheckers[18797]: TCP connection to [192.168. 1. 6]:3306 Failed!!! Sep  9:192.168. 1. 2  keepalived_healthcheckers[18797]: removing service [192.168. 1. 6]: 3306 from VS [192.168. 1]:3306

Automatically join the failed node to the LVS cluster automatically after starting MySQL Server2 from new

Sep  9:192.168. 1. 2 Keepalived_ healthcheckers[18797]: TCP connection to [192.168. 1. 6]:3306 Success. Sep  9:192.168. 1. 2  keepalived_healthcheckers[18797]: Adding service [192.168. 1. 6]:  3306 to VS [192.168. 1]:3306

Turn off keepalived on LVS1 (simulate down operation), view the logs on LVS1, and see keepalived Remove VIP on LVS1

Sep9  -: on: - 192.168. 1. 2keepalived[18796]: Stopping keepalived v1. 2.( the/ the, the) Sep9  -: on: - 192.168. 1. 2keepalived_healthcheckers[18797]: Removing service [192.168. 1. 5]:3306From VS [192.168. 1.]:3306Sep9  -: on: - 192.168. 1. 2keepalived_healthcheckers[18797]: Removing service [192.168. 1. 6]:3306From VS [192.168. 1.]:3306Sep9  -: on: - 192.168. 1. 2keepalived_vrrp[18799]: Vrrp_instance (vi_1) sending0Prioritysep9  -: on: - 192.168. 1. 2keepalived_vrrp[18799]: vrrp_instance (vi_1) removing protocol VIPs.

While viewing the logs on the LVS2, you can see that LVS2 became master and took over the VIP

Sep9  -: One: - 192.168. 1. Onekeepalived_vrrp[7457]: Vrrp_instance (vi_1) Transition to MASTER Statesep9  -: One: - 192.168. 1. Onekeepalived_vrrp[7457]: Vrrp_instance (vi_1) Entering MASTER statesep9  -: One: - 192.168. 1. Onekeepalived_vrrp[7457]: vrrp_instance (vi_1) Setting protocol Vips.sep9  -: One: - 192.168. 1. Onekeepalived_vrrp[7457]: Vrrp_instance (vi_1) sending gratuitous ARPs on eth0 for 192.168. 1.Sep9  -: One: - 192.168. 1. Onekeepalived_healthcheckers[7456]: NetLink Reflector reports IP192.168. 1.Addedsep9  -: One: - 192.168. 1. Oneavahi-daemon[1407]: RegisteringNewAddress record for 192.168. 1.On eth0. Ipv4.sep9  -: One: - 192.168. 1. Onekeepalived_vrrp[7457]: Vrrp_instance (vi_1) sending gratuitous ARPs on eth0 for 192.168. 1.

Check the LVS cluster status on LVS2, everything is OK.

# Ipvsadm-ln 1.2 . 1 (size=4096) Prot localaddress:port Scheduler Flags-  remoteaddress:port           Forward Weight activeconn inactconntcp  192.168 . 1 .:3306  192.168. 1. 5:3306             Route   3      2          0           192.168 . 1 . 6:3306             Route   3      1          0

Summarize

• MySQL primary master replication is the base of the cluster, which makes up the server Array, where each node acts as a real server.
• The LVS server provides load balancing, distributing user requests to real server, and a real server failure does not affect the entire cluster.
• Keepalived build the main spare LVS server, avoid the single point of failure of LVS server, can automatically switch to the normal node when failure occurs.

MySQL primary master replication +lvs+keepalived for MySQL high availability