A Brief introduction
Keepalived+lvs+mysql Primary master replication is a more common MySQL high-availability scenario where LVS provides read load balancing and keepalived failover via virtual VIP drift, while MySQL is configured as primary master replication, However, a single-point write is generally used to ensure data consistency.
The implementation of the whole scheme is very simple, only need two servers, the installation configuration is relatively simple, but need to write their own scripts to implement the Keepalived service open and close, for only two database servers and do not implement read-write separation of the scene. The overall structure of the system is as follows
Two environment construction
2.1 The environment is configured as follows
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 configuring the two servers to each other's master and slave, the implementation is relatively simple, the approximate steps are as follows
2.2.1 Create a copy account on Master
mysql>grantreplication Slave on * * to [email protected] identified by ' 123456 ';
mysql>flushprivileges;
2.2.2 Modify two MySQL configuration files my.cnf as follows
# #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 Perform the show master status on the master server, 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 performed on 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 to create the account Rep72,show master status on the slave server and start slave on master so that the master synchronization of MySQL is configured to run show on both servers Slave status, if the results are displayed slave_io_running:yes,slave_sql_running:yes, the synchronization is normal.
Installation of 2.3 keepalived
2.3.1 is installed 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
Installation of 2.4 keepalived
2.4.1 is installed 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 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 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 "$" 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 that as long as the MySQL service is detected to stop the keepalived service also stops, because keepalived is the multicast way to tell the network segment itself is still alive when the MySQL service stops keepalived still running At this point need to stop keepalived let another host to obtain the virtual IP, can run the script in the background can also be added to the keepalived configuration file This script, master and slave nodes are required, but the database user name 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
&nbs P Mysql_ok=1
Else
mysql_ok=0
fi
return $MYSQL _ ok
}
While [$CHECK _time-ne 0] ,
do
let "check_time-= 1"
& nbsp; check_mysql_helth
If [$MYSQL _OK = 1]; then
Check_ Time=0
exit 0
fi
If [$MYSQL _ok-eq 0] && [$CHECK _time-eq 0]&nbs P
then
pkill keepalived
Exit 1
fi
Sleep 1
Done
The script needs to have execute permissions, plus execute permissions with the following command
# chmod +x check_mysql.sh
2.7 Test keepalived Auto Fail-over
Master and the slave on the execution sudo servicekeepalived start , in Master on the execution IP a
Slave on the execution IP a
from the above can be seen Mastemysql on the virtual IP Bind succeeded, Backup MySQL on keepalived service is working, but no virtual IP ( This belongs to normal )
clients can pass VIP Connect on MySQL
Stop Master on the MySQL services that can be found keepalived service will also stop
this time slave Run IP a The discovery binds the virtual IP , and from the library MySQL and the client still works VIP Connect to the database
Restart Master of the MySQL and the keepalived later, and in slave Library Run start Slave , Master Run IP a Discover services prior to recovery
2.8 Test LVs the load Balancing effect
After starting master and backup MySQL, perform the following command on master and backup
Dynamic keepalived and Realserver scripts:
/etc/rc.d/init.d/realserver.sh start
/etc/rc.d/init.d/keepalived start
The following information can be seen on the sudo ipvsadm-ln running master and backup
When the client connects to MySQL via the read VIP and executes several select, the inaction has changed, proving that load balancing is achieved
Keepalived+lvs+mysql Primary master replication