1.前言
前文中我们介绍了HAProxy的安装布置(参考
http://blog.csdn.net/wjf870128/article/details/45221359)和Percona XtraDB Cluster的安装布置(
http://blog.csdn.net/wjf870128/article/details/45176011)。下面我们使用sysbench来检测下HAProxy是否能够实现负载均衡和对于集群中节点故障的请求转移。
2.sysbench安装
1)环境:
OS:redhat 6.5 32位
software:sysbench-0.4.8.tar.gz
ip addr:192.168.163.8(已经配置好haproxy)
2)解压缩软件包
[root@mysql-rep01 sysbench]# tar -zxvf sysbench-0.4.8.tar.gz
[root@mysql-rep01 sysbench]# cd sysbench-0.4.8
3)安装automake
[root@mysql-rep01 sysbench-0.5]# yum install automake
[root@mysql-rep01 Packages]# yum install libtool
[root@mysql-rep01 software]# rpm -ivh MySQL-devel-advanced-5.6.23-1.el6.i686.rpm
Preparing... ########################################### [100%]
1:MySQL-devel-advanced ########################################### [100%]
[root@mysql-rep01 software]# rpm -ivh MySQL-shared-advanced-5.6.23-1.el6.i686.rpm
[root@mysql-rep01 software]# rpm -ivh MySQL-shared-compat-advanced-5.6.23-1.el6.i686.rpm
6)安装sysbench软件包
[root@mysql-rep01 sysbench-0.5]# chmod u+x autogen.sh
[root@mysql-rep01 sysbench-0.5]# ./autogen.sh
[root@mysql-rep01 sysbench-0.5]# ./configure --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib/mysql/
[root@mysql-rep01 sysbench-0.5]# make
[root@mysql-rep01 sysbench-0.5]# make install
3.测试HAProxy分发
1)我们的haproxy的配置文件如下:
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
user nobody
group nobody
daemon
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
frontend pxc-front
bind *:3307
mode tcp
default_backend pxc-back
frontend stats-front
bind *:80
mode http
default_backend stats-back
frontend pxc-onenode-front
bind *:3306
mode tcp
default_backend pxc-onenode-back
backend pxc-back
mode tcp
balance roundrobin
option httpchk
server mysql-pxc01 192.168.163.6:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-pxc02 192.168.163.7:3306 check port 9200 inter 12000 rise 3 fall 3
backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth pxcstats:secret
backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server mysql-pxc01 192.168.163.6:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-pxc02 192.168.163.7:3306 check port 9200 inter 12000 rise 3 fall 3
2)在我们的percona集群中创建测试数据库和测试用户
mysql> create database sbtest;
Query OK, 1 row affected (0.39 sec)
mysql> grant all on sbtest.* to 'sbtest'@'%' identified by 'sbpass';
Query OK, 0 rows affected (0.25 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
3)创建测试数据
[root@mysql-rep01 db]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 prepare
sysbench 0.5: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
4)使用3306端口访问mysql数据库
[root@mysql-rep01 db]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 --num-threads=8 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored
Threads started!
登录haproxy的状态界面
http://192.168.163.8/haproxy/stats查看状态
分发成功
5)我们将主节点mysql-pxc01关闭掉在查看状态
[root@mysql-pxc01 ~]# service mysql stop
Shutting down MySQL (Percona XtraDB Cluster)..........[ OK ]
此时我们的sysbench报警退出...
ALERT: failed to execute MySQL query: `UPDATE sbtest1 SET c='48430239815-39636634349-44303135689-38090726026-22121476891-44178343736-66628055146-51151353374-76675391132-92944723344' WHERE id=5000`:
ALERT: Error 1317 Query execution was interrupted
FATAL: failed to execute function `event': (null)
ALERT: failed to execute MySQL query: `SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 3535 AND 3535+99 ORDER BY c`:
ALERT: Error 1047 Unknown command
FATAL: failed to execute function `event': (null)
ALERT: failed to execute MySQL query: `SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 5050 AND 5050+99 ORDER BY c`:
ALERT: Error 1047 Unknown command
FATAL: failed to execute function `event': (null)
ALERT: failed to execute MySQL query: `SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 5010 AND 5010+99 ORDER BY c`:
ALERT: Error 1047 Unknown command
FATAL: failed to execute function `event': (null)
再次执行的时候发现,haproxy识别到节点1关闭掉,状态如下
6)我们再次打开节点1,看看haproxy在leastconn的分发
由于我们是双节点的集群,节点1启动的时候需要抓取节点2的日志数据,备节点2不对外服务,所以连接为0。
同步完成后状态转化为:
7)节点正常后,由于我们使用的是leastconn,节点1上暂时没有连接,再次运行命令,创建5个线程连接3306端口
[root@mysql-rep01 ~]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 --num-threads=5 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 5
Random number generator seed is 0 and will be ignored
Threads started!
状态如下:
可见leastconn的算法生效。
8)使用3307端口访问mysql数据库查看
[root@mysql-rep01 db]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 --num-threads=8 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored
Threads started!
发现haproxy成功分发到各个server中
9)断开节点1
10)恢复节点1
已有的连接不会进行roundrobin循环分配
11)我们再次建立6个连接到3307端口
发现每个节点各自分配了3个新连接。
HAProxy能够实现节点的故障检测和根据相应算法进行负载均衡。