• notice
  • Congratulations on the launch of the Sought Tech site

Keepalived + Haproxy implementation of MYSQL highly available load balancing configuration

Technical otaku 2021-12-31
0 2784

 Keepalived

Because the mysql cluster is used in the production environment, it is necessary to achieve high-availability load balancing, here is the keepalived+ haproxy to achieve.

      The main function of keepalived is to realize fault isolation of real machines and failover between load balancers.It can be exchanged at layers 3, 4, and 5.It passes VRRPv2 (Virtual Router Redundancy Protocol) stack.

      Layer3: Keepalived will periodically send an ICMP packet (the Ping program we usually use) to the servers in the server farm.If it is found that the IP address of a certain service does not After activation, Keepalived reports that this server is invalid and removes it from the server farm.A typical example of this situation is that a certain server is shut down illegally.The Layer3 method is based on whether the server's IP address is valid as a standard for whether the server is working properly.

     Layer4: The status of the TCP port is mainly used to determine whether the server is working properly.For example, the service port of a web server is generally 80.If Keepalived detects that port 80 is not started, Keepalived will remove this server from the server group.

     Layer5: The bandwidth occupied on the network should also be larger.Keepalived will check whether the server program is running normally according to the user's setting.If it does not match the user's setting, Keepalived will remove the server from the server group.

Software Design

There will be a single process after keepalived is started

8352? Ss 0:00/usr/sbin/keepalived
8353? S 0:00 \_/usr/sbin/keepalived
8356? S 0:01 \_/usr/sbin/keepalived

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

Child process: VRRP child process

Child process: Healthchecking child process
Examples

2 sets of mysqlcluster 10.1.6.203 master  10.1.6.205 backup

vip 10.1.6.173

Destination visit 10.1.6.173 3366 port, polling and forwarding to 10.1.6.203 3306 and 10.1.6.205 3306 through haproxy respectively

The mysqlcluster build refers to the previous blog, here is to install keepalived on 2 machines

[email protected]:~# apt-get install keepalived
[email protected]:~# 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 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 {#Pay attention to the brace spaces
        chk_haproxy
    }
 
    notify_master/etc/keepalived/scripts/start_haproxy.sh #Indicates the script to be executed when switching to the master state
    notify_fault/etc/keepalived/scripts/stop_keepalived.sh #Script executed at the time of failure
    notify_stop/etc/keepalived/scripts/stop_haproxy.sh #Run the script specified by notify_stop before keepalived stops running}

VRRPD configuration includes three categories:

  • VRRP synchronization group (synchroization group)
  • VRRP Instance (VRRP Instance)
  • VRRP script

VRRP instance, VRRP script are used here

Pay attention to the configuration options:

stat: Specify the initial state of instance (Initial), that is, after the configuration, the initial state of the server is specified here, but the specified here is not counted, it still has to be determined by priority through elections.If it is set to master here, but if its priority is lower than the other one, then this one will send its own priority when sending the announcement, and the other one finds that the priority is not as high as its own, then he will go back to preempt For master

  • interface: the network card bound to the instance, because it must be added to the existing network card when configuring the virtual IP
  • priority 101: Set the priority of this node, the highest priority is master
  • debug: debug level
  • nopreempt: set to not preempt
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 The priority change caused by the script result: 2 means priority +2;-2 means priority-2
}

Then reference in the instance (vrrp_instance), a bit similar to the function reference in the script: define first, then quote the function name

 track_script {
        chk_haproxy
    }

Note: VRRP script (vrrp_script) and VRRP instance (vrrp_instance) belong to the same level

[email protected]:scripts# cat start_haproxy.sh
#!/bin/bash
 
sleep 5
get=`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.cfg
else
    echo "`date +%c` can not get vip" >>/etc/keepalived/scripts/start_ha.log
fi
[email protected]: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.log
else
  echo "`date +%c` will stop keepalived ">>/etc/keepalived/scripts/stop_keep.log
 /etc/init.d/keepalived stop
fi
 
/etc/init.d/keepalived stop
 
[email protected]:scripts# cat stop_haproxy.sh
#!/bin/bash
 
pid=`pidof haproxy`
echo "`date +%c` stop haproxy" >>/etc/keepalived/scripts/stop_ha.log
kill-9 $pid

Similarly configure 10.1.6.205

[email protected]:~# 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.sh
notify_fault/etc/keepalived/scripts/stop_keepalived.sh
notify_stop/etc/keepalived/scripts/stop_haproxy.sh
 
}

 HAProxy

Let’s introduce haproxy again

       HAProxy is a proxy software based on TCP (layer 4) and HTTP (layer 7) applications.It can also be used as a load balancer.It can support several Thousands of concurrent connections.At the same time, it can protect the server from being exposed to the network through port mapping.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.gz
tar xvfz/tmp/haproxy-1.4.22.tar.gz-C/tmp/
cd/tmp/haproxy-1.4.22
make TARGET=linux26
make install

      haproxy needs to perform a health check on each mysqlcluster server

1.Configure haproxy.cfg on 2 hosts separately

[email protected]:scripts# cat/etc/haproxy/haproxy.cfg
global
    maxconn 51200 #Default maximum number of connections
    #uid 99
    #gid 99
    daemon #Run haproxy in the background
    #quiet
    nbproc 1 #Number of processes (multiple processes can be set to improve performance)
    pidfile/etc/haproxy/haproxy.pid #haproxy's pid storage path, the user who starts the process must have permission to access this file
 
defaults
    mode tcp #Processed category (#7 layer http; 4 layer tcp)
    option redispatch #After the server corresponding to serverId hangs, it is forcibly directed to other healthy servers
    option abortonclose #When the server load is high, automatically terminate the connection that has been processed by the current queue for a long time
    timeout connect 5000s #Connection timeout
    timeout client 50000s #Client timeout
    timeout server 50000s #Server timeout
    log 127.0.0.1 local0 #error log record
    balance roundrobin #The default load balancing method, polling method
 
listen proxy
    bind 10.1.6.173:3366 #Listening port
    mode tcp #http's 7-layer mode
    option httpchk #File for heartbeat detection
    server db1 10.1.6.203:3306 weight 1 check port 9222 inter 12000 rise 3 fall 3 #server definition, check inter 12000 is to detect the heartbeat frequency.Rise 3 is 3 times that the server is correctly considered to be available, and fall 3 is 3 times that it fails and that the server is unavailable.weight represents weight
    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 #Website health check URL, used to check whether the website managed by HAProxy is available, normal return 200, abnormal return 503
    stats realm Haproxy Manager
    stats auth admin:p@a1SZs24 #Account password

[email protected]:~$ cat/etc/haproxy/haproxy.cfg
global
    maxconn 51200
    #uid 99
    #gid 99
    daemon
    #quiet
    nbproc 1
    pidfile/etc/haproxy/haproxy.pid
 
defaults
    mode tcp
    option redispatch
    option abortonclose
    timeout connect 5000s
    timeout client 50000s
    timeout server 50000s
    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

[email protected]:~# apt-get install xinetd

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

[email protected]:~# vim/etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk #Need to be defined in servive
{
    flags=REUSE
    socket_type=stream
    port=9222
    wait=no
    user=nobody
    server=/opt/mysqlchk
    log_on_failure +=USERID
    disable=no
    per_source=UNLIMITED
    bind=10.1.6.173
}
 
r[email protected]:~# vim/etc/services
mysqlchk 9222/tcp # mysqlchk

4.Write mysqlchk monitoring service script

[email protected]:~# ls-l/opt/mysqlchk
-rwxr--r--1 nobody root 1994 2013-09-17 11:27/opt/mysqlchk
[email protected]:~# 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/mysqld.sock"
MYSQL_USERNAME="mysqlchkusr" #The account password needs to be added in mysql
MYSQL_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"
 
preflight_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: 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
    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 "<html><body>MySQL is running.</body></html>\r\n"
  echo-e "\r\n"
  rm $ERR_FILE $TMP_FILE
  exit 0
}
return_fail()
{
  echo-e "HTTP/1.1 503 Service Unavailable\r\n"
  echo-e "Content-Type: text/html\r\n"
  echo-e "Content-Length: 42\r\n"
  echo-e "\r\n"
  echo-e "<html><body>MySQL is *down*.</body></html>\r\n"
  sed-e's/\n$/\r\n/' $ERR_FILE
  echo-e "\r\n"
  rm $ERR_FILE $TMP_FILE
  exit 1
}
preflight_check
if [-f "$FORCE_FAIL" ]; then
    echo "$FORCE_FAIL found" > $ERR_FILE
    return_fail;
fi
$MYSQL_BIN $MYSQL_OPTS--host=$MYSQL_HOST--socket=$MYSQL_SOCKET--user=$MYSQL_USERNAME--password=$MYSQL_PASSWORD-e "$CHECK_QUERY" > $TMP_FILE 2> $ERR_FILE
if [$?-ne 0 ]; then
    return_fail;
fi
return_ok;

Test

Enable keepalived on 2 nodes (the master node will get vip and automatically pull up haproxy), xinetd

[email protected]:~# ip add
1: 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 lo
2: 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 eth0
3: 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 eth1
4: 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:ff
5: 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:ff
[email protected]:~# netstat-tunlp | grep ha
tcp 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
[email protected]:~# netstat-tunlp | grep xine
tcp 0 0 10.1.6.203:9222 0.0.0.0:* LISTEN 30897/xinetd
[email protected]:~# ps-ef | grep haproxy
root 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 the account dave permission needs to add 3 ip10.1.6.203, 10.1.6.205, 10.1.6.173)

[email protected]:mgm# mysql-udave-p-h 10.1.6.173-P 3366
Enter password:
Welcome to the MySQL monitor.Commands end with; or \g.
Your MySQL connection id is 1344316
Server 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 make keepalive, haproxy, and the database hang up.vip10.1.6.173 will automatically float to 10.1.6.205 from above, and it does not affect the access of vip

Check the status of each node through vip, haproxy

http://10.1.6.173:8888/status

Tags

Technical otaku

Sought technology together

Related Topic

MySQL string intercept correlation function summary
0 2162 0
Solve the Tomcat under Linux to insert data Chinese garbled problem
0 2154 0

0 Comments

Leave a Reply

+