centos7环境配置haproxy实现mysql数据库代理
我们通常会碰到这样的业务场景:
b主机和c数据库在同一个内网,a主机不能直接访问c数据库,我们可以通过在b主机上搭建代理让a访问c数据库,我们使用haproxy来干这个事情
安装haproxy
yum install -y haproxy
配置haproxy:
vim /etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
stats socket /var/lib/haproxy/stats
defaults
log global
log 127.0.0.1 local3
mode http
option tcplog
option dontlognull
retries 10
option redispatch
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
listen mysql
bind 0.0.0.0:7306
mode tcp
balance roundrobin
server mysql1 192.168.3.18:3306
CentOS 7上yum安装的Haproxy,默认没有记录日志。需要做一下配置才能记录日志。
1.创建日志文件/var/log/haproxy/haproxy.log
cd /var/log
mkdir haproxy
cd haproxy
touch haproxy.log
chmod a+w haproxy.log
2.开启rsyslog的haproxy日志记录功能
编辑vim /etc/rsyslog.conf文件,将
$ModLoad imudp
$UDPServerRun 514
两行前的#去掉。
在
local7.* /var/log/boot.log
之后添加
# Save haproxy log
local2.* /var/log/haproxy/haproxy.log
修改vim /etc/sysconfig/rsyslog 文件,将
SYSLOGD_OPTIONS=""
改为
SYSLOGD_OPTIONS="-r -m 2 -c 2"
3.配置haproxy
修改/etc/haproxy/haproxy.cfg文件,在global区段添加
log 127.0.0.1 local2
重启rsyslog和haproxy服务,haproxy就能记录日志了。
systemctl restart rsyslog
systemctl restart haproxy
测试
报错:
Stopping haproxy: [ OK ]
Starting haproxy: [WARNING] 313/111853 (29445) : parsing [/etc/haproxy/haproxy.cfg:17] : 'option httplog' not usable with proxy 'mysql' (needs 'mode http'). Falling back to 'option tcplog'.
将haproxy.cfg的httplog改为tcplog即可
可能报错:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 50,158 milliseconds ago. The last packet sent successfully to the server was 50,157 milliseconds ago
参数可以适当调整:
timeout connect 500000
timeout client 500000
timeout server 500000
连接redis的配置,centos6.5
global
maxconn 2
# debug
quiet
user haproxy
group haproxy
nbproc 1
log 127.0.0.1 local3
defaults
timeout server 3s
timeout connect 3s
timeout client 60s
timeout http-request 3s
timeout queue 3s
frontend redis_read
bind 0.0.0.0:7736
default_backend cluster_redis
backend cluster_redis
mode tcp
option tcpka
balance static-rr
option httpchk
server redis_01 192.168.1.1:6379 weight 1
直接通过RedisDesktopManager工具连接redis即可