Keepalived + Lvs + Mysql master-master replication, keepalivedlvs

Keepalived + Lvs + Mysql master-master replication, keepalivedlvs

1 Introduction

Keepalived + lvs + mysql master/Master replication is a common Mysql high-availability solution. lvs provides read load balancing and Keepalived implements automatic failover through virtual vip drift, although Mysql is configured as master-master replication, it generally uses single-point writing to ensure data consistency.

The implementation of the entire solution is very simple. It only requires two servers, and the installation and configuration are relatively simple. However, you need to write a script to enable and disable the keepalived service, it is applicable to scenarios where there are only two database servers and no read/write splitting is implemented. The overall system architecture is as follows:

2. Environment Construction

2.1 environment Configuration:

Keepalived Server	LVS Server	LVS Realserver	MySQL Master-Master	Write VIP	Read VIP	OS	MySQL
Master 10.0.11.243	10.0.11.243	10.0.11.243	10.0.11.243	10.0.11.190	10.0.11.191	Centos6	Percona5.6
Backup 10.0.11.244	10.0.11.244	10.0.11.244	10.0.11.244	10.0.11.190	10.0.11.191	Centos6	Percona5.6

 

 

2.2 mysql master-master replication Configuration

The Master-master replication configuration is essentially to configure the master-slave relationship between the two servers. The implementation is relatively simple. The general steps are as follows:

2.2.1 create a duplicate account on the Master

Mysql> grantreplication slave on *. * to rep71@10.0.11.244identified by '123 ';

Mysql> flushprivileges;

 

2.2.2 modify the two mysql configuration files my. cnf as follows

# Replication General Config (both master and slave)

Servers-id = 101

Log-bin = mysql-log-bin

Auto-increment = 2

Auto-increment-offset = 1

Binlog_format = row

Relay_log = mysql-relay-bin

 

2.2.3 execute show Master status on the master server to record File and Position

Mysql> show master status \ G;

* *************************** 1. row ***************************

File: mysql-log-bin.000005

Position: 327933

Binlog_Do_DB:

Binlog_Ignore_DB: test, mysql, information_schema, cece_schema

2.2.4 run on the Slave Service

Mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql> change master
-> Master_host = '10. 0.11.243 ',
-> Master_user = 'rep71 ',
-> Master_password = '123 ',
-> Master_log_file = 'mysql-log-bin.000005 ',
-> Master_log_pos = 327933;
Query OK, 0 rows affected (0.01 sec)

Mysql> start slave;

2.2.5 repeat the above four steps to create the account rep72 and show master status on the Slave server and start slave on the Master server, so that the master synchronization of mysql is configured, run show slave status on both servers. If the results show Slave_IO_Running: Yes and Slave_ SQL _Running: Yes, the synchronization is normal.

 

2.3 install Keepalived

2.3.1 install on both the master and backup servers:

Wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz

Sudotar zxvf ipvsadm-1.24.tar.gz

Cdipvsadm-1.24

Sudoln-s/usr/src/kernels/2.6.32-358. el6.x86 _ 64 // usr/src/linux

Make

Sudomake install

 

2.4 install Keepalived

2.4.1 install on both the master and backup servers:

Wget http://www.keepalived.org/software/keepalived-1.1.19.tar.gz

Tarzxvf keepalived-1.1.19.tar.gz

Cdkeepalived-1.1.19

Yuminstall-y openssl-devel

Sudoyum install popt-devel-y

./Configure -- sysconf =/etc/-- with-kernel-dir =/usr/src/kernels/2.6.18-308. el5-x86_64/

Make

Sudomake install

Ln-s/usr/local/sbin/keepalived/sbin/

2.4.2 Keepalived configuration on the Master

Global_defs {

Router_id mysqlmha1

}

 

Vrrp_scriptcheck_run {

Script "/etc/keepalived/check_mysqll.sh"

Interval 1

}

 

 

Vrrp_instanceVI_1 {

State MASTER

Interface em1

Virtual_router_id 51

Priority100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

 

Track_script {

Check_run

}

Virtual_ipaddress {

10.0.11.190 dev em1 label em1: 1

10.0.11.191 dev em1 label em1: 2

}

}

 

Virtual_server10.0.11.190 3306 {

Delay_loop6

Lb_algorr

Lb_kindDR

Persistence_timeout20

ProtocolTCP

Sorry_server10.0.11.244 3306

Real_server10.0.11.243 3306 {

Weight3

TCP_CHECK {

Connect_timeout3

Nb_get_retry3

Delay_before_retry3

Connect_port3306

}

}

}

 

Virtual_server10.0.11.191 3306 {

Delay_loop6

Lb_algorr

Lb_kindDR

# Persistence_timeout20

ProtocolTCP

Real_server10.0.11.243 3306 {

Weight3

TCP_CHECK {

Connect_timeout3

Nb_get_retry3

Delay_before_retry3

Connect_port3306

}

}

Real_server10.0.11.244 3306 {

Weight3

TCP_CHECK {

Connect_timeout3

Nb_get_retry3

Delay_before_retry3

Connect_port3306

}

}

}

2.4.3 keepalived configuration of Backup

Global_defs {

Router_id mysqlmha1

}

 

Vrrp_script check_run {

Script "/etc/keepalived/check_mysqll.sh"

Interval 1

}

 

 

Vrrp_instance VI_1 {

State BACKUP

Interface em1

Virtual_router_id 51

Priority 90

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

 

Track_script {

Check_run

}

Virtual_ipaddress {

10.0.11.190 dev em1 label em1: 1

10.0.11.191 dev em1 label em1: 2

}

}

 

Virtual_server 10.0.11.190 3306 {

Delay_loop 6

Lb_algo rr

Lb_kind DR

Persistence_timeout 20

Protocol TCP

Sorry_server 10.0.11.244 3306

Real_server 10.0.11.243 3306 {

Weight 3

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

}

 

Virtual_server 10.0.11.191 3306 {

Delay_loop 6

Lb_algo rr

Lb_kind DR

# Persistence_timeout 20

Protocol TCP

Real_server 10.0.11.243 3306 {

Weight 3

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

Real_server 10.0.11.244 3306 {

Weight 3

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

}

2.5 configure the realserver of Master and backup

The configuration of the realserver master and backup are consistent. The script content is as follows:

#! /Bin/bash

# Description: Config realserver lo and apply noarp

SNS_VIP = 10.1.11.190

SNS_VIP2 = 10.0.11.191

/Etc/rc. d/init. d/functions

Case "$1" in

Start)

Ifconfiglo: 0 $ SNS_VIP netmask 255.255.255.255 broadcast $ SNS_VIP

Ifconfiglo: 1 $ SNS_VIP2 netmask 255.255.255.255 broadcast $ SNS_VIP2

/Sbin/routeadd-host $ SNS_VIP dev lo: 0

/Sbin/routeadd-host $ SNS_VIP2 dev lo: 1

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

Echo "1">/proc/sys/net/ipv4/conf/em1/arp_ignore

Echo "2">/proc/sys/net/ipv4/conf/em1/arp_announce

Sysctl-p>/dev/null 2> & 1

Echo "RealServer Start OK"

;;

Stop)

Ifconfiglo: 0 down

Ifconfiglo: 1 down

Routedel $ SNS_VIP>/dev/null 2> & 1

Routedel $ SNS_VIP2>/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 "0">/proc/sys/net/ipv4/conf/em1/arp_ignore

Echo "0">/proc/sys/net/ipv4/conf/em1/arp_announce

Echo "RealServer Stoped"

;;

*)

Echo "Usage: $0 {start | stop }"

Exit1

Esac

Exit0

 

2.6 edit the script to control enabling and stopping Keepalived

In general, as long as the mysql service is detected to stop keepalived, because keepalived tells the CIDR block that it is still alive by means of multicast. When the mysql service is stopped and keepalived is still running, stop keepalived to allow another host to obtain the virtual IP address, you can run this script in the background or add this script to the keepalived configuration file. Both the master and slave nodes need this script, but the database username and password are different.
[Root @ 244 ~] # More/etc/keepalived/check_mysql.sh
#20140722
#! /Bin/bash
MYSQL =/usr/bin/mysql
MYSQL_HOST = 10.0.11.243

MYSQL_USER = root
MYSQL_PASSWORD = mysql
CHECK_TIME = 3

# Mysql isworking MYSQL_ OK is 1, mysql down MYSQL_ OK is 0

MYSQL_ OK = 1
Function check_mysql_helth (){
$ MYSQL-h $ MYSQL_HOST-u $ MYSQL_USER-p $ MYSQL_PASSWORD-e "showstatus;">/dev/null 2> & 1
If [$? = 0]; then
MYSQL_ OK = 1
Else
MYSQL_ OK = 0
Fi
Return $ MYSQL_ OK
}
While [$ CHECK_TIME-ne 0]
Do
Let "CHECK_TIME-= 1"
Check_mysql_helth
If [$ MYSQL_ OK = 1]; then
CHECK_TIME = 0
Exit 0
Fi
If [$ MYSQL_ OK-eq 0] & [$ CHECK_TIME-eq 0]
Then
Pkill keepalived
Exit 1
Fi
Sleep 1
Done

This script requires the execution permission. Use the following command to add the execution permission.
# Chmod + x check_mysql.sh

2.7 test Keepalived automatic failover

Run sudo servicekeepalived start on the Master and slave, and run ip a on the master.

Run ip a on Slave

From the above we can see that the virtual IP address is successfully bound on masteMySQL, And the keepalived service on backup MySQL runs normally, but there is no virtual IP address (this is normal)

The client can connect to mysql through vip

Stop the mysql service on the master node. It can be found that the Keepalived service will also stop.

At this time, slave runs ip a and finds that it is bound to a virtual ip address, and mysql runs normally from the database, while the client can still connect to the database through vip.

Restart the master's mysql and Keepalived, and run start slave in the slave database. master runs ip a to find the service before recovery.

2.8 test the load balancing effect of lvs

After mysql of master and backup is started, run the following command on master and backup to start

Run keepalived and realserver scripts:

/Etc/rc. d/init. d/realserver. sh start

/Etc/rc. d/init. d/keepalived start

Run sudo ipvsadm-ln on the master and backup nodes. The following information is displayed:

When the client connects to mysql through read vip and executes the select statement several times, the inaction is changed, which proves that the load balancing is realized.