ProxySQL 读写分离配置

📋 环境规划

hostgroup 规划：10=写组（主库）、20=读组（从库）

主机名	IP	角色	说明
db-master	192.168.7.10	MySQL 主库	写操作
db-slave1	192.168.7.20	MySQL 从库1	读操作
db-slave2	192.168.7.30	MySQL 从库2 + ProxySQL	读操作 + 负载均衡

安装 ProxySQL（db-slave2）

 # 配置ProxySQL下载
 cat > /etc/yum.repos.d/proxysql.repo << EOF
 [proxysql_repo]
 name=ProxySQL repository
 baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/8
 gpgcheck=1
 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
 EOF
 
 # 清理缓存 > 生成缓存 > 安装软件
 yum clean all
 yum makecache
 yum install -y proxysql
 
 # 启动ProxySQL 并设置开机自启
 systemctl start proxysql
 systemctl enable proxysql

添加后端节点（ProxySQL 执行）

 -- 登录ProxySQL
 mysql -u admin -padmin -h 127.0.0.1 -P6032
 
 -- 清空 ProxySQL 中所有后端 MySQL 服务器的配置
 DELETE FROM mysql_servers;
 
 -- 主库（写组 10）
 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
 VALUES (10, '192.168.7.10', 3306, 100);
 
 -- 从库1（读组 20）
 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
 VALUES (20, '192.168.7.20', 3306, 100);
 
 -- 从库2（读组 20）
 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
 VALUES (20, '192.168.7.30', 3306, 100);
 
 -- 加载到运行时 及 保存到磁盘
 LOAD MYSQL SERVERS TO RUNTIME;
 SAVE MYSQL SERVERS TO DISK;
 
 -- 验证
 -- （预期：3条记录，10=主库，20=两个从库）
 SELECT * FROM runtime_mysql_servers;

配置复制主机组

 -- 登录 ProxySQL
 mysql -u admin -padmin -h 127.0.0.1 -P6032
 
 -- 配置复制主机组（10=写组/主库，20=读组/从库）
 INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
 VALUES (10, 20, 'master-slave');
 
 -- 加载并保存
 LOAD MYSQL SERVERS TO RUNTIME;
 SAVE MYSQL SERVERS TO DISK;
 
 -- 验证
 SELECT * FROM mysql_replication_hostgroups;
 -- 预期：writer=10, reader=20

在主节点 MySQL 创建 monitor 用户

⚠️ 什么情况下主库 + 两个从库 都要执行
操作方式
是否通过SQL语句
是否被主从同步
结果
CREATE USER 'monitor'@'%' ...
✅ 是 (DDL)
✅ 是
主库执行后，从库会同步创建同名用户。
INSERT INTO mysql.user ...
❌ 否 (DML)
❌ 否
操作仅在当前实例生效，主从数据不一致。

操作方式	是否通过SQL语句	是否被主从同步	结果
`CREATE USER 'monitor'@'%' ...`	✅ 是 (DDL)	✅ 是	主库执行后，从库会同步创建同名用户。
`INSERT INTO mysql.user ...`	❌ 否 (DML)	❌ 否	操作仅在当前实例生效，主从数据不一致。

 # 在 db-master、db-slave1、db-slave2 的 MySQL（3306）分别执行上面 SQL
 
 # 登录MySQL
 mysql -uroot -p123456
 
 -- 创建用户 > 授予权限 > 刷新生效
 CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'Monitor123!';
 GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
 FLUSH PRIVILEGES;

ProxySQL 配置 monitor（ProxySQL 执行）

 # 登录ProxySQL
 mysql -u admin -padmin -h 127.0.0.1 -P6032
 
 -- ProxySQL 中配置监控功能
 SET mysql-monitor_username='monitor';
 SET mysql-monitor_password='Monitor123!';
 
 -- 加载到运行时 及 保存到磁盘
 LOAD MYSQL VARIABLES TO RUNTIME;
 SAVE MYSQL VARIABLES TO DISK;
 
 **等待 10-20 秒后验证监控：**
 
 
 -- 查看当前 ProxySQL 中所有后端 MySQL 服务器的实时状态 
 --（预期：全部 ONLINE ✅）
 SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
 
 -- 查看 ProxySQL 对后端 MySQL 服务器的网络层 ping 检测日志，用于监控网络连通性
 -- （预期：ping_success = true）
 SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 5;
 
 # 可能存在 ProxySQL 把连接失败的节点标记为 SHUNNED（临时剔除）
 # 即使后来监控恢复了，这个状态也不会自动恢复，需要手动重置
 -- ProxySQL 管理端
 UPDATE mysql_servers SET status='ONLINE' WHERE status='SHUNNED';
 LOAD MYSQL SERVERS TO RUNTIME;
 SAVE MYSQL SERVERS TO DISK;
 
 # 上面是把所有 SHUNNED 改成 ONLINE
 # 这个是把某一个主机修改成 ONLINE
 -- ProxySQL 管理端
 UPDATE mysql_servers SET status='ONLINE' WHERE hostname='192.168.7.20';
 LOAD MYSQL SERVERS TO RUNTIME;
 SAVE MYSQL SERVERS TO DISK;

在主节点 MySQL 创建 app_user 用户

⚠️ 什么情况下主库 + 两个从库 都要执行
操作方式
是否通过SQL语句
是否被主从同步
结果
CREATE USER 'monitor'@'%' ...
✅ 是 (DDL)
✅ 是
主库执行后，从库会同步创建同名用户。
INSERT INTO mysql.user ...
❌ 否 (DML)
❌ 否
操作仅在当前实例生效，主从数据不一致。

操作方式	是否通过SQL语句	是否被主从同步	结果
`CREATE USER 'monitor'@'%' ...`	✅ 是 (DDL)	✅ 是	主库执行后，从库会同步创建同名用户。
`INSERT INTO mysql.user ...`	❌ 否 (DML)	❌ 否	操作仅在当前实例生效，主从数据不一致。

 # 登录MySQL
 mysql -uroot -p123456
 
 -- 创建用户 > 授予权限 > 刷新生效
 CREATE USER 'app_user'@'%' IDENTIFIED BY '123456';
 GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';
 FLUSH PRIVILEGES;

ProxySQL 配置 app_user（ProxySQL 执行一次）

 # 登录 ProxySQL
 mysql -u admin -padmin -h 127.0.0.1 -P6032
 
 -- 设置用户
 INSERT INTO mysql_users (username, password, default_hostgroup)
 VALUES ('app_user', '123456', 10);
 
 -- 加载到运行时 及 保存到磁盘
 LOAD MYSQL USERS TO RUNTIME;
 SAVE MYSQL USERS TO DISK;
 
 -- 验证连接
 # 在 db-slave2 执行
 （预期：返回 1 ✅）
 mysql -u app_user -p123456 -h 127.0.0.1 -P6033 -e "SELECT 1"

配置读写分离规则（ProxySQL 执行）

 # 登录 ProxySQL
 mysql -u admin -padmin -h 127.0.0.1 -P6032
 
 -- 1. 清空旧规则
 DELETE FROM mysql_query_rules;
 
 -- 2. 添加读写分离规则（不是加服务器！）
 -- 规则1：写操作 → 主库(10)
 INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
 VALUES (1, 1, '^INSERT|^UPDATE|^DELETE|^REPLACE|^CREATE|^ALTER|^DROP', 10, 1);
 
 -- 规则2：SELECT ... FOR UPDATE → 主库(10)
 INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
 VALUES (2, 1, '^SELECT.*FOR UPDATE', 10, 1);
 
 -- 规则3：普通 SELECT → 从库(20)
 INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
 VALUES (3, 1, '^SELECT', 20, 1);
 
 -- 规则4：SHOW → 从库(20)
 INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
 VALUES (4, 1, '^SHOW', 20, 1);
 
 -- 3. 加载并保存规则
 LOAD MYSQL QUERY RULES TO RUNTIME;
 SAVE MYSQL QUERY RULES TO DISK;
 
 -- 4. 验证规则
 SELECT rule_id, match_digest, destination_hostgroup 
 FROM mysql_query_rules ORDER BY rule_id;
 -- 预期：4条规则，1→10(写)，2→10(FOR UPDATE)，3→20(读)，4→20(SHOW)

验证读写分离

 # 通过 ProxySQL 连接后查看实际访问的主机
 mysql -u app_user -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname, @@port;"
 
 mysql -u app_user -p123456 -h 127.0.0.1 -P6033
 
 USE school_db;
 
 -- 写操作（应走主库 10）
 INSERT INTO students(name, age, class_name) VALUES ('ProxySQL测试', 20, 'Q20班');
 UPDATE students SET age = 21 WHERE name = 'ProxySQL测试';
 DELETE FROM students WHERE name = 'ProxySQL测试';
 
 -- 读操作（应走从库 20）
 SELECT * FROM students;
 SELECT COUNT(*) FROM students;
 
 **查看路由统计：**
 -- ProxySQL 管理端执行
 -- 退出当前 mysql 连接
 exit
 
 -- 重新连接 ProxySQL 管理端
 mysql -u admin -padmin -h 127.0.0.1 -P6032
 
 -- 查询预期效果
 SELECT hostgroup, digest_text, count_star
 FROM stats_mysql_query_digest
 ORDER BY count_star DESC LIMIT 10;
 -- 预期：INSERT/UPDATE/DELETE → hostgroup=10
 --       SELECT → hostgroup=20 ✅

模拟故障（可选）

 # 停从库1
 systemctl stop mysqld   # db-slave1 (192.168.7.20)
 
 -- ProxySQL 等待 10-20 秒后
 SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
 -- 预期：192.168.7.20 → SHUNNED，192.168.7.30 仍 ONLINE
 
 # 读请求仍正常
 mysql -u app_user -p123456 -h 127.0.0.1 -P6033 -e "SELECT * FROM school_db.students;"
 
 # 恢复从库1
 systemctl start mysqld
 # 等待 10-20 秒后自动恢复 ONLINE

🔍 常见问题排查

问题	排查方法
无法登录 admin	`systemctl status proxysql`、重启服务
节点 OFFLINE	检查 monitor 用户是否在所有节点创建、`monitor.mysql_server_connect_log`
读写分离不生效	检查规则顺序、`DELETE FROM stats_mysql_query_digest` 清空统计重测
应用用户连接失败	`SELECT * FROM mysql_users`、重新 `LOAD MYSQL USERS`

📌 核心要点（背下来）

 ProxySQL 配置（mysql_servers/mysql_users/mysql_query_rules）→ 只在 db-slave2 的 6032 执行
 MySQL 账号（CREATE USER monitor/app_user）→ 每台 MySQL 节点都要执行
 顺序：节点 → monitor(MySQL) → monitor(ProxySQL) → app_user(MySQL) → app_user(ProxySQL) → 规则

补充

权重（Weight）概念

权重是 ProxySQL 中控制流量分配比例的参数，用于在同一个 hostgroup 内进行负载均衡。

 weight = 100  -- 基准值
 weight = 200  -- 权重越高，分配的流量越多
 weight = 50   -- 权重越低，分配的流量越少

计算公式

 某服务器流量占比 = 该服务器权重 / 同组所有权重之和
 
 实际请求数 = 总请求数 × 流量占比

文档中的 `comment` 使用

 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment) 
 VALUES (10, '192.168.7.10', 3306, 100, '主库-写');
 
 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment) 
 VALUES (20, '192.168.7.20', 3306, 100, '从库1-读');
 
 INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment) 
 VALUES (20, '192.168.7.30', 3306, 100, '从库2-读');

2.2 查看 `comment`

 SELECT hostgroup_id, hostname, port, weight, comment
 FROM mysql_servers;

风格	示例	适用场景
简单标签	`主库-写`	小型环境
详细描述	`【生产】主库-192.168.7.10-2024年采购`	大型环境
机房+角色	`北京机房-主库-读写`	多机房
性能+角色	`高配32核-从库-只读`	性能差异明显
负责人+角色	`张三负责-主库-生产`	团队协作

FanXing

MySQL 一主两从读写分离配置

ProxySQL 读写分离配置

📋 环境规划

安装 ProxySQL（db-slave2）

添加后端节点（ProxySQL 执行）

配置复制主机组

在主节点 MySQL 创建 monitor 用户

ProxySQL 配置 monitor（ProxySQL 执行）

在主节点 MySQL 创建 app_user 用户

ProxySQL 配置 app_user（ProxySQL 执行一次）

配置读写分离规则（ProxySQL 执行）

验证读写分离

模拟故障（可选）

🔍 常见问题排查

📌 核心要点（背下来）

补充

权重（Weight）概念

计算公式

文档中的 `comment` 使用

2.2 查看 `comment`

推荐的注释格式

MySQL 一主两从读写分离配置

评论交流

FanXing

MySQL 一主两从读写分离配置

ProxySQL 读写分离配置

📋 环境规划

安装 ProxySQL（db-slave2）

添加后端节点（ProxySQL 执行）

配置复制主机组

在主节点 MySQL 创建 monitor 用户

ProxySQL 配置 monitor（ProxySQL 执行）

在主节点 MySQL 创建 app_user 用户

ProxySQL 配置 app_user（ProxySQL 执行一次）

配置读写分离规则（ProxySQL 执行）

验证读写分离

模拟故障（可选）

🔍 常见问题排查

📌 核心要点（背下来）

补充

权重（Weight）概念

计算公式

文档中的 comment 使用

2.2 查看 comment

推荐的注释格式

MySQL 一主两从读写分离配置

评论交流

文档中的 `comment` 使用

2.2 查看 `comment`