haproxy mysql 日志_使用HAProxy路由MySQL读请求

使用HAProxy路由MySQL读请求

对于mysql的读请求可以通过LVS,F5或是HAProxy软件来转发到后台的多个Mysql服务器上,从而实现了扩展的目标.

本文的例子使用HAProxy转发请求到两台mysql从库上.

Mysql:mysql 5.5

HAProxy IP: 192.168.10.192

Mysql Slave1:192.168.10.145

Mysql Slave2:192.168.10.150

在192.168.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 192.168.10.145:3307 check inter 2000 rise 2 fall 5 weight 1

server db2 192.168.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=192.168.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=192.168.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做前端的只读负载分发后，处理能力得到提高了.