Keepalived + HAProxy implements the configuration of MySQL high-availability load balancing, keepalivedhaproxy

Keepalived + HAProxy implements the configuration of MySQL high-availability load balancing, keepalivedhaproxy

Keepalived

Because mysqlcluster is used in the production environment, high-availability load balancing is required. keepalived + haproxy is provided here.

Keepalived is mainly used to implement fault isolation of real machines and failover between load balancers. It can be switched at Layer 3, 4, and 5. It is implemented through VRRPv2 (Virtual Router Redundancy Protocol) stack.

Layer3: Keepalived regularly sends messages to servers in the server group. send an ICMP packet (the Ping program we usually use). If the IP address of a service is not activated, Keepalived reports that the server is invalid and removes it from the server group, A typical example of this situation is that a server is illegally shut down. Layer3 is based on whether the IP address of the server is valid as the standard for whether the server works normally.

Layer4: the TCP port status determines whether the server works normally. For example, the Service port of the web server is usually 80. If Keepalived detects that port 80 is not started, Keepalived removes the server from the server group.

Layer5: The bandwidth used on the network is larger. Keepalived checks whether the server program runs properly according to the user's settings. If it does not match the user's settings, Keepalived removes the server from the server group.

Software Design

Keepalived starts a single process.

8352 ?    Ss   0:00 /usr/sbin/keepalived8353 ?    S   0:00 \_ /usr/sbin/keepalived8356 ?    S   0:01 \_ /usr/sbin/keepalived

Parent process: memory management, child process management, etc.

Sub-process: VRRP sub-process

Sub-process: Healthchecking sub-process
Instance

2 mysqlcluster 10.1.6.203 master 10.1.6.205 backup

Vip 10.1.6.173

Destination access port 10.1.6.173 3366 is forwarded to 10.1.6.203 3306 and 10.1.6.205 3306 respectively through haproxy

For more information about mysqlcluster setup, see the previous blog. Here, keepalived is installed on two machines.

Root@10.1.6.203 :~ # Apt-get install keepalivedroot@10.1.6.203 :~ # Cat/etc/keepalived. conf vrrp_script chk_haproxy {script "killall-0 haproxy" # verify the pid existance interval 2 # check every 2 seconds weight-2 # add 2 points of prio if OK} vrrp_instance VI_1 {interface eth1 # interface to monitor state MASTER virtual_router_id 51 # Assign one ID for this route priority 101 #101 on master, 100 on backup nopreempt debug virtual_ipaddress {10.1.6.173} track_script {# note the braces chk_haproxy} notify_master/etc/keepalived/scripts/start_haproxy.sh # indicates that when switching to the master status, script to be executed: policy_fault/etc/keepalived/scripts # script executed during fault: policy_stop/etc/keepalived/scripts/stop_haproxy.sh # keepalived run the script specified by policy_stop}

VRRPD configuration includes three types:

• VRRP synchronization group)
• VRRP Instance)
• VRRP script

VRRP instance and VRRP script are used here

Note:

Stat: Specifies the Initial state of the instance (Initial). After the instance is configured, the Initial state of the server is specified here, but not here, you still have to determine the priority through the election. If the priority is set to master here, but if the priority is lower than that of the other server, this server will send its own priority when sending the announcement, the other one finds that the priority is not as high as that of his own, so he will seize the master again.

• Interface: The network card bound to the instance, because the virtual IP address must be added to the existing network card
• Priority 101: set the priority of the current node. The priority is higher than that of the master node.
• Debug: debug level
• Nopreempt: set to not preemptible

Vrrp_script chk_haproxy {script "killall-0 haproxy" # verify the pid existance interval 2 # check every 2 seconds script execution interval weight-2 # add 2 points of prio if OK result priority change: 2 indicates priority + 2;-2 indicates priority-2}

Then reference it in the instance (vrrp_instance), which is similar to the function reference in the script: Define it first, and then reference the function name.

    track_script {        chk_haproxy    }

Note: VRRP scripts (vrrp_script) and VRRP instances (vrrp_instance) belong to the same level.

root@10.1.6.203:scripts# cat start_haproxy.sh #!/bin/bash sleep 5get=`ip addr |grep 10.1.6.173 |wc -l`echo $get >> /etc/keepalived/scripts/start_ha.log if [ $get -eq 1 ]then    echo "`date +%c` success to get vip" >> /etc/keepalived/scripts/start_ha.log    /usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfgelse    echo "`date +%c` can not get vip" >> /etc/keepalived/scripts/start_ha.logfiroot@10.1.6.203:scripts# cat stop_keepalived.sh #!/bin/bash pid=`pidof keepalived`if [ $pid == "" ]then  echo "`date +%c` no keepalived process id" >> /etc/keepalived/scripts/stop_keep.logelse  echo "`date +%c` will stop keepalived " >> /etc/keepalived/scripts/stop_keep.log  /etc/init.d/keepalived stopfi /etc/init.d/keepalived stop root@10.1.6.203:scripts# cat stop_haproxy.sh #!/bin/bash pid=`pidof haproxy`echo "`date +%c` stop haproxy" >> /etc/keepalived/scripts/stop_ha.logkill -9 $pid

Configure 10.1.6.205 in the same way

root@10.1.6.205:~# cat /etc/keepalived/keepalived.conf vrrp_script chk_haproxy {  script "killall -0 haproxy"  # verify the pid existance  interval 2          # check every 2 seconds  weight 2           # add 2 points of prio if OK} vrrp_instance VI_1 {  interface eth1        # interface to monitor  state BACKUP  virtual_router_id 51     # Assign one ID for this route  priority 100         # 101 on master, 100 on backup  virtual_ipaddress {    10.1.6.173  }   track_script {    chk_haproxy  } notify_master /etc/keepalived/scripts/start_haproxy.shnotify_fault /etc/keepalived/scripts/stop_keepalived.shnotify_stop /etc/keepalived/scripts/stop_haproxy.sh }

HAProxy

Next we will introduce haproxy

HAProxy is a proxy software based on TCP (Layer 4) and HTTP (Layer 7) applications. It can also be used as a server Load balancer. supports tens of thousands of concurrent connections. at the same time, it can protect the server from being exposed to the network through port ing. it also comes with a page to monitor the server status.

Install haproxy

wget -O/tmp/haproxy-1.4.22.tar.gz http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.22.tar.gztar xvfz /tmp/haproxy-1.4.22.tar.gz -C /tmp/cd /tmp/haproxy-1.4.22make TARGET=linux26make install

Haproxy needs to perform health check on every mysqlcluster Server

1. Configure haproxy. cfg on both hosts

Root@10.1.6.203: scripts # cat/etc/haproxy. cfg global maxconn 51200 # default maximum number of connections # uid 99 # gid 99 daemon # Run haproxy # quiet nbproc 1 # Number of processes in the future (you can set multiple processes to improve performance) pidfile/etc/haproxy. pid # The path where the haproxy pid is stored. the user who starts the process must have the permission to access the file defaults mode tcp # the class to be processed (# Layer 7 http; Layer 4 tcp) option redispatch # After the server corresponding to the serverId fails, it is forcibly directed to another healthy server option abortonclose # When the server load is high, automatically end the connection timeout connect 5000 s that has been processed by the current queue for a long time # connection timeout Timeout client 50000 s # client timeout server 50000 s # server timeout log 127.0.0.1 local0 # error log record balance roundrobin # default load balancing method, Poll listen proxy bind 10.1.6.173: 3366 # listening port mode tcp # http Layer 7 mode option httpchk # Heartbeat detection File server db1 10.1.6.203: 3306 weight 1 check port 9222 inter 12000 rise 3 fall 3 # server definition, check inter 12000 is the heartbeat detection frequency rise 3 is 3 times correctly think the server is available, fall 3 is 3 times failed think the server is unavailable, weight stands for weight server db2 10.1.6.205: 3306 weweigh T 1 check port 9222 inter 12000 rise 3 fall 3 listen haproxy_stats mode http bind 10.1.6.173: 8888 option httplog stats refresh 5S stats uri/status # website health check URL, used to detect HAProxy management site can be used, normal return 200, abnormal return 503 stats realm Haproxy Manager stats auth admin: p @ a1SZs24 # account password root@10.1.6.205 :~ $ Cat/etc/haproxy. cfg global maxconn 51200 # uid 99 # gid 99 daemon # quiet nbproc 1 pidfile/etc/haproxy. pid defaults mode tcp option redispatch option abortonclose timeout connect 5000 s timeout client 50000 s timeout server 50000 s log 127.0.0.1 local0 balance roundrobin listen proxy bind 10.1.6.173: 3366 mode tcp option httpchk server db1 10.1.6.203: 3306 weight 1 check port 9222 inter 12000 rise 3 fall 3 server db2 10.1.6.205: 3306 weight 1 check port 9222 inter 12000 rise 3 fall 3 listen haproxy_stats mode http bind 10.1.6.173: 8888 option httplog stats refresh 5S stats uri/status stats realm Haproxy Manager stats auth admin: p @ a1SZs24

2. Install xinetd

root@10.1.6.203:~# apt-get install xinetd

3. Add the xinetd service script and mysqlchk port number to each node.

Root@10.1.6.203 :~ # Vim/etc/xinetd. d/mysqlchk # default: on # description: mysqlchkservice mysqlchk # need to define {flags = REUSE socket_type = stream port = 9222 wait = no user = nobody server =/opt/mysqlchk restart + = USERID disable = no per_source = unsqllimitbind in servive = 10.1.6.173} root@10.1.6.203: ~ # Vim/etc/services MySQL chk 9222/tcp # mysqlchk

4. Compile the mysqlchk Monitoring Service script

Root@10.1.6.203 :~ # Ls-l/opt/mysqlchk-rwxr -- r -- 1 nobody root 1994/opt/mysqlchkroot@10.1.6.203 :~ # Cat/opt/mysqlchk #! /Bin/bash # This script checks if a mysql server is healthy running on localhost. it will # return: # "HTTP/1.x 200 OK \ r" (if mysql is running smoothly) #-OR-# "HTTP/1.x 500 Internal Server Error \ r" (else) # The purpose of this script is make haproxy capable of monitoring mysql properly # MYSQL_HOST = "localhost" MYSQL_SOCKET = "/var/run/mysqld. sock "MYSQL_USERNAME =" mysqlchkusr "# add MY SQL _PASSWORD = "secret" MYSQL_OPTS = "-N-q-A" TMP_FILE = "/dev/shm/mysqlchk. $. out "ERR_FILE ="/dev/shm/mysqlchk. $. err "FORCE_FAIL ="/dev/shm/proxyoff "MYSQL_BIN ="/opt/mysqlcluster/mysql-cluster-gpl-7.2.6-linux2.6-x86_64/bin/mysql "CHECK_QUERY =" select 1 "preghtfli_check () {for I in "$ TMP_FILE" "$ ERR_FILE"; do if [-f "$ I"]; then if [! -W $ I]; then echo-e "HTTP/1.1 503 Service Unavailable \ r \ n" echo-e "Content-Type: text/plain \ r \ n "echo-e" \ r \ n "echo-e" Cannot write to $ I \ r \ n "echo-e" \ r \ n" exit 1 fi done} return_ OK () {echo-e "HTTP/1.1 200 OK \ r \ n" echo-e "Content-Type: text/html \ r \ n" echo-e "Content-Length: 43 \ r \ n "echo-e" \ r \ n "echo-e" 
Test
Enable keepalived for two nodes (the master node will get the vip and automatically pull the haproxy), xinetd
root@10.1.6.203:~# ip add1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN   link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00  inet 127.0.0.1/8 scope host lo2: eth0: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast state DOWN qlen 1000  link/ether 00:26:b9:36:0f:81 brd ff:ff:ff:ff:ff:ff  inet 211.151.105.186/26 brd 211.151.105.191 scope global eth03: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000  link/ether 00:26:b9:36:0f:83 brd ff:ff:ff:ff:ff:ff  inet 10.1.6.203/24 brd 10.1.6.255 scope global eth1  inet 10.1.6.173/32 scope global eth14: eth2: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000  link/ether 00:26:b9:36:0f:85 brd ff:ff:ff:ff:ff:ff5: eth3: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000  link/ether 00:26:b9:36:0f:87 brd ff:ff:ff:ff:ff:ffroot@10.1.6.203:~# netstat -tunlp | grep hatcp    0   0 10.1.6.173:3366   0.0.0.0:*        LISTEN   1042/haproxy  tcp    0   0 10.1.6.203:8888   0.0.0.0:*        LISTEN   1042/haproxy  udp    0   0 0.0.0.0:56562      0.0.0.0:*              1042/haproxy  root@10.1.6.203:~# netstat -tunlp | grep xinetcp    0   0 10.1.6.203:9222   0.0.0.0:*        LISTEN   30897/xinetd  root@10.1.6.203:~# ps -ef | grep haproxyroot   1042   1 0 Sep17 ?    00:00:00 /usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfg
Test:
Access the cluster database through vip10.1.6.173 3366 (note that three ip10.1.6.203, 10.1.6.205, 10.1.6.173 must be added to the dave account)
root@10.1.6.203:mgm# mysql -udave -p -h 10.1.6.173 -P 3366Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1344316Server version: 5.5.22-ndb-7.2.6-gpl-log MySQL Cluster Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases;+--------------------+| Database      |+--------------------+| information_schema | | dave       | | test        | +--------------------+3 rows in set (0.01 sec) mysql>
Manually disable keepalive, haproxy, and disable vip10.1.6.173 from 10.1.6.205 without affecting vip access.
View the status of each node through vip and haproxy
Http: // 10.1.6.173: 8888/status


Articles you may be interested in:

 

 
  
Deployment and implementation of MySQL Server Clusters with load balancing Functions