全部博文（389）
分类： Mysql/postgreSQL
2014-11-12 20:04:38
使用HAProxy路由MySQL读请求
对于mysql的读请求可以通过LVS,F5或是HAProxy软件来转发到后台的多个Mysql服务器上,从而实现了扩展的目标.
本文的例子使用HAProxy转发请求到两台mysql从库上.
Mysql:mysql 5.5
HAProxy IP: 172.28.10.192
Mysql Slave1:172.28.10.145
Mysql Slave2:172.28.10.150
在172.28.10.192上编译和安装haproxy
[root@cscscslocalhost 1109]# tar -zvfx haproxy-1.4.21.tar.gz
[root@cscscslocalhost haproxy-1.4.21]#make TARGET=linux26 PERFIX=/usr/local/haproxy
[root@cscscslocalhost haproxy-1.4.21]# make install PREFIX=/usr/local/haproxy
install -d /usr/local/haproxy/sbin
install haproxy /usr/local/haproxy/sbin
install -d /usr/local/haproxy/share/man/man1
install -m 644 doc/haproxy.1 /usr/local/haproxy/share/man/man1
install -d /usr/local/haproxy/doc/haproxy
for x in configuration architecture haproxy-en haproxy-fr; do \
install -m 644 doc/$x.txt /usr/local/haproxy/doc/haproxy ; \
--查看haproxy版本，如有返回表示安装成功
[root@cscscslocalhost haproxy-1.4.21]# haproxy -v
HA-Proxy version 1.4.21 2012/05/21
Copyright 2000-2012 Willy Tarreau <>
[root@cscscslocalhost haproxy]# cd /usr/local/haproxy
--在haproxy安装目录中，新建conf和logs目录
[root@cscscslocalhost haproxy]# mkdir conf
[root@cscscslocalhost haproxy]# mkdir logs
[root@cscscslocalhost haproxy]# ls
conf doc logs sbin share
--编写haproxy的配置文件，在haproxy的软件包中examples中有例子，可以复制过来再修改.
值得注意的有轮询方式，转发方式，每服务器的权重等信息。
[root@cscscslocalhost haproxy]# vi /etc/haproxy.cfg
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 4096
chroot /usr/share/haproxy
uid 99
gid 99
daemon
#debug
#quiet
defaults
log global
mode tcp
option httddplog
option dontlognull
retries 3
redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
listen mysql_proxy
bind 0.0.0.0:23306
mode tcp
balance roundrobin
server db1 172.28.10.145:3307 check inter 2000 rise 2 fall 5 weight 1
server db2 172.28.10.150:3306 check inter 2000 rise 2 fall 5 weight 1
--新建对应的chroot目录，否则会报错,haproxy不能正常启动
[root@cscscslocalhost haproxy]# haproxy -f /etc/haproxy.cfg
[ALERT] 314/142955 (12657) : [haproxy.main()] Cannot chroot(/usr/share/haproxy).
[root@cscscslocalhost haproxy]# mkdir /usr/share/haproxy
--启动haproxy,查看端口，确保启动成功.
[root@cscscslocalhost haproxy]# haproxy -f /etc/haproxy.cfg
[root@cscscslocalhost haproxy]# netstat -nltp | grep haproxy
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:23306 0.0.0.0:* LISTEN 12674/haproxy
--修改日志的相关配置，让haproxy能够输出日志信息
[root@cscscslocalhost log]# vi /etc/syslog.conf
local6.* /var/log/haproxy.log
[root@cscscslocalhost haproxy]# vi /etc/sysconfig/syslog
SYSLOGD_OPTIONS=" -r -m 0"
[root@cscscslocalhost haproxy]# service syslog restart
Shutting down kernel logger: [ OK ]
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]
Starting kernel logger: [ OK ]
--到此haproxy已经能够正常的转发mysql请求到后端的真正mysql服务器上了。
[root@cscscslocalhost haproxy]# mysql -u root -p123456 --host=127.0.0.1 --port=23306
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sports_sso |
| zabbix |
--使用sysbecn来测试一下单台服务器的处理能力和haproxy进行负载均衡后的处理能力.
先测试单机的处理能力
[root@localhost ~]# /usr/local/bin/sysbench --test=oltp --mysql-user=root --mysql-password=123456 --mysql-db=db1 --mysql-host=172.28.10.150 --mysql-port=3306 --oltp-table-size=500 --oltp-read-only=true --num-threads=32 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 32
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 612122
write: 120699
other: 53723
total: 786544
transactions: 10000 (56.75 per sec.)
deadlocks: 33723 (191.37 per sec.)
read/write requests: 732821 (4158.51 per sec.)
other operations: 53723 (304.86 per sec.)
Test execution summary:
total time: 176.2218s
total number of events: 10000
total time taken by event execution: 5632.1118
per-request statistics:
min: 12.98ms
avg: 563.21ms
max: 16905.54ms
approx. 95 percentile: 2218.47ms
测试haproxy转发后的处理能力.
[root@localhost ~]# /usr/local/bin/sysbench --test=oltp --mysql-user=root --mysql-password=123456 --mysql-db=db1 --mysql-host=172.28.10.192 --mysql-port=23306 --oltp-table-size=500 --oltp-read-only=true --num-threads=32 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 32
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 225316
write: 63519
other: 26094
total: 314929
transactions: 10000 (210.44 per sec.)
deadlocks: 6094 (128.24 per sec.)
read/write requests: 288835 (6078.33 per sec.)
other operations: 26094 (549.13 per sec.)
Test execution summary:
total time: 47.5188s
total number of events: 10000
total time taken by event execution: 1512.8555
per-request statistics:
min: 16.23ms
avg: 151.29ms
max: 2262.30ms
approx. 95 percentile: 465.13ms
Threads fairness:
events (avg/stddev): 312.5000/78.56
execution time (avg/stddev): 47.2767/0.18
通过结果对比测试，我们发现haproxy做前端的只读负载分发后，处理能力得到提高了.
