centos mysql读写分离策略

CentOS 上 MySQL 读写分离策略

一 架构与复制模式选择

• 基础架构建议采用一主多从：主库（Master）承担写入与强一致读，从库（Slave）承担大部分读流量；必要时可引入延迟从库用于审计/回滚。复制链路基于主库的二进制日志（binlog）与从库的中继日志（relay log）。复制模式有三种：
  1. 异步复制：主库不等待从库应答，性能最好但一致性最弱；
  2. 半同步复制：主库至少等待一个从库确认，折中方案；
  3. 全同步复制：所有从库确认才返回，一致性最强但延迟最高。
     在 CentOS 上常见做法是主库开启 binlog，从库配置 server-id 与 relay-log，建立复制链路后再在代理层做读写路由。

二 数据面配置要点

• 主库配置（示例 /etc/my.cnf）
  • 启用唯一 server-id 与 log_bin；可按需设置仅复制的库（如 binlog_do_db），或全局复制后由从库过滤。
  • 创建复制专用账号并授予 REPLICATION SLAVE 权限；导出一致性位点（File/Position）供从库接入。
  • 示例要点：
    • server-id=1；log_bin=/var/log/mysql/mysql-bin.log
    • CREATE USER ‘replicator’@‘%’ IDENTIFIED BY ‘pwd’; GRANT REPLICATION SLAVE ON . TO ‘replicator’@‘%’;
    • SHOW MASTER STATUS; 记录 File/Position。
• 从库配置（示例 /etc/my.cnf）
  • 设置唯一 server-id 与 relay_log；可开启 read_only=1 防止业务误写（SUPER 权限仍可写）。
  • 使用 CHANGE MASTER TO 接入主库（填入 Master_Host/Port、复制账号、File/Position），启动复制并检查：
    • Slave_IO_Running=Yes、Slave_SQL_Running=Yes。
• 一致性与时序
  • 建议在主从间启用 NTP 时间同步，避免复制元数据与时序异常。
  • 对强一致读场景，可在事务内强制走主库（如 SELECT … FOR UPDATE）。

三 流量路由与中间件选型

• 应用内直连路由
  • 为写操作配置主库专用账号，为读操作配置从库只读账号；在 DAO/ORM 或连接池层按操作类型选择数据源。优点是简单可控，缺点是耦合应用。
• 代理/中间件路由
  • MySQL Proxy：通过 Lua 脚本（如 rw-splitting.lua）实现读写分离，配置读写后端与路由规则，适合轻量场景与验证性部署。
  • ProxySQL：更成熟的生产级方案，支持基于规则的查询路由、读写分离、健康检查、读写主机组（HostGroup）与运行时动态加载。常见做法：写默认到 hostgroup 1（主库），将 SELECT 路由到 hostgroup 2（从库），对 SELECT … FOR UPDATE 强制路由到主库；应用连接 6033，管理端 6032。
  • MaxScale：亦可完成读写分离与故障切换，适合与 MariaDB/MySQL 生态配合。

四 路由规则与一致性策略

• 路由粒度与策略
  • 按语句特征：将 SELECT 定向到从库，INSERT/UPDATE/DELETE 定向到主库；对 SELECT … FOR UPDATE 强制走主库以保证锁定与一致性。
  • 按事务边界：事务内保持同一后端，避免跨库/跨节点读导致不可预期的结果（ProxySQL 可通过 transaction_persistent 维持）。
  • 读写一致性级别：对强一致读使用“主库读/事务内读”；对最终一致读使用“从库读”，结合业务容忍的复制延迟设置超时与降级策略。
• 故障与退化
  • 从库不可用或延迟过高时，将读流量自动回退到主库；必要时触发告警与摘除异常从库。
  • 复制中断/错误时，优先修复根因（数据不一致、DDL 冲突等），再恢复复制链路，避免盲目跳过事件导致数据空洞。

五 监控 高可用与备份

• 复制与健康监控
  • 定期巡检 SHOW SLAVE STATUS（IO/SQL 线程、Seconds_Behind_Master、Last_Error）；在 ProxySQL 中启用 monitor 账号做后端健康检查，观察连接、查询与错误统计。
• 高可用与切换
  • 结合 MHA、Orchestrator 或手动提升从库为主库（切换前确保数据追平与一致性校验），并更新中间件的主从拓扑与路由规则。
• 备份与恢复
  • 主库定期做物理/逻辑备份（如 Percona XtraBackup、mysqldump），从库可用于快速重建；发生误删/回滚时基于备份与 binlog 做时间点恢复（PITR）。
• 安全与合规
  • 复制与业务链路启用 SSL/TLS 加密，最小权限分配，限制来源网段，审计关键操作与复制异常。