使用haproxy实现MySQL服务器负载均衡

一、环境准备

主机名	IP地址	备注
openEuler-1	192.168.121.11	mysql-server-1
openEuler-2	192.168.121.12	mysql-server-2
openEuler-3	192.168.121.13	client
Rocky-1	192.168.121.51	haproxy

二、mysql-server配置

[root@openEuler-1 ~]# yum install -y mariadb-server
[root@openEuler-1 ~]# vim /etc/my.cnf
[mysqld]
server-id=11     # 另一台主机为12# 授权用户（mariadb授权自动创建用户）
[root@openEuler-1 ~]# systemctl enable --now mariadb
[root@openEuler-1 ~]# mysql -e "grant all on *.* to test@'192.168.121.%' identified by '123456'"

三、haproxy配置

[root@Rocky-1 ~]# vim /etc/haproxy/conf.d/mysql.cfg
listen mysql_portbind 192.168.121.51:3306mode tcpbalance leastconnserver mysql1 192.168.121.11:3306 checkserver mysql2 192.168.121.12:3306 check# 或者使用frontend和backend实现
frontend mysqlbind :3306mode tcp                            #必须指定tcp模式default_backend mysqlsrvs
backend mysqlsrvsmode tcp                            #必须指定tcp模式balance leastconnserver mysql1 192.168.121.11:3306server mysql2 192.168.121.11:3306# 检查配置文件
[root@Rocky-1 ~]# haproxy -c -f /etc/haproxy/conf.d/mysql.cfg
[root@Rocky-1 ~]# systemctl restart haproxy.service

四、client测试

[root@openEuler-3 ~]# mysql -utest -p123456 -h192.168.121.51 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|          11 |
+-------------+
[root@openEuler-3 ~]# mysql -utest -p123456 -h192.168.121.51 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|          12 |
+-------------+