HAProxy是 七层代理 ，在使甠HAProxy后，在MySQL上 看不到Apps的源IP地址 ，看到的是HAProxy地址，而 MySQL的权限访问设置是和IP地址有关 ，这样就导致了MySQL无法 针对应用 进行区分权限了，所以使用的时候要注意。

1. HAProxy的安装

Shell> yum install haproxy

2. HAProxy的配置

2.1. haproxy.cfg

将以下配置文件保存为 /etc/haproxy/haproxy.cfg

[root@mysql3 haproxy]# cat /etc/haproxy/haproxy.cfg

# this config needs haproxy-1.1.28 or haproxy-1.2.1

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 http

#option httplog

option dontlognull

retries 3

option redispatch

maxconn 2000

contimeout 5000

clitimeout 50000

srvtimeout 50000

# 绑定的IP和端口

listen MySQL 10.100.25.42:3308

mode tcp

maxconn 200

# 负载均衡算法是 轮询

balance roundrobin

# 通过mysql连接去检测mysql是否可以访问

option mysql-check user haproxy_check

server mysql_1 10.100.25.40:3308 inter 1s rise 2 fall 2

server mysql_3 10.100.25.41:3307 inter 1s rise 2 fall 2

server mysql_3 10.100.25.41:3308 inter 1s rise 2 fall 2

# 自带的监控服务器的配置

listen admin_status

mode http

bind 0.0.0.0:8899

option httplog

log global

stats enable

stats refresh 10s

stats hide-version

stats realm Haproxy\ Statistics

stats uri /

# 监控的用户名和密码

stats auth myadmin:myadmin

stats admin if TRUE

2.2. 添加haproxy_check用户

将以下SQL语句在Master端执行，通过复制功能，传递到Slave上。

drop user haproxy_check@'XX';

create user haproxy_check@'XX';

grant usage on *.* to haproxy_check@'XX';

2.3. 配置日志

注意：该方法仅在 CentOS 6.X 上使用， CentOS 7.x 安装 HAProxy 后可甠 systemctl status haproxy 进行查看。

将以下文件保存为 /etc/rsyslog.d/49-haproxy.conf

# Create an additional socket in haproxy's chroot in order to allow logging via

# /dev/log to chroot'ed HAProxy processes

$AddUnixListenSocket /var/lib/haproxy/dev/log

$ModLoad imudp

$UDPServerRun 514

local3.* /var/log/haproxy.log

# Send HAProxy messages to a dedicated logfile

if $programname startswith 'haproxy' then /var/log/haproxy.log

&~

然后重启 rsyslog 服务

Shell> service rsyslog restart

Shutting down system logger: [ OK ]

Starting system logger: [ OK ]

3. 启动HAProxy

Shell> service haproxy start

[root@mysql3 ~]# netstat -tunlp | grep haproxy

tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 2583/haproxy

tcp 0 0 0.0.0.0:8899 0.0.0.0:* LISTEN 2583/haproxy

udp 0 0 0.0.0.0:33136 0.0.0.0:* 2583/haproxy

4. HAProxy测试

[root@mysql3 haproxy]# mysql -h 10.100.25.42 -P 3308 -uroot -pmysql -e "show variables like 'server_id'";

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 22 |

+---------------+-------+

[root@mysql3 haproxy]# mysql -h 10.100.25.42 -P 3308 -uroot -pmysql -e "show variables like 'server_id'";

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 33 |

+---------------+-------+

[root@mysql3 haproxy]# mysql -h 10.100.25.42 -P 3308 -uroot -pmysql -e "show variables like 'server_id'";

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 44 |

+---------------+-------+