Keepalived+lvs+mysql Primary master replication

A Brief introduction

Keepalived+lvs+mysql Primary master replication is a much more frequently used MySQL high-availability scheme, in which LVS provides read load Balancing, keepalived the virtual VIP drift to achieve self-failover, while MySQL is configured to master replication. However, the general use of single-point write to ensure data consistency.

The entire solution is easy to implement, only two servers can be installed, the installation configuration is relatively simple, but it is necessary to write their own scripts to enable the Keepalived service to open and close, for only two database server and not realize the read and write separation of the scene.

The overall system structure diagram for example

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy3vnx2ppyw5nmti2y29t/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma== /dissolve/70/gravity/center ">

Two environment construction

2.1 Environment configuration such as the following

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 primary master replication configuration is essentially the configuration of two servers with each other's master and slave, the implementation is relatively simple, the general process such as the following

2.2.1 Create a copy account on Master

mysql>grantreplication Slave on * * to [email protected] identified by ' 123456 ';

mysql>flushprivileges;

2.2.2 Change two MySQL configuration files my.cnf such as the following

# #Replication General Config (both master and slave)

Server-id= 101

log-bin= Mysql-log-bin

Auto-increment-increment= 2

Auto-increment-offset= 1

binlog_format= row

relay_log= Mysql-relay-bin

2.2.3 Run the show master status on Masterserver, recording the 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,performance_schema

2.2.4 running on the slave service

mysql> slave stop;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> Change Master to
Master_host= ' 10.0.11.243 ',
Master_user= ' rep71 ',
-master_password= ' 123456 ',
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. Create theaccount Rep72,show master status on the slave server. Start slave on master so that MySQL master synchronization is configured to perform show slave status on both servers, assuming the results are displayed Slave_io_running:yes,slave_sql_ Running:yes. It proves that the synchronization is normal.

Installation of 2.3 keepalived

2.3.1 is installed in both master and backup server:

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

Installation of 2.4 keepalived

2.4.1 is installed in both master and backup server:

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 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/

Configuration of the keepalived on 2.4.2 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

Priority 100

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

}

}

}

Configuration of the keepalived for 2.4.3 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

}

}

}

Configuration of the 2.5 Master and backup Realserver

For realserver configuration master and backup are consistent, the script contents such as the following:

#!/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 "$" 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: $ {start|stop}"

Exit1

Esac

Exit0

2.6 Editing scripts to control the opening and stopping of keepalived

The general meaning is only to detect the MySQL service stop keepalived Service also stop, because keepalived is through the multicast mode to tell the network segment itself is still alive when the MySQL service stopped keepalived still execute At this point need to stop keepalived Let there is a host to obtain a virtual IP, the ability to execute the script in the background can also add the script in the keepalived configuration file, both the master and slave nodes are required, Just the database username and password are different
[[Email protected] ~] #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-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

The script needs to have permission to run. Add running permissions with the following command
# chmod +x check_mysql.sh  

2.7 Test keepalived self-proactively fail-over

Master and the slave running on sudo servicekeepalived start , in Master running on IP a

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy3vnx2ppyw5nmti2y29t/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma== /dissolve/70/gravity/center ">

Slave running on IP a

can see from above Mastemysql on the virtual IP Bind succeeded, Backup MySQL on keepalived The service performs normally, but no virtual IP ( This belongs to normal )

Client can pass VIP Connect on MySQL

Stop Master on the MySQL services that can be found keepalived service will also stop

this time slave Execution IP a The discovery binds the virtual IP , and from the library MySQL execution is normal, and client can still pass VIP Connect to the database

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy3vnx2ppyw5nmti2y29t/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma== /dissolve/70/gravity/center ">

Restart Master of the MySQL and the keepalived later, and in slave Library Execution start Slave , Master Execution IP a Discover services prior to recovery

2.8 Test LVs the load Balancing effect

After starting master and backup MySQL, run the following command on master and backup, for example

Dynamic keepalived and Realserver scripts:

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

/etc/rc.d/init.d/keepalived start

Perform the sudo ipvsadm-ln on master and backup to see information such as the following

When the client connects to MySQL via the read VIP and runs several select, the inaction has changed. Proving that load balancing was achieved

Keepalived+lvs+mysql Primary master replication