ProxySQL实现MySQL主从集群的读写分离

基于 CentOS 7 MySQL 5.7MySQL将配置主从复制集群ProxySQL实现 SQL 语句执行的读写分离请求的负载均衡一、环境说明与架构图1. 服务器角色与 IP 规划角色IP 地址系统版本软件版本备注主库Master192.168.1.10CentOS 7MySQL 5.7处理写请求同步数据到从库从库 1Slave1192.168.1.11CentOS 7MySQL 5.7处理读请求同步主库数据从库 2Slave2192.168.1.12CentOS 7MySQL 5.7处理读请求同步主库数据ProxySQL192.168.1.13CentOS 7ProxySQL 2.5.5读写分离中间件分发请求2. 架构图应用程序 → ProxySQL192.168.1.13:6033 ├→ 主库192.168.1.10:3306【写请求INSERT/UPDATE/DELETE】 ├→ 从库1192.168.1.11:3306【读请求SELECT负载均衡】 └→ 从库2192.168.1.12:3306【读请求SELECT负载均衡】二、环境准备1. 关闭防火墙与 SELinux# 关闭防火墙 systemctl stop firewalld systemctl disable firewalld ​ # 临时关闭SELinux setenforce 0 # 永久关闭SELinux重启生效 sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config2. 安装 MySQL 5.7# 安装MySQL官方仓库 rpm -Uvh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm ​ # 安装MySQL 5.7 yum install -y mysql-community-server ​ # 启动MySQL并设置开机自启 systemctl start mysqld systemctl enable mysqld ​ # 获取初始密码MySQL 5.7默认生成随机密码 grep temporary password /var/log/mysqld.log # 示例输出A temporary password is generated for rootlocalhost: xxxxxxxx ​ # 登录MySQL并修改root密码需符合复杂度大小写数字符号 mysql -u root -p # 输入初始密码后执行以下SQL ALTER USER rootlocalhost IDENTIFIED BY Root123456; FLUSH PRIVILEGES;三、MySQL 主从复制配置1. 主库配置- 修改 MySQL 配置文件vim /etc/my.cnf 添加以下内容 ----------------------------------------------------------------------------- [mysqld] server-id 10 # 主库唯一ID log_bin /var/lib/mysql/mysql-bin # 开启binlog binlog_format ROW # 行级复制避免SQL模式差异导致同步失败 expire_logs_days 7 # binlog保留7天避免磁盘占满 sync_binlog 1 # 每次事务提交同步binlog到磁盘可选增强安全性 ----------------------------------------------------------------------------- 重启 MySQL 使配置生效 systemctl restart mysqld- 创建主从复制用户登录主库 MySQLmysql -u root -pRoot123456允许从库 IP 连接需要创建复制用户CREATE USER repl192.168.1.% IDENTIFIED BY Repl123456; # 从库IP前缀匹配 GRANT REPLICATION SLAVE ON *.* TO repl192.168.1.%; FLUSH PRIVILEGES;- 查看主库 binlog 状态FLUSH TABLES WITH READ LOCK; # 锁表防止配置期间数据写入 SHOW MASTER STATUS; 记录输出结果后续从库配置需要 ------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000001 | 154 | | | | ------------------------------------------------------------------------------- 解锁表 UNLOCK TABLES;2. 从库配置- 修改 MySQL 配置文件vim /etc/my.cnf 添加以下内容 ----------------------------------------------------------------------------- [mysqld] server-id 11 # 从库1用11从库2用12 relay_log /var/lib/mysql/relay-bin # 中继日志同步主库binlog用 log_bin /var/lib/mysql/mysql-bin # 若需级联复制从库再作为主库 则开启否则可选 read_only ON # 从库设置为只读root用户不受限避免误写 ----------------------------------------------------------------------------- 重启 MySQL systemctl restart mysqld- 配置从库连接主库登录从库 MySQLmysql -u root -pRoot123456配置主库信息替换为实际主库 IP、binlog 文件和位置CHANGE MASTER TO MASTER_HOST 192.168.1.10, MASTER_USER repl, MASTER_PASSWORD Repl123456, MASTER_LOG_FILE mysql-bin.000001, # 主库SHOW MASTER STATUS的File值 MASTER_LOG_POS 154; # 主库SHOW MASTER STATUS的Position值启动从库复制进程START SLAVE;- 验证主从同步状态SHOW SLAVE STATUS\G关键参数需满足Slave_IO_Running: YesIO 线程正常Slave_SQL_Running: YesSQL 线程正常Seconds_Behind_Master: 0无延迟或数值较小四、ProxySQL 部署与配置1. 安装 ProxySQL# 添加ProxySQL仓库 cat /etc/yum.repos.d/proxysql.repo EOF [proxysql_repo] nameProxySQL YUM repository baseurlhttps://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/7 gpgcheck1 gpgkeyhttps://repo.proxysql.com/ProxySQL/repo_pub_key EOF ​ # 安装ProxySQL yum install -y proxysql ​ # 启动并设置开机自启 systemctl start proxysql systemctl enable proxysqlProxySQL 默认端口管理接口6032 --- 用于配置 ProxySQL数据库接口6033 --- 应用连接的端口模拟 MySQL2. 配置 ProxySQL- 登录 ProxySQL 管理界面默认账号密码admin/admin建议后期修改mysql -u admin -padmin -h 127.0.0.1 -P 6032- 添加 MySQL 集群服务器ProxySQL 通过 “主机组” 区分读写节点通常主机组 10写组主库主机组 20读组从库执行以下 SQL 添加主库和从库# 添加主库到写组10 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (10, 192.168.1.10, 3306, 1); ​ # 添加从库1到读组20 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (20, 192.168.1.11, 3306, 1); # weight1表示权重值越高分配请求越多 ​ # 添加从库2到读组20 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (20, 192.168.1.12, 3306, 2); # 从库2性能更好权重设为2- 配置读写分离规则通过mysql_query_rules定义 SQL 路由规则# 所有SELECT语句除SELECT ... FOR UPDATE路由到读组20 INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, ^SELECT .*FOR UPDATE$, 10, 0); # SELECT FOR UPDATE强制走写组因为会加锁 ​ INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, ^SELECT, 20, 1); # 普通SELECT走读组 ​ # 其他语句INSERT/UPDATE/DELETE路由到写组10 INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (3, 1, ., 10, 1);- 添加应用访问用户假设后端应用通过app_user用户连接数据库则需要在 ProxySQL 中配置该用户并映射到后端 MySQL 的实际用户# 在ProxySQL中添加用户用户名/密码需与后端MySQL集群的一致 INSERT INTO mysql_users (username, password, default_hostgroup) VALUES (app_user, App123456, 10); # 防止无规则匹配default_hostgroup默认走写组 ​ # 授权用户访问所有数据库 UPDATE mysql_users SET default_schematest WHERE usernameapp_user;注意需在主库创建app_user用户从库会自动同步-- 在主库执行 CREATE USER app_user% IDENTIFIED BY App123456; GRANT ALL PRIVILEGES ON *.* TO app_user%; FLUSH PRIVILEGES;- 加载配置并保存登录控制台6032保存配置ProxySQL 配置需加载到运行时runtime并保存到磁盘disk否则重启后失效# 加载mysql_servers和mysql_users到runtime LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL USERS TO RUNTIME; ​ # 加载mysql_query_rules到runtime LOAD MYSQL QUERY RULES TO RUNTIME; ​ # 保存配置到磁盘 SAVE MYSQL SERVERS TO DISK; SAVE MYSQL USERS TO DISK; SAVE MYSQL QUERY RULES TO DISK;3. 配置健康检查ProxySQL 默认会检测后端节点状态可通过以下参数调整在管理接口执行# 检测间隔毫秒 UPDATE global_variables SET variable_value1000 WHERE variable_namemysql_monitor_ping_interval; ​ # 连接超时毫秒 UPDATE global_variables SET variable_value2000 WHERE variable_namemysql_monitor_connect_timeout; ​ # 检测失败阈值超过则标记节点不可用 UPDATE global_variables SET variable_value3 WHERE variable_namemysql_monitor_ping_max_failures; ​ # 加载并保存配置 LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;五、功能验证1. 验证主从复制在主库创建测试库和表并插入数据-- 主库执行 CREATE DATABASE IF NOT EXISTS test_db; USE test_db; CREATE TABLE t_user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20)); INSERT INTO t_user (name) VALUES (test1), (test2);在从库 1 和从库 2 验证数据是否同步-- 从库执行 USE test_db; SELECT * FROM t_user; # 应看到两条数据说明同步成功2. 验证读写分离使用应用用户通过 ProxySQL 连接数据库mysql -u app_user -pApp123456 -h 192.168.1.13 -P 6033- 主库写入在 ProxySQL 连接中执行插入操作USE test_db; INSERT INTO t_user (name) VALUES (proxy_write_test);到主库查看是否有数据从库是否同步确认写操作在主库执行- 从库读取在 ProxySQL 连接中执行查询SELECT * FROM t_user;在从库 1 和从库 2 执行以下命令查看当前连接确认读请求在从库SHOW PROCESSLIST; # 应看到来自ProxySQL192.168.1.13的查询连接3. 验证读库负载均衡多次执行查询SELECT * FROM t_user;在从库 1 和从库 2 查看SHOW PROCESSLIST的连接次数应符合权重比例 从库 1: 从库 2 ≈ 1:2说明轮询 加权策略生效。六、常见问题排查1.主从同步失败检查从库SHOW SLAVE STATUS\G中Last_IO_Error或Last_SQL_Error通常是网络不通、复制用户密码错误或 binlog 位置错误。解决重新执行CHANGE MASTER TO修正主库信息或用STOP SLAVE; RESET SLAVE ALL;重置后重新配置。2.ProxySQL 无法连接后端 MySQL检查后端 MySQL 是否允许 ProxySQL IP192.168.1.13连接用户权限是否包含%或具体 IP检查 ProxySQL 中mysql_users的密码是否与后端一致3.读写分离规则不生效在 ProxySQL 管理接口执行SELECT * FROM mysql_query_rules;确认规则active1。执行SELECT * FROM stats_mysql_query_digest;查看 SQL 是否匹配到正确规则。七、总结通过以上步骤已实现MySQL 主从复制即 主库写入从库同步ProxySQL 读写分离即 写请求走主库读请求走从库读库负载均衡即 按权重分发读请求到多个从库。后续可根据业务需求扩展从库数量或通过 ProxySQL 配置高可用比如 双机热备。