MySQL中haproxy的搭建#Olivia丶长歌#

***********************************搭建haproxy***********************************
1,添加haproxy用户
useradd haproxy
2,在MARIADB GALERA CLUSTER集群中添加登录用户
mysql> grant all on *.* to root@'%' identified by '123456’;
刷新权限
mysql> flush privileges;
3,上传haproxy的软件和配置文件到/soft
ls
haproxy-1.4.20.tar.gz  haproxy.cfg
4，解压和创建数据目录、日志目录
tar -zxvf haproxy-1.4.20.tar.gz 
cd /usr/local/haproxy/
ls
mkdir conf
mkdir logs
cd /soft
cp -rf haproxy.cfg /usr/local/haproxy/conf/
4，编译安装
cd haproxy-1.4.20
make TARGET=linux26 PREFIX=/usr/local/haproxy ARCH=X86_64
make install PREFIX=/usr/local/haproxy
5,修改用户
chown -R haproxy.haproxy /usr/local/haproxy
6,修改配置文件（************星号是要修改的位置**************）
cd /usr/local/haproxy
vim haproxy.cfg
# this config needs haproxy-1.1.28 or haproxy-1.2.1
global
log 127.0.0.1 local0
maxconn 4096
log 127.0.0.1 local1 notice
#log loghost local0 info
#maxconn 4096
chroot /usr/local/haproxy
uid 501
gid 501
daemon
nbproc 1
pidfile /usr/local/haproxy/logs/haproxy.pid
#debug
#quiet
defaults
log global
#option dontlognull
retries 3
option redispatch
maxconn 4096
timeout http-keep-alive 10s
timeout check 10s
contimeout 5000
clitimeout 50000
srvtimeout 50000
listen admin_stats 192.168.100.12:48800   ******在哪个主机上搭的haproxy写哪个主机的IP***页面访问的端口号******
stats enable
stats hide-version
stats realm <realm>
stats refresh 5s
stats uri /haproxy-status  *********网页登录的后缀地址************
stats auth admin:admin     *******网页登录的账户和密码************
stats admin if TRUE
mode http
option httplog
timeout connect 50000
timeout check 50000
timeout client 300000
timeout server 300000
listen mysql_proxy 192.168.100.12:3310   *********haproxy的IP和端口号********************
mode tcp
balance roundrobin
option tcpka
option tcplog
server pxc01 192.168.100.12:3306 weight 1 check inter 2000 rise 2 fall 5  *********主服务器*********
server pxc02 192.168.100.13:3307 weight 1 check inter 2000 rise 2 fall 5  *********从服务器1*********
server pxc03 192.168.100.14:3308 weight 1 check inter 2000 rise 2 fall 5  *********从服务器2*********
7，启动haproxy
查看haproxy的命令
/usr/local/haproxy/sbin/haproxy ?f /usr/local/haproxy/conf/haproxy.cfg
启动
/usr/local/haproxy/sbin/haproxy -f  /usr/local/haproxy/conf/haproxy.cfg
8，查看haproxy进程
ps -ef|grep ha
查看haproxy
netstat -nltp|grep 3310
测试haproxy+pxc负载均衡方案
/usr/local/mysql/bin/mysql -h 192.168.100.12 -P 3306 -proot -e "select
@@hostname";
Warning: Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| oracle     |
+------------+
/usr/local/mysql/bin/mysql -h 192.168.100.13 -P 3308 -proot -e "select
@@hostname";
Warning: Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql      |
+------------+
此时haproxy可以轮训访问三个节点