Haproxy+mysql implementing proxy MySQL

we typically run into a business scenario like this:
b host and C database in the same intranet, a host cannot directly access the C database, we can build the agent on the B host let a access C database, we use Haproxy to do this thing

CENTOS7 Environment

MySQL Self-installing

Haproxy installation Configuration

yum install-y haproxy

Configuration haproxy:
Vim/etc/haproxy/haproxy.cfg #内容如下

Global

# to has these messages end up In/var/log/haproxy.log you'll

# Need to:

#

# 1) Configure Syslog to accept network log events. This was done

# by adding the '-r ' option to the syslogd_options in

#/etc/sysconfig/syslog

#

# 2) Configure Local2 events to go to The/var/log/haproxy.log

# file. A line like the following can is added to

#/etc/sysconfig/syslog

#

# local2.*/var/log/haproxy.log

#

Log 127.0.0.1 Local2

Chroot/var/lib/haproxy

Pidfile/var/run/haproxy.pid

Maxconn 4000

User Haproxy

Group Haproxy

Daemon

# Turn on stats UNIX socket

Stats Socket/var/lib/haproxy/stats

Defaults

Mode http

Log Global

Option Httplog

Option Dontlognull

Option Http-server-close

Option Forwardfor except 127.0.0.0/8

Option Redispatch

Retries 3

Timeout Http-request 10s

Timeout Queue 1m

Timeout Connect 10s

Timeout Client 1m

Timeout Server 1m

Timeout Http-keep-alive 10s

Timeout Check 10s

Maxconn 3000

Listen MySQL

Bind 0.0.0.0:7306

Mode TCP

Balance Roundrobin

Server MYSQL1 192.168.10.130:3306

#server MYSQL2 192.168.10.132:3306

Listen stats

Bind 0.0.0.0:1080

Mode http

Option Httplog

Maxconn 10

Stats Refresh 30s

Stats Uri/stats

Stats Realm xingcloud\ Haproxy

Stats auth admin:admin #用这个账号登录, you can set your own

Stats Auth Frank:frank

Stats Hide-version

Stats Admin If TRUE

Haproxy installed on CentOS 7, the log is not logged by default. Need to be configured to log logs

1. Create a log file/var/log/haproxy/haproxy.log
Cd/var/log
mkdir haproxy
CD Haproxy
Touch Haproxy.log
chmod a+w Haproxy.log


2. Turn on the Rsyslog haproxy logging function


Edit the vim/etc/rsyslog.conf file to


$ModLoad imudp
$UDPServerRun 514

The # before two lines is removed.

in the
local7.*/var/log/boot.log
after adding


# Save Haproxy log
local2.*/var/log/haproxy/haproxy.log

Modify the Vim/etc/sysconfig/rsyslog file to
syslogd_options= ""  

syslogd_options= "-r-m 2-c 2"  

Restart the Rsyslog and Haproxy services, and Haproxy will be able to log the logs.
Systemctl Restart Rsyslog

Systemctl Restart Haproxy

Test

Mysql-h192.168.10.130-uhyh-p-p7306

Haproxy+mysql implementing proxy MySQL