MySQL 主从复制 + 读写分离 

一、核心概念

1. 主从复制

• 主库（Master）：负责写入，记录数据变更到二进制日志（binlog）。
• 从库（Slave）：负责读取，通过 I/O 线程拉取 binlog，SQL 线程重放，实现数据同步。
• 作用：读写分离、负载均衡、数据备份、故障切换。

2. 读写分离

• 写操作只走主库，读操作分发到从库。
• 常用方案：应用内直连区分、中间件代理（MyCat、Sharding-JDBC、MaxScale、ProxySQL）。

---

二、主从复制原理

1. 主库开启 binlog，所有 DDL/DML 写入 binlog。
2. 从库启动 I/O 线程，连接主库请求 binlog。
3. 主库推送 binlog 到从库，从库写入中继日志（relay log）。
4. 从库 SQL 线程读取 relay log，重放 SQL，保持数据一致。

---

三、主从复制搭建步骤

1. 环境准备

• 主从服务器时间同步
• 防火墙 / 安全组开放 3306
• MySQL 版本尽量一致

2. 主库配置

ini

[mysqld]
server-id = 1                # 唯一ID，主从不能相同
log_bin = mysql-bin           # 开启binlog
binlog_format = ROW           # 推荐行模式
expire_logs_days = 7          # 日志自动清理
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

重启 MySQL：systemctl restart mysqld

3. 主库创建复制账号

sql

CREATE USER 'repl'@'192.168.x.%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.x.%';
FLUSH PRIVILEGES;

4. 查看主库状态

sql

FLUSH TABLES WITH READ LOCK;  -- 锁表防止数据变化
SHOW MASTER STATUS;
UNLOCK TABLES;

5. 从库配置

ini

[mysqld]
server-id = 2
relay_log = relay-bin
read_only = 1                 # 普通用户只读，超级用户不受限
log_slave_updates = 1         # 级联复制可选

重启 MySQL。

6. 从库连接主库

sql

CHANGE MASTER TO
MASTER_HOST='192.168.x.x',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',  -- 主库查到的
MASTER_LOG_POS=xxx;                 -- 主库查到的

START SLAVE;

7. 检查从库状态

sql

SHOW SLAVE STATUS\G

看到：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes即主从正常。

---

四、常见主从问题

1. 主键冲突、SQL 报错

   • 跳过错误：SET GLOBAL sql_slave_skip_counter = 1;
   • 或使用 slave_skip_errors

2. 主从延迟

   • 大事务、从库性能差、网络慢
   • 优化：并行复制、分库分表、升级硬件

3. binlog 丢失

   • 主库日志清理过快
   • 重新备份 + 重做主从

---

五、读写分离实现方式

1. 应用代码层（简单）

• 写：dataSourceMaster
• 读：dataSourceSlave
• 优点：无额外组件；缺点：耦合高、扩展麻烦

2. 中间件代理（推荐）

• Sharding-JDBC：Java 生态，轻量，无额外部署
• MyCat：功能全，支持分库分表
• ProxySQL / MaxScale：专业 MySQL 代理，性能高

3. 通用读写分离策略

• 强制强一致读：走主库
• 普通查询：走从库
• 统计 / 报表：专用从库
• 写后立即读：走主库避免延迟问题

---

六、生产注意事项

1. 主库避免大事务、批量更新。
2. 从库至少 2 台，避免单点。
3. 定期校验主从数据一致（pt-table-checksum）。
4. 主从延迟监控，超过阈值告警。
5. 主库高可用搭配 MGR、MHA、Orchestrator。