Keepalived+haproxy to achieve Mysql high availability load Balancing configuration _mysql

Keepalived

Due to the use of mysqlcluster in the production environment, high availability load balancing is required, and keepalived+haproxy is provided here for implementation.

The main function of keepalived is to realize the fault isolation of the real machine and the failover between load balancer. Can be exchanged at the 3,4,5 layer. It is implemented through VRRPV2 (Virtual Router redundancy) stack.

Layer3:keepalived periodically to servers in the server farm. Send an ICMP packet (a ping program that we normally use), and if we find that the IP address of a service is not activated, Keepalived will report the server invalidation and remove it from the server farm. A typical example of this is the illegal shutdown of a server. The LAYER3 approach is to use the server's IP address as a standard for whether the server is working properly or not.

Layer4: Mainly in the TCP port state to determine whether the server is working properly or not. A Web server's service port is typically 80, and if Keepalived detects that 80 ports are not booting, keepalived will remove the server from the server farm.

LAYER5: The bandwidth used on the network is also larger. Keepalived will be based on user settings to check whether the server program is operating normally, if the user's settings do not match, then keepalived will remove the server from the server group.

Software Design

Keepalived starts with a single process

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.

Subprocess: VRRP Child process

Subprocess: healthchecking Child process
Instance

2 mysqlcluster 10.1.6.203 Master 10.1.6.205 Backup

VIP 10.1.6.173

Purpose to access 10.1.6.173 3366 ports are polled by Haproxy forward to 10.1.6.203 3306 and 10.1.6.205 3306

Mysqlcluster build reference before the blog, here on 2 machines installed keepalived

root@10.1.6.203:~# apt-get Install keepalived
root@10.1.6.203:~# cat/etc/keepalived/keepalived.conf 
Script Chk_haproxy {
    script "killall-0 haproxy"  # Verify the PID existance
    interval 2          # Check every 2 sec Onds
    weight-2          # Add 2 points of Prio if OK
}
 
vrrp_instance vi_1 {interface
    eth1        # interface T o Monitor state
    MASTER         
    virtual_router_id The     # Assign one ID for this route
    priority         Master, MB on backup
    nopreempt
    debug
 
    virtual_ipaddress {
        10.1.6.173
    }
 
    track_script {    #注意大括号空格
        chk_haproxy
    }
 
    notify_master/etc/keepalived/scripts/start_haproxy.sh # Represents the script to execute when switching to master State
    notify_fault/etc/keepalived/scripts/stop_keepalived.sh #故障时执行的脚本
    notify_stop  /etc/keepalived/scripts/stop_haproxy.sh #keepalived停止运行前运行notify_stop指定的脚本}

The VRRPD configuration consists of three classes:

• VRRP Sync Group (synchroization Group)
• VRRP instance (VRRP Instance)
• VRRP script

This uses the VRRP instance, the VRRP script

Note Configuration options:

Stat: Specifies the initial state of the instance (Initial), which means that the initial state of the server is specified here after it is configured, but it is not specified here, or it has to be determined through the campaign priority, if this is set to master, But if his priority is less than the other one, then this will send a notice in the send its own priority, the other one is not as high priority, then he will be back to preempt master

• Interface: Instance-bound NIC because it must be added on an existing network card when configuring the virtual IP
• Priority 101: Set priority for this node, high 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 script results in priority change: 2 indicates priority +2;-2 indicates priority-2
}

It is then referenced in the instance (vrrp_instance), somewhat similar to the function reference in the script: first defined, then reference function name

    Track_script {
        Chk_haproxy
    }

Note: The VRRP script (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 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 ' s Uccess to get VIP ">>/etc/keepalived/scripts/start_ha.log/usr/local/sbin/haproxy-f/etc/haproxy/haproxy.cfg E LSE echo "' Date +%c ' can not get VIP" >>/etc/keepalived/scripts/start_ha.log fi root@10.1.6.203:scripts# Cat St  op_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 ' would stop keepalived" >>/etc/keepalived/sc ripts/stop_keep.log/etc/init.d/keepalived Stop fi/etc/init.d/keepalived stop root@10.1.6.203:scripts# cat Stop_hap roxy.sh #!/bin/bash pid= ' pidof haproxy ' echo ' ' Date +%c ' Stop Haproxy ' >>/etc/keepalived/scripts/stop_ha.log L-9 $pid 

Empathy Configuration 10.1.6.205

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/     # Assign One ID for this route
  priority         ' on master, MB 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

Here is a brief introduction to the next Haproxy

Haproxy is a TCP (layer fourth) and HTTP (layer seventh) application of the agent software, it can also be used as a load balancer. Tens of thousands of concurrent connections can be supported. It also protects the server from being exposed to the network and through port mapping. It has a page that monitors 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 need to perform a health check on each mysqlcluster server

1. Configure HAPROXY.CFG on 2 hosts separately

root@10.1.6.203:scripts# cat/etc/haproxy/haproxy.cfg Global maxconn 51200 #默认最大连接数 #uid #gid Dae Mon #以后台形式运行haproxy #quiet nbproc 1 #进程数量 (you can set up multiple processes to improve performance) Pidfile/etc/haproxy/haproxy.pid #haproxy的pid存放路 , the user starting the process must have permission to access this file defaults mode TCP #所处理的类别 (#7层 http;4 layer tcp) Option Redispatch #serverId对应的服务器挂掉后, strongly developed To the other Healthy server Option Abortonclose #当服务器负载很高的时候, automatically end the current queue processing a relatively long connection timeout connect 5000s #连接超时 Timeout Client 50 
 
000s #客户端超时 Timeout Server 50000s #服务器超时 log 127.0.0.1 local0 #错误日志记录 balance roundrobin #默认的负载均衡的方式, polling mode Listen proxy bind 10.1.6.173:3366 #监听端口 mode TCP #http的7层模式 option HTTPCHK #心跳检测的文件 Server D B1 10.1.6.203:3306 weight 1 Check port 9222 Inter 12000 Rise 3 Fall 3 #服务器定义, check Inter 12000 is detecting heartbeat frequency rise 3 is 3 times correctly think the server is available , Fall 3 is 3 failures that the server is not available, weight represents the weight server DB2 10.1.6.205:3306 weight 1 Check port 9222 Inter 12000 rise 3 Fall 3 list En haproxy_stats MoDE http bind 10.1.6.173:8888 option httplog stats Refresh 5s stats uri/status #网站健康检测URL, used to detect Haproxy managed network Station is available, normal return 200, abnormal return 503 stats realm haproxy Manager stats auth admin:p@a1szs24 #账号密码 root@10.1.6.205:~$ cat/etc /haproxy/haproxy.cfg Global maxconn 51200 #uid #gid 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 man Ager Stats Auth AdmiN:p@a1szs24

 

2. Install xinetd

root@10.1.6.203:~# Apt-get Install xinetd

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

root@10.1.6.203:~# vim/etc/xinetd.d/mysqlchk 
# default:on
# description:mysqlchk
service Mysqlchk         #需要在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
}
 
root@10.1.6.203:~# vim/etc/services 
mysqlchk    9222/tcp            # Mysqlchk

4. Write Mysqlchk monitoring Service script

root@10.1.6.203:~# Ls-l/opt/mysqlchk-rwxr--r--1 Nobody root 1994 2013-09-17 11:27/opt/mysqlchk root@10.1.6.203:~# C At/opt/mysqlchk #!/bin/bash # This script checks if a MySQL server was healthy running on localhost. It'll # return: # ' http/1.x ok\r ' (if MySQL is running smoothly) #-OR-# "http/1.x Internal Server error\r" ( ELSE) # The purpose of this script are make haproxy capable of monitoring MySQL properly # mysql_host= "localhost" MySQL _socket= "/var/run/mysqld/mysqld.sock" mysql_username= "mysqlchkusr" #该账户密码需要在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 Ech O-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 ok\r\n "Echo-e" content-type:text/h tml\r\n "Echo-e" content-length:43\r\n "echo-e" \ r \ n "echo-e"  

Test

2 nodes to open keepalived (the main node will receive VIP, automatically pull up haproxy), xinetd

root@10.1.6.203:~# IP Add 1:lo: <LOOPBACK,UP,LOWER_UP> MTU 16436 qdisc noqueue State UNKNOWN Link/loopback 00:0 0: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 p Fifo_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 scopes 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 root@10.1.6.203:~# 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 root@10.1.6.203:~# NETSTAT-TUNLP | grep xine TCP 0 0 10.1.6.203:9222 0.0.0.0:* LISTEN 30897/xinetd root@10.1.6.203:~# 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 via vip10.1.6.173 3366 (note that the account Dave permissions need to add 3 ip10.1.6.203,10.1.6.205,10.1.6.173)

root@10.1.6.203: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       | 
| 
+--------------------+
3 rows in Set (0.01 sec)
 
mysql>

Manually separate the Keepalive,haproxy, the database hangs. vip10.1.6.173 will automatically drift to 10.1.6.205 from the top, does not affect the VIP access

View the status of each node through Vip,haproxy

Http://10.1.6.173:8888/status