Mysql high-availability architecture solution II (keepalived + lvs + read/write splitting + Server Load balancer)

Mysql high-availability architecture solution II (keepalived + lvs + read/write splitting + Server Load balancer)
Mysql master-slave replication and lvs + keepalived for high load availability




Directory
1. Preface 4
2. Principle 4
2.1 Overview 4
2.2 Working Principle 4
2.3. Actual purpose 4
3 solution 4
3.1. Environment 4
3.2 architecture 5
3.3 Design Principle 6
4. Install related software 6
4. Configure mysql Master/Slave 7
5. Implement load and Hot Standby through lvs + keepalived, and implement read/write splitting 8



1. Preface
I recently studied highly available things. Here I will summarize the high-availability solutions for mysql master-slave replication and read/write separation, which can improve the server usage efficiency and maintenance efficiency. At the same time, the application efficiency will also be improved. If the application needs to modify the read IP address and the written IP address, it will be easy to transform.
2. Principles
2.1 Overview
If you divide TCP/IP into five layers, Keepalived is ~ Software with layer-5 switching mechanism, with 3 ~ The layer-5 switching function is mainly used to detect the status of the web server. If a web server fails, Keepalived will detect and remove it from the system, when the web server is working normally, Keepalived automatically adds it to the server group. All these tasks are completed automatically without manual intervention. You only need to manually repair the faulty web server.
2.2 Working Principle
Keepalived implements a high-availability solution based on VRRP to avoid spof. In this solution, at least two servers run Keepalived, that is, one of them is the MASTER, the other is BACKUP, but it acts as a virtual IP address. The MASTER will send a specific message to the BACKUP. When the BACKUP cannot receive the message, the MASTER is considered faulty, BACKUP takes over the virtual IP address and continues to provide services to ensure high availability. The layer-3 mechanism is to send ICMP packets to PING a server. If it does not hurt, it is considered faulty, and remove it from the server group. The layer-4 mechanism is to check the status of TCP port numbers to determine whether a server is faulty. If the server fails, it is removed from the server group. The layer-5 mechanism is to check whether a server application runs properly according to user settings. If the application is abnormal, remove it from the server group. 3,
2.3 practical functions
Keepalived + lvs is mainly used for RealServer health check and failover implementation between the MASTER and BACKUP of the Server Load balancer device.
3 Solution
In this case, the dual-host MASTER-Server Load balancer is used for high availability in two linux systems to achieve write separation and improve query performance). Data Replication and synchronization are implemented using semi-synchronization of MYSQL5.6.x, use keepalived to monitor MYSQL and provide read/write VIP floating. Keepalived is mainly used for RealServer health check and failover implementation between LoadBalance host and BackUP host.
When a host goes down, it does not affect the provision of external services (read/write vip addresses can fluctuate), ensuring high service availability.

3.1 Environment
Host A: 192.168.150.171
Host B: 192.168.150.172
W-VIP: 192.168.150.173 (write)
R-VIP: 192.168.150.174 (responsible for reading)
Client: any. You only need to access the above three IP addresses.

3.2 architecture Diagram
The architecture diagram is as follows:

3.3 design principles (Exceptions)
1. server A and server B use binlog to synchronize data through the mysql slave process.
2. enable two virtual IP addresses through keepalived: W-VIP/R-VIP, one responsible for writing, one responsible for reading, to achieve read/write separation.
3. When both A and B exist, the request is forwarded to host A under the W-VIP, And the R-VIP forwards the request to A and B to achieve load balancing.
4, when the host A exception, B takes over the service, W-VIP/R-VIP at this time drift to host B, at this time the two virtual IP is under the host B, to achieve high availability
5. When host B is abnormal, The R-VIP will kick B out, other unchanged

Effect after implementation
Normal

Prot LocalAddress:Port Scheduler Flags-> RemoteAddress:Port Forward Weight ActiveConn InActConnTCP 192.168.150.173:3306 wrr persistent 60-> 192.168.150.171:3306 Local 3 0 0TCP 192.168.150.174:3306 wrr persistent 60-> 192.168.150.172:3306 Route 3 0 0-> 192.168.150.171:3306 Local 1 0 0

Status of B after A fault

Prot LocalAddress:Port Scheduler Flags-> RemoteAddress:Port Forward Weight ActiveConn InActConnTCP 192.168.150.173:3306 wrr persistent 60-> 192.168.150.172:3306 Local 3 0 0TCP 192.168.150.174:3306 wrr persistent 60-> 192.168.150.172:3306 Local 3 0 0

Architecture Diagram

4. Install related software
1. mysql can be installed as needed, which is omitted here
2. Install lvs + keepalived

Associate kernel information required by kernel s of lvs and keepalived
Ln-s/usr/src/kernels/2.6.18-194. el5-x86_64 // usr/src/linux

Install lvs
Download: wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz
Tar-zxvf ipvsadm-1.24.tar.gz
Ipvsadm-1.24 for cd tar-zxvf
Make
Make install
Install yum install ipv *
Verify
Ipvsadm-v
Ipvsadm v1.24 2003/06/07 (compiled with getopt_long and IPVS v1.2.0) indicates that the installation is successful.

Install keepalived

tar –zxvf keepalived-1.2.12.tar.gzcd keepalived-1.2.12./configure --prefix=/usr/local/keepalived/makemake installln -s /usr/local/keepalived/etc/keepalived /etc/ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ln -s /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ln -s /usr/local/keepalived/bin/genhash /bin/ln -s /usr/local/keepalived/sbin/keepalived /sbin/

Note that yes is returned for Use of IPVS Framework, IPVS sync daemon support, and Use VRRP Framework during configure; otherwise, the ipvs function cannot be associated.

4. Configure mysql Master/Slave

Master (210.171) Configuration
Vi/etc/my. cnf

Add the following content:

Server-id = 1 # master IDbinlog-do-db = ppl # binlog-ignore-db = mysql # ignore the synchronized database, that is, the database that cannot be synchronized # enable log-bin in the configuration file, for example, log-bin = mysql-binmysql-uroot-p

Run the following command in mysql:

mysql> grant replication slave on *.* to ‘repdb01’@’%’ identified by '123456';mysql>create database db01;mysql>flush logs;mysql>show master status;mysql>use db01mysql> create table test(name char);

The following table is returned. Remember the File content and use it in slave configuration.


Slave Configuration
Vi/etc/my. cnf

Add the following content:

Server-id = 2 # slave IDmaster-host = 192.168.150.171 # specify the master Address master-user = repdb01 # account used for synchronization master-password = 123456 # used for synchronization password master-port = 3306 # mysql port on master replicate-do-db = db01 # Name of the database to be synchronized replicate-ignore-db = mysql # Name of the database to be synchronized slave- skip-errors = 1062 # When a synchronization exception occurs, errors are skipped. In this example, errors 1062 # log-slave-updates # Are synchronized, and your binlog is also recorded. If there is another slave that is synchronized through this machine, add this option, # skip-slave-start # do not enable the slave process automatically at startup # read-only # Set the database to read-only mode and only synchronize data from the master, mysql-uroot-p

Run the following command in mysql:

mysql>create database db01;mysql>change master to master_log_file=’mysql-bin.000007’,master_log=106;mysql>slave start;mysql>show slave status \G

Check the returned values. If the values of slave_IO_Runing and slave_ SQL _Runing are Yes, the synchronization is successful.


5. Implement load and Hot Standby through lvs + keepalived, and implement read/write splitting

Configure vi/etc/keepalived. conf on the Master! Configuration File for Route {router_id MySQL-HA} vrrp_instance VI_1 {state BACKUPinterface limit 90 priority limit 1policy_master "/usr/local/mysql/bin/volume" Limit {auth_type PASSauth_pass abcd1234} virtual_ipaddress {192.168.150.173 label eth0: 119425150.174 label eth0: 2} virtual_server 192.168.150.173 3306 {delay_loop 2lb_algo w Rrlb_kind DRpersistence_timeout 60 protocol TCPreal_server 192.168.150.171 3306 {weight 3notify_down/usr/local/mysql/bin/mysql. detail {connect_timeout 000000003connect_port 3306 }}} virtual_server 192.168.150.174 3306 {delay_loop 00000000000060 protocol TCPreal_server 192.168.150.171 3306 {weight 1policy_down/usr/local/mysql/bin/mysql. shT CP_CHECK {connect_timeout 255.%3connect_port 3306} real_server 192.168.150.172 3306 {weight 3TCP_CHECK {connect_timeout timeout %3connect_port 3306 }}} vi/usr/local/mysql/bin/remove_slave.sh #! /Bin/bashuser = rootpassword = 123456log =/root/mysqllog/remove_slave.log # define echo "'date'" >>$ log/usr/bin/mysql-u $ user-p $ password-e "set global read_only = OFF; reset master; stop slave; change master to master_host = 'localhost '; ">>$ log/bin/sed-I's # read-only #\# read-only # '/etc/my. cnfchomd 755/usr/local/mysql/bin/remove _ Slave. shvi/usr/local/mysql/bin/mysql. sh #! /Bin/bash/etc/init. d/keepalived stopSlave configuration vi/etc/keepalived. conf! Configuration File for Route {router_id MySQL-HA} vrrp_instance VI_1 {state BACKUPinterface limit 90 priority limit 1policy_master "/usr/local/mysql/bin/partition" authentication {auth_type PASSauth_pass ppl.com} virtual_ipaddress {192.168.150.173 label eth0: 119425150.174 label eth0: 2} virtual_server 192.168.150.173 3306 {delay_loop 2lb_algo wrrlb_kind Rpersistence_timeout 60 protocol TCPreal_server 192.168.150.172 3306 {weight 3notify_down/usr/local/mysql/bin/mysql. detail {connect_timeout 000000003connect_port 3306 }}} virtual_server 192.168.150.174 3306 {delay_loop 00000000000060 protocol TCPreal_server 192.168.150.172 3306 {weight 3policy_down/usr/local/mysql/bin/mysql. shTCP_CHECK {c Onnect_timeout extends 3connect_port 3306 }}# real_server 192.168.150.172 3306 {# weight 3 # TCP_CHECK {# connect_timeout 10 # nb_get_retry 3 # delay_before_retry 3 # connect_port 3306 #}#}} vi/usr/local /mysql/bin/remove_slave.sh #! /Bin/bashuser = rootpassword = 123456log =/root/mysqllog/remove_slave.log # define echo "'date'" >>$ log/usr/bin/mysql-u $ user-p $ password-e "set global read_only = OFF; reset master; stop slave; change master to master_host = 'localhost '; ">>$ log/bin/sed-I's # read-only #\# read-only # '/etc/my. cnfchomd 755/usr/local/mysql/bin/remove _ Slave. shvi/usr/local/mysql/bin/mysql. sh #! /Bin/bash/etc/init. d/keepalived stopvi/usr/local/keepalived/bin/lvs-rs.sh #! /Bin/bashWEB_VIP = 192.168.150.174. /etc/rc. d/init. d/functionscase "$1" instart) ifconfig lo: 0 $ WEB_VIP netmask 255.255.255.255 broadcast $ WEB_VIP/sbin/route add-host $ WEB_VIP dev lo: 0 echo "1">/proc/sys/net/ipv4/conf/lo/arp_ignoreecho "2">/proc/sys/net/ipv4/conf/lo/arp_announceecho "1 ">/proc/sys/net/ipv4/conf/all/arp_ignoreecho" 2 ">/proc/sys/net/ipv4/conf/all/arp_announcesysctl-p>/dev/ null 2> & 1 Echo "RealServer Start OK"; stop) ifconfig lo: 0 downroute del $ WEB_VIP>/dev/null 2> & 1 echo "0">/proc/sys/net/ipv4/conf/lo/arp_ignoreecho "0">/proc/ sys/net/ipv4/conf/lo/arp_announceecho "0">/proc/sys/net/ipv4/conf/all/arp_ignoreecho "0">/proc/sys/net/ ipv4/conf/all/arp_announceecho "RealServer Stoped ";; status) # Status of LVS-DR real server. islothere = '/sbin/ifconfig lo: 0 | grep $ WEB_VIP' isrothere = 'Netstat-rn | grep "lo: 0" | grep $ web_VIP 'if [! "$ Islothere"-o! "Isrothere"]; then # Either the route or the lo: 0 device # not found. echo "LVS-DR real server Stopped. LVS-DR Running. "fi; *) # Invalid entry. echo "$0: Usage: $0 {start | status | stop}" exit 1 ;; esacexit 0 chmod 755/usr/local/keepalived/bin/lvs-rs.shecho "/usr/local/keepalived/bin/lvs-rs.sh start">/etc/rc. local

Vi/etc/my. cnf
Remove # from the front of the two parameters and restart mysql.
# Skip-slave-start
# Read-only
Log on to mysql and manually start the slave process.
Mysql> slave start;

Start keepalived on the master, and then start the Server Load balancer instance.
After startup, the master database can view ip address

[Root @ rac3 ~] # Ip a1: lo: <LOOPBACK, UP, LOWER_UP> mtu 16436 qdisc noqueuelink/loopback 00: 00: 00: 00: 00 brd 00: 00: 00: 00: 00: 00 inet 127.0.0.1/8 scope host loinet6: 1/128 scope hostvalid_lft forever preferred_lft forever2: eth0: <BROADCAST, MULTICAST, UP, LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 link/ether 00: 50: 56: 95: 06: 1f brd ff: ffinet 192.168.150.171.171/24 brd 192.168.0.255 scope global eth0inet 192.168.150.173/32 scope global eth0: 1 inet 192.168.150.174/32 scope global eth0: 2inet6 fe80: 250: 56ff: fe95: 61f/64 scope users forever preferred_lft forever3: sit0: <NOARP> mtu 1480 qdisc nooplink/sit 0.0.0.0 brd workshop view [root @ rac1 keepalive] # ip a1: lo: <LOOPBACK, UP, LOWER_UP> mtu 16436 qdisc noqueuelink/loopback 00: 00: 00: 00: 00: 00 brd 00: 00: 00: 00: 00 inet 127.0.0.1/8 scope host loinet 192.168.150.174/32 brd 192.168.150.174 scope global lo: 0inet6: 1/128 scope used forever preferred_lft forever2: eth0: <BROADCAST, MULTICAST, UP, LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 link/ether 00: 50: 56: 95: 5e: b4 brd ff: ffinet logs/24 brd 192.168.0.255 scope global variables/24 brd 192.168.0.255 scope global secondary eth0: 1 inet logs/24 brd 192.168.0.255 scope global secondary eth0: 4inet6 fe80: 250: 56ff: fe95: 5eb4/64 scope used forever preferred_lft forever3: eth1: <BROADCAST, MULTICAST, UP, LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 link/ether 00: 50: 56: 95: 11: ba brd ff: ffinet 10.10.188/24 brd 10.10.10.255 scope global eth1inet release/16 brd 169.254.255.255 scope global eth1: 1inet6 fe80: 250: 56ff: fe95: 11ba/64 scope linkvalid_lft forever preferred_lft forever4: sit0: <NOARP> mtu 1480 qdisc nooplink/sit 0.0.0.0 brd 0.0.0.0

We can see that the vip address of 210.174 read is on the master and backup machines.
210.173 only the vip can be viewed on the master node.

Multiple labs are in progress. Please wait.