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

一、环境准备 主机名IP地址备注openEuler-1192.168.121.11mysql-server-1openEuler-2192.168.121.12mysql-server-2openEuler-3192.168.121.13clientRocky-1192.168.121.51haproxy 二、mysql-server配置 [root@openEuler-1 ~]# yum install -y mariadb-server [root@openEuler-1 ~]# vim /etc/my f [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_port bind 192.168.121.51:3306 mode tcp balance leastconn server mysql1 192.168.121.11:3306 check server mysql2 192.168.121.12:3306 check # 或者使用frontend和backend实现 frontend mysql bind :3306 mode tcp #必须指定tcp模式 default_backend mysqlsrvs backend mysqlsrvs mode tcp #必须指定tcp模式 balance leastconn server mysql1 192.168.121.11:3306 server 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 | +-------------+