haproxy实现MySQL服务器负载均衡

 1.环境准备

准备好下面四台台服务器：

主机名	IP	角色
open-Euler1	192.168.121.150	mysql-server1
openEuler-2	192.168.121.151	mysql-server2
openEuler-3	192.168.121.152	client
Rocky8-1	192.168.121.160	haproxy

2.mysql服务器配置

1.下载mariadb

#下载mariadb
[root@open-Euler2 ~]# yum install mariadb-server -y

2.创建用户并授权

MariaDB [(none)]> RENAME USER 'test'@'%' TO 'test'@'%';
Query OK, 0 rows affected (0.001 sec)

3.配置server_id便于测试

在/etc/my.cnf配置文件的[mysqld]下增加一行server_id,例如:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=150

然后重启服务:

systemctl restart mariadb

注：两台服务器的server-id不能一样，另外一台我配成了server-id=150

3.haproxy安装和配置

1.下载合适版本的haproxy

下载链接

我这里是源码编译安装haproxy-3.0.8-tar.gz

当然也可以用命令在线安装

curl -O https://www.haproxy.org/download/3.0/src/haproxy-3.0.8.tar.gz

解压:

[root@Rocky8 ~]# tar -xf haproxy-3.0.8.tar.gz -C /usr/local/src/
[root@Rocky8 ~]# cd /usr/local/src/haproxy-3.0.8/
[root@Rocky8 haproxy-3.0.8]# ll
total 1736
drwxrwxr-x.  7 root root      79 Jan 29 09:38 addons
drwxrwxr-x. 12 root root    4096 Jan 29 09:38 admin
-rw-rw-r--.  1 root root   14046 Jan 29 09:38 BRANCHES
-rw-rw-r--.  1 root root     345 Jan 29 09:38 BSDmakefile
-rw-rw-r--.  1 root root 1528884 Jan 29 09:38 CHANGELOG
-rw-rw-r--.  1 root root   55775 Jan 29 09:38 CONTRIBUTING
drwxrwxr-x. 17 root root    4096 Jan 29 09:38 dev
drwxrwxr-x.  5 root root    4096 Jan 29 09:38 doc
drwxrwxr-x.  4 root root    4096 Jan 29 09:38 examples
drwxrwxr-x.  5 root root      47 Jan 29 09:38 include
-rw-rw-r--.  1 root root   43394 Jan 29 09:38 INSTALL
-rw-rw-r--.  1 root root    2029 Jan 29 09:38 LICENSE
-rw-rw-r--.  1 root root    5628 Jan 29 09:38 MAINTAINERS
-rw-rw-r--.  1 root root   54751 Jan 29 09:38 Makefile
-rw-rw-r--.  1 root root    1093 Jan 29 09:38 README
drwxrwxr-x. 35 root root    4096 Jan 29 09:38 reg-tests
drwxrwxr-x.  2 root root    4096 Jan 29 09:38 scripts
drwxrwxr-x.  2 root root    8192 Jan 29 09:38 src
-rw-rw-r--.  1 root root      10 Jan 29 09:38 SUBVERS
drwxrwxr-x.  5 root root      41 Jan 29 09:38 tests
-rw-rw-r--.  1 root root      37 Jan 29 09:38 VERDATE
-rw-rw-r--.  1 root root       6 Jan 29 09:38 VERSION

安装基础命令及其编译依赖环境

[root@Rocky8 haproxy-3.0.8]# yum -y install gcc openssl-devel pcre-devel systemd-devel make
[root@Rocky8 ~]# curl -L -R -O http://www.lua.org/ftp/lua-5.4.7.tar.gz
[root@Rocky8 ~]# tar xvf  lua-5.4.7.tar.gz -C /opt/
[root@Rocky8 ~]# cd /opt/lua-5.4.7
[root@Rocky8 ~] lua-5.4.7]# make linux test

编译安装

[root@Rocky8 haproxy]# cd /usr/local/src/haproxy-3.0.8/
[root@Rocky8 haproxy-3.0.8]# make  ARCH=x86_64 TARGET=linux-glibc  USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1  USE_SYSTEMD=1  USE_LUA=1 LUAINC=/opt/lua-5.4.7/src LUA_LIB=/opt/lua-5.4.7/src  #编译
[root@Rocky8 haproxy-3.0.8]# make install PREFIX=/apps/haproxy/  #安装
[root@Rocky8 haproxy-3.0.8]# tree /apps  #查看生成的文件
/apps
└── haproxy├── doc│   └── haproxy│       ├── 51Degrees-device-detection.txt│       ├── architecture.txt│       ├── configuration.txt│       ├── cookie-options.txt│       ├── DeviceAtlas-device-detection.txt│       ├── intro.txt│       ├── linux-syn-cookies.txt│       ├── lua.txt│       ├── management.txt│       ├── netscaler-client-ip-insertion-protocol.txt│       ├── network-namespaces.txt│       ├── peers.txt│       ├── peers-v2.0.txt│       ├── proxy-protocol.txt│       ├── regression-testing.txt│       ├── seamless_reload.txt│       ├── SOCKS4.protocol.txt│       ├── SPOE.txt│       └── WURFL-device-detection.txt├── sbin│   └── haproxy└── share└── man└── man1└── haproxy.17 directories, 21 files

2.验证haproxy版本

[root@Rocky8 haproxy-3.0.8]# haproxy -v
HAProxy version 3.0.8-6036c31 2025/01/29 - https://haproxy.org/
Status: long-term supported branch - will stop receiving fixes around Q2 2029.
Known bugs: http://www.haproxy.org/bugs/bugs-3.0.8.html
Running on: Linux 4.18.0-553.el8_10.x86_64 #1 SMP Fri May 24 13:05:10 UTC 2024 x86_64

3.HAProxy启动脚本

[root@Rocky8 haproxy-3.0.8]# cat /usr/lib/systemd/system/haproxy.service
[Unit]
Description=HAProxy Load Balancer
After=syslog.target network.target[Service]
ExecStartPre=/usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -f /etc/haproxy/conf.d -c -q
ExecStart=/usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -f /etc/haproxy/conf.d -p /var/lib/haproxy/haproxy.pid
ExecReload=/bin/kill -USR2 $MAINPID[Install]
WantedBy=multi-user.target默认缺少配置文件，无法启动创建自定义的配置文件:[root@Rocky8 haproxy-3.0.8]# mkdir  /etc/haproxy
[root@Rocky8 haproxy-3.0.8]# cat /etc/haproxy/haproxy.cfg 
globalmaxconn 100000chroot /apps/haproxystats socket /var/lib/haproxy/haproxy.sock mode 600 level admin#uid 99#gid 99user  haproxygroup haproxydaemon#nbproc 4#cpu-map 1 0#cpu-map 2 1#cpu-map 3 2#cpu-map 4 3pidfile /var/lib/haproxy/haproxy.pidlog 127.0.0.1 local2 infodefaultsoption http-keep-aliveoption  forwardformaxconn 100000mode httptimeout connect 300000mstimeout client  300000mstimeout server  300000mslisten statsmode httpbind 0.0.0.0:9999stats enablelog globalstats uri     /haproxy-statusstats auth    haadmin:123456

4.启动haproxy

[root@Rocky8 haproxy-3.0.8]# mkdir  /var/lib/haproxy
[root@Rocky8 haproxy-3.0.8]# useradd -r -s /sbin/nologin -d /var/lib/haproxy haproxy
[root@Rocky8 haproxy-3.0.8]# systemctl  enable --now haproxy

5.验证haproxy状态

[root@Rocky8 haproxy-3.0.8]# systemctl status haproxy.service 
● haproxy.service - HAProxy Load BalancerLoaded: loaded (/usr/lib/systemd/system/haproxy.service; disabled; vendor preset: disabled)Active: active (running) since Sat 2025-02-15 17:29:43 CST; 1h 2min agoProcess: 12629 ExecStartPre=/usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -f /etc/haproxy/conf.d -c -q (code=exited, status=0/SU>Main PID: 12632 (haproxy)Tasks: 3 (limit: 23007)Memory: 20.2MCGroup: /system.slice/haproxy.service├─12632 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -f /etc/haproxy/conf.d -p /var/lib/haproxy/haproxy.pid└─12634 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -f /etc/haproxy/conf.d -p /var/lib/haproxy/haproxy.pidFeb 15 17:29:43 Rocky8 systemd[1]: Starting HAProxy Load Balancer...
Feb 15 17:29:43 Rocky8 systemd[1]: Started HAProxy Load Balancer.
Feb 15 17:29:43 Rocky8 haproxy[12632]: [NOTICE]   (12632) : haproxy version is 3.0.8-6036c31
Feb 15 17:29:43 Rocky8 haproxy[12632]: [NOTICE]   (12632) : path to executable is /usr/sbin/haproxy
Feb 15 17:29:43 Rocky8 haproxy[12632]: [ALERT]    (12632) : config : parsing [/etc/haproxy/haproxy.cfg:15] : 'pidfile' already spe>
Feb 15 17:29:43 Rocky8 haproxy[12632]: [WARNING]  (12632) : config : 'option forwardfor' ignored for proxy 'web_test' as it requir>
Feb 15 17:29:43 Rocky8 haproxy[12632]: [WARNING]  (12632) : config : 'option forwardfor' ignored for frontend 'mysql' as it requir>
Feb 15 17:29:43 Rocky8 haproxy[12632]: [WARNING]  (12632) : config : 'option forwardfor' ignored for backend 'mysqlsrvs' as it req>
Feb 15 17:29:43 Rocky8 haproxy[12632]: [NOTICE]   (12632) : New worker (12634) forked
Feb 15 17:29:43 Rocky8 haproxy[12632]: [NOTICE]   (12632) : Loading success.[root@Rocky8 haproxy-3.0.8]# pstree -p |grep haproxy|-haproxy(12632)---haproxy(12634)---{haproxy}(12635)

6.查看haproxy的状态页面

浏览器访问：http://192.168.121.160:9999/haproxy-status 这里的ip改成你的haproxy服务器ip

注：登录该界面需要用户名和密码，我这里用户名和密码在配置文件/etc/haproxy/haproxy.cfg设置成了

账号：hadmin

密码:123456

7.配置haproxy实现MySQL服务器负载均衡

在/etc/haproxy/haproxy.cfg追加以下内容:

frontend mysqlbind :3306mode tcpdefault_backend mysqlsrvs
backend mysqlsrvsmode tcpbalance leastconnserver mysql1 192.168.121.150:3306server mysql2 192.168.121.151:3306

8.测试