haproxy+mysql集群如何實(shí)現(xiàn)負(fù)載均衡

haproxy+mysql集群如何實(shí)現(xiàn)負(fù)載均衡，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

HAProxy是 七層代理 ，在使甠HAProxy后，在MySQL上 看不到Apps的源IP地址 ，看到的是HAProxy地址，而 MySQL的權(quán)限訪(fǎng)問(wèn)設(shè)置是和IP地址有關(guān) ，這樣就導(dǎo)致了MySQL無(wú)法 針對(duì)應(yīng)用 進(jìn)行區(qū)分權(quán)限了，所以使用的時(shí)候要注意。

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
# 負(fù)載均衡算法是 輪詢(xún)
balance roundrobin
# 通過(guò)mysql連接去檢測(cè)mysql是否可以訪(fǎng)問(wèn)
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

# 自帶的監(jiān)控服務(wù)器的配置
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 /
# 監(jiān)控的用戶(hù)名和密碼
stats auth myadmin:myadmin 
stats admin if TRUE

2.2. 添加haproxy_check用戶(hù)
將以下SQL語(yǔ)句在Master端執(zhí)行，通過(guò)復(fù)制功能，傳遞到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 進(jìn)行查看。
將以下文件保存為 /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 服務(wù)
Shell> service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

3. 啟動(dòng)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測(cè)試

[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 |
+---------------+-------+

看完上述內(nèi)容，你們掌握haproxy+mysql集群如何實(shí)現(xiàn)負(fù)載均衡的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！