MySQL multi-slave read load balancing using Haproxy

Haproxy provides high availability, load balancing, and proxies based on TCP and HTTP applications, supporting virtual hosting, which is a free, fast, and reliable solution. MySQL is a more common architecture, we can use Haproxy in the TCP layer to the database read request proxy, so as to achieve a plurality of load balancing from the library read.

At present, the company's business is a master many from, want to put the query to multiple from the library, are through the development and maintenance of multiple from the library configuration to achieve, so more trouble, through the development of Haproxy only need a configuration item, development regardless of the backend has how many from the library, and can be more from the library are used up, At the same time, the background db has a fault haproxy can be automatically removed, do not need to develop a database configuration as originally needed.

Environment Description:

haproxy:10.32.1.64

salve1:10.32.1.67

slave2:10.32.1.73

One, install Haproxy

Most Linux systems come with a Haproxy installation package, and we use the CentOS 6 system as an example to install the system's own package directly.

# yum install-y haproxy# haproxy-vha-proxy version 1.5.4 2014/09/02

Second, prepare the Haproxy configuration file

cd /etc/haproxyvim haproxy.cfg 
 

global        log 127.0.0.1 local2         chroot /var/lib/haproxy        pidfile  /var/run/haproxy.pid        maxconn 4000         user haproxy        group  haproxy        daemondefaults         mode tcp        log global         option httplog        option  dontlognull        retries 3         timeout http-request 10s        timeout  Queue 1m        timeout connect 10s         timeout client 1m        timeout server 1m         timeout http-keep-alive 10s         timeout check 10s        maxconn 3000 ##  defines a monitoring page, listens on port 1080, and enables the authentication mechanism listen stats        mode  http        bind 0.0.0.0:1080         stats enable        stats hide-version         stats uri /haproxyadmin?stats         stats realm Haproxy\ Statistics         stats auth admin:admin        stats admin if truefrontend  mysql        bind *:3306         mode tcp         #log  global         option tcplog        default_ Backend mysqlserversbackend mysqlservers        balance  leastconn        server dbsrv1 10.32.1.73:3306  check port 3306 rise 1 fall 2 maxconn 300         server dbsrv2 10.32.1.67:3306 check port 3306 rise  1 fall 2 maxconn 300

The following command can be used to detect if the configuration file is correct:

# haproxy-f/etc/haproxy/haproxy.cfg-c

The configuration file is valid indicates that there is no problem, if the profile is wrong, the detection will be prompted, according to the prompts to modify it.

Add Haproxy System Account:

# Useradd Haproxy

Three: Open Haproxy log

Set up a separate log directory for Haproxy

# Mkdir/var/log/haproxy

# chown Haproxy:haproxy/var/log/haproxy

Modify the Rsyslog.cfg file

# VIM/ETC/RSYSLOG.CNF

$ModLoad IMUDP

$UDPServerRun 514

Remove the # before the two lines.

After local7.*/var/log/boot.log, add the following:

# Save Haproxy Log

local2.*/var/log/haproxy/haproxy.log

Modify the Rsylog file

# Vim/etc/sysconfig/rsyslog

Syslogd_options= "" changed to Syslogd_options= "-r-m 2-c 2"

Restart log service for configuration to take effect

#/etc/init.d/rsyslog restart

Four, start Haproxy

# /etc/init.d/haproxy start# ps -ef | grep  haproxyhaproxy  12648     1  0 02:34 ?         00:00:04 /usr/sbin/haproxy -d -f /etc/haproxy/ Haproxy.cfg -p /var/run/haproxy.pid 

# netstat -tunlp | grep 12648tcp        0       0 0.0.0.0:1080                 0.0.0.0:*                    listen      12648/ haproxy       tcp        0       0 0.0.0.0:3306                 0.0.0.0:*                    listen      12648/haproxy        udp        0       0 0.0.0.0:60182               0.0.0.0:*                                 12648/haproxy

Five, Test load balancing

IP authorizations for Haproxy on slave1 and slave2 respectively:

> Grant all privileges on * * to [email protected] identified by ' 123456 ';> flush privileges;

For the convenience of testing, build a T1 table under the test library of Slave1, and slave2 the test library below to build the Test1 table. Executing the show tables command under the test library results in a different result, which means load balancing is valid.

salve1:10.32.1.67

>use test;database changed>show tables;+----------------+| Tables_in_test |+----------------+| T1 |+----------------+

slave2:10.32.1.73

>use test;database changed>show tables;+----------------+| Tables_in_test |+----------------+| Test1 |+----------------+

Test results:

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8D/AC/wKioL1imTM7TW7IuAABkTaNzUoM335.png-wh_500x0-wm_ 3-wmp_4-s_4195759617.png "title=" 2.png "alt=" Wkiol1imtm7tw7iuaabktanzuom335.png-wh_50 "/>

The results returned by multiple queries are alternating between T1 and Test1. The description query was distributed to SLAVE1 and Slave2 respectively, thus achieving load balancing.

Vi. Visit the Haproxy monitoring page

Because the monitoring page is configured in the configuration file, we can view the status of the back-end db through Http://10.32.1.64:1080/haproxyadmin?stats.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/8D/B0/wKiom1imYrLSJRpEAAG-J-o4CVg027.png-wh_500x0-wm_ 3-wmp_4-s_446413179.png "title=" 3.png "alt=" Wkiom1imyrlsjrpeaag-j-o4cvg027.png-wh_50 "/>

Seven, view Log

 # tail -f /var/log/haproxy/haproxy.logFeb 17 17:06:58 localhost  Haproxy[12648]: 10.32.1.64:50626 [17/feb/2017:17:06:58.959] mysql mysqlservers/dbsrv1  1/0/30 395 -- 0/0/0/0/0 0/0feb 17 17:07:00 localhost haproxy[ 12648]: 10.32.1.64:50629 [17/feb/2017:17:07:00.050] mysql mysqlservers/dbsrv2 1/0/5  392 -- 0/0/0/0/0 0/0Feb 17 17:07:01 localhost haproxy[12648]:  10.32.1.64:50632 [17/feb/2017:17:07:01.252] mysql mysqlservers/dbsrv1 1/0/12 395  -- 0/0/0/0/0 0/0Feb 17 17:07:02 localhost haproxy[12648]:  10.32.1.64:50635 [17/feb/2017:17:07:02.037] mysql mysqlservers/dbsrv2 1/0/9 392  -- 0/0/0/0/0 0/0feb 17 18:41:17 localhost haproxy[12648]: 10.32.1.227:8398  [17/feb/2017:18:41:17.489] stats stats/<stats> 3/0/0/0/4 200 17398 - - lr-- 3/3/0/0/0 0/0   "get /haproxyadmin?stats http/1.1" feb 17 18:41:17 localhost haproxy[12648] :  10.32.1.227:8398 [17/feb/2017:18:41:17.494] stats stats/<nosrv> 89/-1/-1/-1/ 89 503 212 - - lr-- 2/2/0/0/0 0/0  "get /favicon.ico http/1.1 "feb 17 18:41:19 localhost haproxy[12648]: 10.32.1.227:8399 [17/feb/ 2017:18:41:17.489] stats stats/<stats> 1568/0/0/0/1569 200 17399 - -  LR-- 3/3/0/0/0 0/0  "get /haproxyadmin?stats http/1.1" feb 17 18:41:19  localhost haproxy[12648]: 10.32.1.227:8399 [17/Feb/2017:18:41:19.058] stats  stats/<nosrv> 68/-1/-1/-1/68 503 212 - - lr-- 2/2/0/0/0 0/0   "Get /favicon.ico http/1.1 "feb 17 18:41:19 localhost haproxy[12648]: 10.32.1.227:8400 [17/feb/ 2017:18:41:17.489] stats stats/<stats> 2331/0/0/1/2333 200 17402 - -  LR-- 3/3/0/0/0 0/0  "get /haproxyadmin?stats http/1.1" feb 17 18:41:19  localhost haproxy[12648]: 10.32.1.227:8400 [17/Feb/2017:18:41:19.823] stats  stats/<nosrv> 60/-1/-1/-1/60 503 212 - - lr-- 2/2/0/0/0 0/0   "get /favicon.ico http/1.1"

You can see that each request is recorded in a log file.

Eight, failover test

To stop the slave1 MySQL service from simulating the crash, let's look at the logs:

Server Mysqlservers/dbsrv2 is down, reason:layer4 connection problem, info: "Connection refused", check duration:4ms. 1 active and 0 backup servers left. 0 Sessions Active, 0 requeued, 0 remaining in queue.

Haproxy has detected that the slave1 is not available, and now we come back through Haproxy forwarding queries, you can see Haproxy forwarded all the requests to slave2, the monitoring interface has been marked as down slave.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/8D/B1/wKiom1imcgXSYg4eAABmXuHtb9s875.png-wh_500x0-wm_ 3-wmp_4-s_212838257.png "title=" 4.png "alt=" Wkiom1imcgxsyg4eaabmxuhtb9s875.png-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8D/B1/wKiom1imcq7w5Fy-AABqk77BMXU873.png-wh_500x0-wm_ 3-wmp_4-s_3238117568.png "title=" 5.png "alt=" Wkiom1imcq7w5fy-aabqk77bmxu873.png-wh_50 "/>

After the slave1 MySQL service is set up, the SLAVE1 can serve the service normally:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8D/AE/wKioL1imdJfDNzeqAABl1Gi_DtI892.png-wh_500x0-wm_ 3-wmp_4-s_1516355396.png "title=" 6.png "alt=" Wkiol1imdjfdnzeqaabl1gi_dti892.png-wh_50 "/>

Attention:

1, if the log appears proxy Proxy-mysql has no server available! this error, you can first look at the back end of the MySQL service is normal, if normal, there may be a firewall rules there is a problem or selinux did not shut down caused.

2, the production environment if from the vault down to determine whether master-slave replication is wrong, replication delay is not big, confirm that no problem to provide services, otherwise the problem from the library from the configuration file removed.

3, the above configuration file Haproxy to the back end of MySQL detection is only the detection port is in, you can also use the option Mysql-check user Haproxy to detect whether MySQL can connect properly, because have encountered mysqld process is still in, But not even the situation. In this case, the detection user Haproxy needs to be authorized:

Use MySQL;

INSERT into User (Host,user) VALUES (' 10.32.1.64> ', ' haproxy ');

FLUSH privileges;

Reference:

http://www.ttlsa.com/linux/haproxy-study-tutorial/

http://www.haproxy.org/

This article is from the "Always on the Road" blog, please be sure to keep this source http://chenql.blog.51cto.com/8732050/1898713

MySQL multi-slave read load balancing using Haproxy