Keepalived+LVS+MySQL Dual master replication for read / write load balancing and high availability

Catalog

One 、Keepalived and LVS brief introduction

1. Keepalived brief introduction

2. LVS brief introduction

Two 、 Installation configuration

1. Download and install LVS

2. Download and install Keepalived

3. Keepalived To configure

4. To write RealServer Network configuration script for

3、 ... and 、 test

Four 、 summary

Reference resources ：

     We used Keepalived Of HA function , Realization MySQL Automatic failover of master-slave replication . It works by ： The initial will be MySQL Give different priority to the master and slave , When Keepalived Startup time , Will VIP Bind to high priority master library . stay Keepalived Invoking custom script middle note check_run, Check the machine every minute MySQL Server status of , If MySQL Unavailable , Then kill the machine keepalived process .Keepalived This machine will be checked every second keepalived process , If the process does not exist , Will VIP Bind to another machine , If this machine was originally from the library , Call at the same time master.sh The script performs the operation of switching from the library to the main library .

     In this article, we will do another experiment , utilize Keepalived Of IPVS function , call LVS Realization MySQL Read / write load balancing of dual master replication , At the same time, ensure the load balancer and MySQL High availability . The experimental environment is shown in the picture 1 Shown .

chart 1

One 、Keepalived and LVS brief introduction

1. Keepalived brief introduction
   See “ Use Keepalived Realization MySQL Master slave high availability ”.

2. LVS brief introduction
   LVS（Linux Virtual Server） It is a high availability virtual server cluster system . This project is in 1998 year 5 Founded by Dr. Zhang wensong , It is one of the earliest free software projects in China .LVS Mainly used for multi server load balancing , It works on the network layer .LVS Build a server cluster system , The load balancing layer of the front end is called Director Server, The server group layer that the back end provides services is called Real Server. You can get a general idea of LVS Infrastructure for .

chart 2

    LVS from ipvs and ipvsadm Two parts ：

ipvs：ipvs Working in kernel space netfilter Of input The frame on the chain , Manage through user space tools , Among them is the code that really takes effect to realize scheduling .
ipvsadm：ipvsadm Responsible for ipvs Kernel framework writing rules , Is in the management configuration kernel ipvs Program user space management tools .
LVS It's working in linux Kernel space tcp/ip Stack applications , The program name is ipvs.ipvs Will listen input Requests on the chain , Once the cluster service is requested ,ipvs The hook function will pull out the request and modify the message , Force forwarding to postrouting Handle , The relationship is as shown in the figure 3 Shown .

chart 3

     In the client's view ,LVS Is a real application server . Client to LVS Send request information ,LVS Receive data message to kernel space , Working in input On the chain ipvs The module will determine whether the user request is a defined back-end server , If the user requests the defined back-end cluster service , The data message is transmitted to input Chain time ,input The chain will forcibly forward the data message to postrouting,postrouting Send the data message to the back-end real server .LVS It is characterized by strong shunting function , But it can only be responsible for scheduling the direction of traffic , There is no way to divert the load at the business layer .

    LVS Can be used independently , But a more common approach is to work with Keepalived Use it together .LVS Provide load balancing ,Keepalived Provide health checks , Fail over , Improve system availability .Keepalived Medium LVS Configuration includes virtual host groups （Virtual Server Group） And virtual hosts （Virtual Server）. These configurations are passed to ipvsadm As a parameter . After adopting such an architecture , It's easy to extend existing systems , Add or reduce... At the back end realserver after , Just change Keepalived In the configuration file LVS Part can be .

Two 、 Installation configuration
Environmental Science ：
172.16.1.124：Keepalived + LVS Master
172.16.1.125：Keepalived + LVS Slave
172.16.1.126：MySQL Replication Master
172.16.1.127：MySQL Replication Master
172.16.1.100：VIP

     In this environment RealServer There are two MySQL The server ,LVS and RealServer Use two hosts respectively . When LVS In the architecture Director Server and RealServer When working on the same machine , We need to pay attention to SYN_RECV problem , There will be two director Infinite loop forwarding request . For a discussion of this problem, see “ How to make LVS and realserver Working on the same machine ”.

     stay 172.16.1.126 and 172.16.1.127 On the configuration MySQL Double master replication , The detailed steps are omitted . Compared with master-slave replication , For dual master replication, you need to pay attention to the following three parameter settings ：

log_slave_updates： To set up for true, Write the copy event to the local computer binlog. This option must be enabled when a server is both a master library and a slave library .
auto_increment_offset and auto_increment_increment： In order to avoid self addition conflict , These two parameters need to be set , For example, in dual master replication , It can be configured as follows ：

masterA Self growth ID

auto_increment_offset = 1
auto_increment_increment = 2 # Odd number ID

masterB Self increase ID

auto_increment_offset = 2
auto_increment_increment = 2 # even numbers ID

1. Download and install LVS
   stay 172.16.1.124 and 172.16.1.125 On the use of root The user executes the following command ：

yum -y install ipvsadm
2. Download and install Keepalived
stay 172.16.1.124 and 172.16.1.125 Installation on Keepalived, For detailed steps, see “ Use Keepalived Realization MySQL Master slave high availability ”.

3. Keepalived To configure
   172.16.1.124 For the initial keepalived Of master, On top of it keepalived The configuration file is as follows ：

[root@hdp1~]#more /etc/keepalived/keepalived.conf
global_defs {
router_id LVS_DEVEL
}

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
state BACKUP
interface ens32
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
172.16.1.100
}
}

virtual_server 172.16.1.100 3306 { # Define virtual servers , The address is the same as virtual_ipaddress identical
delay_loop 3 # Health check intervals ,3 second
lb_algo rr # Load balancing scheduling algorithm ：rr|wrr|lc|wlc|sh|dh|lblc
lb_kind DR # Load balancing forwarding rules ：NAT|DR|TUN
# persistence_timeout 5 # Session holding time 5 second , Dynamic service is suggested to be enabled
protocol TCP # Forwarding Protocol protocol, Generally speaking, there are tcp and udp Two kinds of

# Back end real server , Just set up a few 
real_server 172.16.1.126 3306 {
    weight 1			        #  The greater the weight, the greater the load ,0 Indicates failure 
    TCP_CHECK {
        connect_timeout 3
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
    }
}
real_server 172.16.1.127 3306 {
    weight 1
    TCP_CHECK {
        connect_timeout 3
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
    }
}

}

[root@hdp1~]#
172.16.1.125 For the initial keepalived Of slave, On top of it keepalived The configuration file is as follows ：

[root@hdp2~]#more /etc/keepalived/keepalived.conf
global_defs {
router_id LVS_DEVEL
}

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
state BACKUP
interface ens32
virtual_router_id 51
priority 90
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
172.16.1.100
}
}

virtual_server 172.16.1.100 3306 { # Define virtual servers , The address is the same as virtual_ipaddress identical
delay_loop 3 # Health check intervals ,3 second
lb_algo rr # Load balancing scheduling algorithm ：rr|wrr|lc|wlc|sh|dh|lblc
lb_kind DR # Load balancing forwarding rules ：NAT|DR|TUN
# persistence_timeout 5 # Session holding time 5 second , Dynamic service is suggested to be enabled
protocol TCP # Forwarding Protocol protocol, Generally speaking, there are tcp and udp Two kinds of

# Back end real server , Just set up a few 
real_server 172.16.1.126 3306 {
    weight 1			        #  The greater the weight, the greater the load ,0 Indicates failure 
    TCP_CHECK {
        connect_timeout 3
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
    }
}
real_server 172.16.1.127 3306 {
    weight 1
    TCP_CHECK {
        connect_timeout 3
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
    }
}

}

[root@hdp2~]#
master And slave Of keepalived There are only... In the configuration file priority Set different ,master by 100,slave by 90, Everything else is the same . Configuration files are organized in blocks , Every block is {} Within the confines of ,# and ！ The first lines are all comments .global_defs、vrrp_sync_group、vrrp_instance For the configuration description of this part, see “ Use Keepalived Realization MySQL Master slave high availability ”.

     There is no configuration in this example virtual_server_group. This configuration segment is optional , The purpose is to make one RealServer One of the Service Can belong to more than one Virtual Server, And only one health check . The following highlights virtual server Configuration of segments .

virtual_server： Set up a virtual server , Specify its virtual location IP And virtual ports .
delay_loop： Specify the service polling interval , The unit is seconds .
lb_algo： Appoint LVS Scheduling algorithm of , This example is specified as rr, I.e. polling . About LVS Description of the supported scheduling algorithm , See http://www.cnblogs.com/anay/p/9260306.html#_label7.
lb_kind： Appoint LVS Forwarding mode , This example is set to DR, This is also the configuration of most production environments . About LVS Description of the forwarding mode supported , See http://www.cnblogs.com/anay/p/9260306.html#_label3.
persistence_timeout： Specify the session duration in seconds , Notes are made here , Don't keep , The purpose is to make it easy to view the following tests rr The effect of load balancing algorithm . It is recommended to enable this parameter in the production environment .
protocol： Specifies the forwarding protocol to use TCP still UDP.
real_server： Each station RealServer You need one real_server Configuration item , Execute the of the real server IP And port . This example specifies two MySQL The server address and port of the library .
weight： Appoint RealServer The weight , The default is 1,0 For failure .
TCP_CHECK： The designated health examination method is TCP. Support HTTP_GET、SSL_GET、TCP_CHECK、SMTP_CHECK、MISC_CHECK、HTTP_GET、SSL_GET Other inspection methods , Each method contains its own parameters .
connect_timeout： Define the connection timeout , The unit is seconds .
nb_get_retry： Specify the number of reconnections .
delay_before_retry： Specify the time interval for reconnection , The unit is seconds .
connect_port： Specify the port for the health check .
5. To write RealServer Network configuration script for
stay 172.16.1.126 and 172.16.1.127 On the establishment of /etc/init.d/realserver file , The contents are as follows ：

#!/bin/sh
VIP=172.16.1.100
. /etc/rc.d/init.d/functions

case “$1” in

Disable local ARP request 、 Bind local loopback address

start)
/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/null 2>&1
/sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 up # Bind on loopback address VIP, Set the mask , And Direct Server On their own IP Maintain communication
/sbin/route add -host $VIP dev lo:0
echo “LVS-DR real server starts successfully.\n”
;;
stop)
/sbin/ifconfig lo:0 down
/sbin/route del KaTeX parse error: Expected 'EOF', got '&' at position 18: …P >/dev/null 2>&̲1 echo "1" …VIP"isRoOn=/bin/netstat -rn | grep “$VIP"‘if["$isLoON” == “” -a “$isRoOn” == “” ]; then
echo “LVS-DR real server has run yet.”
else
echo “LVS-DR real server is running.”
fi
exit 3
;;
*)
echo “Usage: $0 {start|stop|status}”
exit 1
esac
exit 0
Execute the following command to add the script to the startup self startup ：

chmod +x /etc/init.d/realserver
echo “/etc/init.d/realserver” >> /etc/rc.d/rc.local
Execute the following command to configure realserver：

service realserver start
After the execution of the command 172.16.1.126 and 172.16.1.127 Upper IP The addresses are shown in the figure 4、5 Shown .

chart 4

chart 5

3、 ... and 、 test

1. Respectively in master Shanghe slave Start the keepalived process .
   stay 172.16.1.124 and 172.16.1.125 Execute the following command on ：

/etc/init.d/keepalived start
2. see master and slave Upper VIP
The results are as follows 6、7 Shown , You can see VIP Successfully bound to 172.16.1.124.

chart 6

chart 7

     At this point to see LVS State of the cluster , You can see that there are two RealServer, Scheduling algorithm , Weight and other information .ActiveConn On behalf of the current RealServer The number of active connections .

[root@hdp1~]#ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 172.16.1.100:3306 rr
-> 172.16.1.126:3306 Route 1 0 0
-> 172.16.1.127:3306 Route 1 0 0
[root@hdp1~]#
3. verification LVS Load balancing forwarding strategy
MySQL Client side usage VIP Connect to database , And view the connected database server ID. You can see , Each execution is connected to 172.16.1.126 and 172.16.1.127 Of MySQL, Proved to be the result of the polling strategy .

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 126 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 126 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+
4. simulation LVS Of master invalid
stop it master Upper keepalived service , stay 172.16.1.124 Execute the following command on ：

/etc/init.d/keepalived stop
Look again 172.16.1.124、172.16.1.125 The binding of VIP Separately shown 8、9 Shown . You can see VIP Has drifted to 172.16.1.125 On , It has become a new master.

chart 8

chart 9

     Now connect MySQL, Load balancing is not affected .

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 126 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 126 |
±--------------±------+
Start again at this time 172.16.1.124 On keepalived service , It has become slave, And I'm not going to grab master, This is from nopreempt Parameters determine .

5. simulation mysqld crash
   stay 172.16.1.126 Execute the following command on ：

pkill -9 mysqld
here ,LVS Detected. 172.16.1.126 Upper MySQL Server Downtime , The cluster automatically eliminates the faulty nodes . At this time, there is only one in the cluster RealServer The address of , namely 172.16.1.127:3306.

[root@hdp1~]#ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 172.16.1.100:3306 rr
-> 172.16.1.127:3306 Route 1 0 0
[root@hdp1~]#
Now connect MySQL, You can see that the application is not affected , But only connected to one MySQL The server .

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+

C:\WINDOWS\system32>mysql -uwxy -p123456 -h172.16.1.100 -e “show variables like ‘server_id’”
mysql: [Warning] Using a password on the command line interface can be insecure.
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 127 |
±--------------±------+
Restart 172.16.1.126 Upper MySQL after ,LVS Automatically join the failed node into the cluster .

[root@hdp1~]#ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 172.16.1.100:3306 rr
-> 172.16.1.126:3306 Route 1 0 0
-> 172.16.1.127:3306 Route 1 0 0
[root@hdp1~]#
Four 、 summary
Why use MySQL Double master replication instead of master-slave replication , This is because this scheme does not involve read-write separation , But in two equivalent MySQL Load balancing between servers .
In this case, in addition to the simple configuration , There are no custom scripts .
Keepalived utilize VRRP Realized LVS Of HA, Avoided LVS Single point of server failure , In case of failure, it can automatically switch to the normal node .
LVS The server provides load balancing , Distribute user requests to multiple RealServer. meanwhile , a RealServer Failure does not affect the entire cluster , because LVS Will detect RealServer The state of , And automatically add or delete RealServer service .
As in this example , Need to consider a single MySQL The load of the server should not exceed 50%, Otherwise, once one MySQL Server failure , There may be another normal MySQL An overburdened situation .
Reference resources ：
MySQL Master master copy +LVS+Keepalived Realization MySQL High availability
LVS NAT,DR,TUN Three load principles
Keepalived Authoritative guide