haproxy实现MySQL服务器负载均衡

1.环境准备

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

主机名IP角色open-Euler1192.168.121.150mysql-server1openEuler-2192.168.121.151mysql-server2openEuler-3192.168.121.152clientRocky8-1192.168.121.160haproxy 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 f配置文件的[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 .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:// .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.1 7 directories, 21 files 2.验证haproxy版本 [root@Rocky8 haproxy-3.0.8]# haproxy -v HAProxy version 3.0.8-6036c31 2025/01/29 - haproxy.org/ Status: long-term supported branch - will stop receiving fixes around Q2 2029. Known bugs: http:// .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 global maxconn 100000 chroot /apps/haproxy stats socket /var/lib/haproxy/haproxy.sock mode 600 level admin #uid 99 #gid 99 user haproxy group haproxy daemon #nbproc 4 #cpu-map 1 0 #cpu-map 2 1 #cpu-map 3 2 #cpu-map 4 3 pidfile /var/lib/haproxy/haproxy.pid log 127.0.0.1 local2 info defaults option http-keep-alive option forwardfor maxconn 100000 mode http timeout connect 300000ms timeout client 300000ms timeout server 300000ms listen stats mode http bind 0.0.0.0:9999 stats enable log global stats uri /haproxy-status stats 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 Balancer Loaded: 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 ago Process: 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.2M CGroup: /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.pid Feb 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 mysql bind :3306 mode tcp default_backend mysqlsrvs backend mysqlsrvs mode tcp balance leastconn server mysql1 192.168.121.150:3306 server mysql2 192.168.121.151:3306 8.测试