MySQL 主从复制 + ProxySQL 读写分离

一、整体架构与原理
1）架构

• MySQL 主库（Master）：处理所有 写（INSERT/UPDATE/DELETE）
• MySQL 从库（Slave）：通过 binlog 实时同步主库数据，处理 读（SELECT）
• ProxySQL：高性能 C++ 代理，位于应用与 MySQL 之间
  • 应用只连 ProxySQL（一个地址）
  • ProxySQL 自动路由：写→主库，读→从库
  • 自带连接池、健康检查、故障自动摘除、权重负载均衡

2）核心原理

• 主从复制：主库写操作记录到 binlog → 从库拉取并执行 → 数据一致
• 读写分离：ProxySQL 通过正则规则识别 SQL 类型，转发到对应主机组
• 高可用：从库宕机自动下线；主库宕机可配合 MHA/Orchestrator 自动切换

二、环境准备（示例）

• 操作系统：CentOS stream 9
• MySQL：8.0（ GTID 模式）
• ProxySQL：2.6+
• 节点规划：
  • 主库：192.168.88.131（server-id=1）
  • 从库：192.168.88.132（server-id=2）
  • ProxySQL：192.168.88.133

三、第一步：配置 MySQL 主从复制（这里采用GTID 模式）
前提：时间同步，防火墙关闭/放行3306端口，selinux关闭

1）主库配置（192.168.88.131）
/etc/my.cnf
[mysqld]
server-id=1 # 主库必须唯一，从库不能一样
log-bin=mysql-bin #主库开启binlog
binlog_format=ROW # 行级复制
gtid_mode=ON # GTID开启
enforce-gtid-consistency=ON # 强制GTID一致性
expire_logs_days=7 # 日志自动清理7天前
max_binlog_size=1G # 单个binlog最大1G
重启：
systemctl restart mysqld

创建复制用户：
CREATE USER 'repl'@'%' IDENTIFIED BY 'Aa123456.';
GRANT REPLICATION SLAVE ON . TO 'repl'@'%';
FLUSH PRIVILEGES;

2）从库配置（192.168.88.132）
/etc/my.cnf
[mysqld]
server-id=2
relay-log=relay-bin #指定从库的 “中继日志” 文件名
read_only=1 #不允许用户写入
gtid_mode=ON
enforce-gtid-consistency=ON
重启：
systemctl restart mysqld

配置复制（GTID 自动找位置）：
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.88.131',
SOURCE_USER='repl',
SOURCE_PASSWORD='Aa123456.',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;

验证是否成功：
START REPLICA;
SHOW SLAVE STATUS\G;
-- 确保：Slave_IO_Running=Yes，Slave_SQL_Running=Yes， Auto_Position=1
注意：MySQL8.0 GTID，规定 SOURCE_AUTO_POSITION=1，要写1，不能写ON

四、第二步：安装与配置 ProxySQL
1）安装 ProxySQL（CentOS）
安装依赖
yum install -y epel-release
安装
yum install -y proxysql
启动
systemctl start proxysql
systemctl enable proxysql
端口：6032（管理）、6033（应用连接）

2）登录 ProxySQL 管理端
mysql -u admin -padmin -h 127.0.0.1 -P 6032

3）添加 MySQL 节点到主机组
约定：

• hostgroup=10：写组（主库）
• hostgroup=20：读组（从库）

-- 1. 添加主库（写组10）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES (10, '192.168.88.131', 3306, 100, 1000);
-- 2. 添加从库（读组20，可加多个）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES (20, '192.168.88.132', 3306, 100, 1000);
-- 加载并保存
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4）配置应用连接用户
用户密码必须和 MySQL 一致：
INSERT INTO mysql_users (username, password, default_hostgroup, active)
VALUES ('app_user', 'Aa123456.', 10, 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

5）核心：配置读写分离规则（重点）
规则按 rule_id 从小到大匹配，精准优先。
-- 规则1：SELECT ... FOR UPDATE → 走主库（加锁读，必须一致）
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (10, 1, '^SELECT.FOR UPDATE', 10, 1);
-- 规则2：普通 SELECT → 走从库（读组20）
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (20, 1, '^SELECT', 20, 1);
-- 规则3：写操作（INSERT/UPDATE/DELETE）→ 走主库
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (30, 1, '^(INSERT|UPDATE|DELETE|REPLACE)', 10, 1);
-- 规则4：兜底（所有其他语句）→ 走主库
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (99, 1, '.', 10, 1);
-- 加载生效并持久化
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

五、第三步：验证读写分离
1）应用连接 ProxySQL
= 应用连接地址：ProxySQL IP:6033
mysql -u root -pAa123456. -h 192.168.88.131 -P 6033
2）测试 SQL 路由

• 执行 SELECT * FROM t1; → 路由到从库（20）
• 执行 SELECT * FROM t1 FOR UPDATE; → 路由到主库（10）
• 执行 INSERT INTO t1 VALUES(1); → 路由到主库（10）
  3）查看路由统计（ProxySQL 管理端）
  SELECT * FROM stats_mysql_query_digest;
  -- 观察：不同 SQL 的 hostgroup 分布

六、关键运维与高可用配置
1）健康检查（自动摘除故障节点）
ProxySQL 默认开启：

• 每 1000ms ping 后端
• 连续 3 次失败 → 标记为 OFFLINE，不再转发流量
• 恢复后自动上线
  2）多从库负载均衡
  读组（20）可添加多个从库，weight 权重控制流量比例：
  -- 从库1：权重100INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)VALUES (20, '192.168.1.11', 3306, 100);
  -- 从库2：权重50（流量少一半）INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)VALUES (20, '192.168.1.13', 3306, 50);
  3）主库高可用（MHA/Orchestrator）
• 主库宕机 → MHA 自动将某从库提升为新主库
• ProxySQL 自动感知（或脚本更新）→ 新主库加入写组（10）
  4）ProxySQL 自身高可用
• 部署多个 ProxySQL 实例
• 前端用 Keepalived/VIP 或 LVS/Nginx 做负载均衡
• 应用连接 VIP，避免 ProxySQL 单点

七、常见坑与避坑

1. 规则顺序错：SELECT FOR UPDATE 必须在普通 SELECT 之前
2. 事务内读：事务中所有读必须走主库（ProxySQL 自动处理）
3. 主从延迟：强一致读走主库；非一致读走从库
4. 用户权限：ProxySQL 用户必须和 MySQL 一致，否则认证失败
5. GTID 未开：生产必须开 GTID，避免复制中断

九、总结
MySQL 主从复制是数据同步基础，ProxySQL 是透明、高性能的读写分离路由中间件。