How to use haproxy load balancing mysql

The content of this article is about how to use haproxy load balancing mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

The system is a one-master, multiple-slave model. The separation of reading and writing is implemented at the application level. Insert, delete, update, and alter go to the master library, and select goes to the slave library. Then I did load balancing for the slave library and used keepalived to combine haproxy.

Without further ado, let’s get started.

System environment: centos7

Having said that, I don’t know when the official community website of haproxy was blocked... Alas, you can download the latest stable version of haproxy through this address.

# wget https://www.haproxy.org/download/1.8/src/haproxy-1.8.9.tar.gz

It is recommended that friends read the readme file before installation

# cat /usr/src/haproxy-1.8.9/README

Install haproxy, the prefix keyword PREFIX here specifies the installation directory, use are capital letters. Generally, for regular source code installation, add the option "--prefix" after the configure command to distinguish it

# tar -zxf haproxy-1.8.9.tar.gz -C /usr/src/
# cd /usr/src/haproxy-1.8.9/
# make TARGET=linux2628 USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 PREFIX=/usr/local/haproxy
# make install  PREFIX=/usr/local/haproxy

Create haproxy program user and configuration file directory

# useradd -M -s /sbin/nologin haproxy
# mkdir /etc/haproxy

The haproxy configuration file does not provide a sample in this version of the package. It needs to be created manually.

# touch /etc/haproxy/haproxy.cfg
# more /etc/haproxy/haproxy.cfg
#global    log  /dev/log   local0 info    log  /dev/log 
  local1 notice    pidfile   
    /var/run/haproxy.pid    maxconn     400000  
      user        haproxy    group    
         haproxy    nbproc      1    daemon
#defaults    mode   http    log   global 
   option http-server-close    option            
         redispatch    retries          
      0    timeout http-request  
        10s    timeout queue     
              1m    timeout connect     
                  10s    timeout client      
                      1m    timeout server     
                           1m    timeout http-keep-alive  300s  
             timeout check           10s    maxconn    
                   100000
listen mysql_select  bind *:3306  mode tcp  balance roundrobin 
 #balance source  #option mysql-check user haproxy  timeout server  15s  
 timeout connect 15s  server mysql162 192.168.5.162:3306 check port 3306 inter 5000 fall 5 
  server mysql163 192.168.5.163:3306 check port 3306 inter 5000 fall 5  
  server mysql164 192.168.5.164:3306 check port 3306 inter 5000 fall 5
listen stats        mode http        bind 0.0.0.0:7979        stats enable            stats hide-version        stats uri /haproxy?hello        stats realm Haproxy\ Statistics        stats auth qkc:pwd@123        stats admin if TRUE

The default log of haproxy is It is output to the system's syslog, which is not very convenient to view. In order to better manage haproxy

logs, we generally define them independently in the production line. The definition method is as follows:

(1) Modify the log configuration options in haproxy.cfg, add the following configuration to global, and record the info and notice logs to different files.

log  /dev/log   local0 info
log  /dev/log   local1 notice

(2) Modify rsyslog configuration

In order to facilitate management, haproxy-related configurations are independently defined in /etc/rsyslog.d/haproxy.conf. This part of the configuration records the info and notice logs to the /var/log/haproxy181/{haproxy181-info.log,haproxy181-notice.log} files respectively, where "& ~" means that after the log is written to the log file, rsyslog Stop processing this message. The syntax configured here is written in rainerscript script language.

# vim /etc/rsyslog.d/haproxy.conf
if ($programname == 'haproxy' and $syslogseverity-text == 'info')
 then -/var/log/haproxy181/haproxy181-info.log& ~if 
 ($programname == 'haproxy' and $syslogseverity-text == 'notice') 
 then -/var/log/haproxy181/haproxy181-notice.log& ~

Check whether the syntax of haproxy.cfg configuration file is correct

# /usr/local/haproxy/sbin/haproxy -f /etc/haproxy/haproxy.cfg -c

If there is no problem, remove the -c option and start haproxy

# /usr/local/haproxy/sbin/haproxy -f /etc/haproxy/haproxy.cfg

[root@server181 haproxy]# ss -antulp | grep haproxy
tcp    LISTEN   0  128   *:3306     *:*    users:(("haproxy",pid=3315,fd=4))
tcp    LISTEN   0  128    *:7979   *:*    users:(("haproxy",pid=3315,fd=6))

Configure haproxy to start automatically at boot

# chmod +x /etc/rc.d/rc.local
# echo '/usr/local/haproxy/sbin/haproxy -f /etc/haproxy/haproxy.cfg' >> /etc/rc.d/rc.local

Connect haproxy for testing

# for i in $(seq 1 10); do mysql -utest -p123456 -h192.168.5.181 -e 'select @@server_id;'; done | egrep '[0-9]'
3306164
3306162
3306163
3306164
3306162
3306163
3306164
3306162
3306163
3306164

Then use another server to do haroxy and integrate keepalived. The remote system uses the mysql client to connect to the load balancing VIP to log in, and then perform health check, failover and other project tests one by one.

Summary:

haproxy has a very low CPU load on medium-sized loads, even in very high load scenarios, 5% user space occupancy and 95% system Space usage is also a very common phenomenon, which means that haproxy process consumption is more than 20 times lower than system space consumption. Therefore, performance tuning of the OS is very important. Even if the occupancy rate of user space is doubled, its CPU occupancy rate is only 10%, which also explains why layer 7 processing has limited impact on performance. As a result, haproxy's layer 7 performance can easily exceed hardware load balancing devices on high-end systems.

The feature of haproxy that supports tcp proxy allows it to also perform load balancing for mysql reading library, and it can perform quite well.

The above is the detailed content of How to use haproxy load balancing mysql. For more information, please follow other related articles on the PHP Chinese website!