MySQL 主从复制详解

一、主从复制核心原理

工作流程

核心组件

1. Binary Log (Binlog)

   • 记录所有数据库结构变更和数据修改
   • 格式：STATEMENT(语句)、ROW(行数据)、MIXED(混合)

2. Relay Log

   • 从库临时存储主库发送的Binlog事件
   • 格式与Binlog相同

3. 复制线程

   • I/O Thread：连接主库获取Binlog
   • SQL Thread：执行Relay Log中的事件

---

二、主从复制配置全流程

1. 主库配置 (my.cnf)

[mysqld]
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
binlog-format = ROW
expire_logs_days = 7
max_binlog_size = 100M
binlog_cache_size = 1M
sync_binlog = 1

# 需要复制的数据库
binlog-do-db = shop_db
binlog-do-db = user_db

# 忽略复制的数据库
binlog-ignore-db = test

2. 创建复制账户

CREATE USER 'repl'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3. 获取主库状态

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

输出：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 785      | shop_db      | test             |
+------------------+----------+--------------+------------------+

4. 从库配置 (my.cnf)

[mysqld]
server-id = 2
relay-log = /var/lib/mysql/relay-bin
read-only = ON
replicate-do-db = shop_db
replicate-ignore-db = temp_db
log_slave_updates = ON  # 级联复制时启用

5. 配置从库连接主库

CHANGE MASTER TO
MASTER_HOST = '192.168.1.100',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'SecurePass123!',
MASTER_LOG_FILE = 'mysql-bin.000003',
MASTER_LOG_POS = 785;

6. 启动复制

START SLAVE;
SHOW SLAVE STATUS\G

---

三、复制模式对比

模式	原理	优点	缺点	适用场景
异步复制	主库提交后立即响应客户端	性能最佳	数据可能丢失	读写分离、报表系统
半同步复制	至少一个从库确认后才响应客户端	数据更安全	性能下降20-30%	金融交易系统
组复制	基于Paxos协议的多主同步	真正高可用、自动故障转移	配置复杂、网络要求高	云数据库、高可用集群

半同步复制配置

# 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  # 1秒超时

# 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

# 重启复制
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

---

四、主从复制监控与管理

关键监控指标

SHOW SLAVE STATUS\G

重点关注：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0 # 复制延迟秒数
• Last_IO_Error:
• Last_SQL_Error:

性能监控脚本

import mysql.connector
import time

def monitor_replication():
    while True:
        master = mysql.connector.connect(host='192.168.1.100', user='monitor', password='monitor_pass')
        slave = mysql.connector.connect(host='192.168.1.101', user='monitor', password='monitor_pass')
        
        # 主库状态
        m_cursor = master.cursor()
        m_cursor.execute("SHOW MASTER STATUS")
        master_log, master_pos = m_cursor.fetchone()[0:2]
        
        # 从库状态
        s_cursor = slave.cursor()
        s_cursor.execute("SHOW SLAVE STATUS")
        slave_status = dict(zip(s_cursor.column_names, s_cursor.fetchone()))
        
        print(f"当前延迟: {slave_status['Seconds_Behind_Master']}秒")
        print(f"主库位置: {master_log}:{master_pos}")
        print(f"从库位置: {slave_status['Relay_Master_Log_File']}:{slave_status['Exec_Master_Log_Pos']}")
        
        if slave_status['Last_SQL_Error']:
            print(f"SQL错误: {slave_status['Last_SQL_Error']}")
        
        time.sleep(10)

if __name__ == "__main__":
    monitor_replication()

日常维护命令

-- 暂停复制
STOP SLAVE;

-- 跳过错误（谨慎使用）
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 重置复制
RESET SLAVE ALL;

-- 切换主库
CHANGE MASTER TO MASTER_HOST='new_master_ip', ...;

---

五、主从复制故障处理

常见问题及解决方案

问题	现象	解决方案
主键冲突	SQL线程停止，Last_SQL_Error显示冲突	手动跳过或删除冲突数据
网络中断	IO线程停止，连接超时	检查网络后重启复制
Binlog损坏	IO线程位置不匹配	重新同步数据
从库写入	数据不一致	启用read-only，检查权限
大事务延迟	Seconds_Behind_Master持续高位	拆分大事务，使用并行复制

数据一致性校验

# 使用pt-table-checksum
pt-table-checksum \
  --host=192.168.1.100 \
  --user=checksum_user \
  --password=CheckPass123! \
  --databases=shop_db \
  --no-check-binlog-format

输出不一致表：

Differences on slave2
TABLE CHUNK CNT_DIFF CRC_DIFF
shop_db.orders 1  -1 1

修复不一致：

pt-table-sync --execute h=192.168.1.100,D=shop_db,t=orders h=192.168.1.101

---

六、主从复制高级架构

级联复制架构

配置要点：

• 中间从库启用log_slave_updates
• 注意延迟累积问题

双主复制架构

配置要点：

• 设置auto_increment_offset和auto_increment_increment
• 启用半同步复制
• 示例配置：

  # Server A
  auto_increment_increment = 2
  auto_increment_offset = 1
  
  # Server B
  auto_increment_increment = 2
  auto_increment_offset = 2
  

---

七、主从复制优化策略

性能优化

1. 并行复制（MySQL 5.6+）

   STOP SLAVE;
   SET GLOBAL slave_parallel_workers = 4;  # CPU核心数
   SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
   START SLAVE;
   

2. Binlog优化

   # 批量提交
   sync_binlog = 100
   binlog_group_commit_sync_delay = 1000  # 1ms延迟
   
   # 压缩Binlog（MySQL 8.0+）
   binlog_transaction_compression = ON
   

3. 网络优化

   # 增加复制缓冲区
   slave_net_timeout = 60
   slave_compressed_protocol = ON
   

延迟优化方案

方案	原理	效果
并行复制	多线程应用Relay Log	提升5-10倍
MTS多线程	按库/事务并行	提升3-8倍
Memcached前置	写缓存缓冲	减少直接写压力
升级硬件	SSD替代HDD	提升IO性能50%

---

八、主从切换与故障转移

手动切换流程

# 1. 停止主库写入
mysql -h master -e "FLUSH TABLES WITH READ LOCK"

# 2. 提升从库
mysql -h slave1 -e "STOP SLAVE; RESET SLAVE ALL"
mysql -h slave1 -e "SET GLOBAL read_only=OFF"

# 3. 修改应用配置
sed -i 's/master_ip/slave1_ip/g' /app/config.ini

# 4. 原主库设置为新从库
mysql -h master -e "UNLOCK TABLES"
mysql -h master -e "CHANGE MASTER TO MASTER_HOST='slave1_ip'..."

使用MHA自动故障转移

# 安装MHA
$ cpan install DBD::mysql
$ git clone https://github.com/yoshinorim/mha4mysql-manager.git

# 配置manager
[server default]
manager_workdir=/var/log/mha
manager_log=/var/log/mha/manager.log
ssh_user=root

[server1]
hostname=master_ip

[server2]
hostname=slave1_ip
candidate_master=1

# 启动监控
$ masterha_manager --conf=/etc/mha.cnf

---

九、主从复制最佳实践

配置清单

1. 必须配置

   server-id = 唯一ID
   log-bin = /path/to/binlog
   binlog_format = ROW
   expire_logs_days = 7
   read_only = ON  # 从库
   

2. 推荐配置

   gtid_mode = ON
   enforce_gtid_consistency = ON
   slave_parallel_workers = 4
   slave_preserve_commit_order = ON
   

安全规范

1. 复制账号使用最小权限原则
2. 启用SSL加密复制连接

   CHANGE MASTER TO MASTER_SSL=1, 
   MASTER_SSL_CA='/etc/mysql/ca.pem',
   MASTER_SSL_CERT='/etc/mysql/client-cert.pem',
   MASTER_SSL_KEY='/etc/mysql/client-key.pem';
   

3. 定期清理过期Binlog

   PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;
   

备份策略

# 物理备份从库
$ xtrabackup --backup --slave-info --target-dir=/backup/$(date +%F)

# 逻辑备份
$ mysqldump --single-transaction --master-data=2 shop_db > shop_db.sql

---

十、MySQL 8.0 新特性

1. 增强的GTID

   • 即时添加新从库无需停止主库

   SET GLOBAL gtid_mode = ON_PERMISSIVE;
   

2. 二进制日志加密

   INSTALL COMPONENT "file://component_binlog_encryption";
   SET GLOBAL binlog_encryption = ON;
   

3. 克隆插件

   INSTALL PLUGIN clone SONAME 'mysql_clone.so';
   CLONE LOCAL DATA DIRECTORY = '/path/to/slave';
   

4. 性能提升

   • 并行复制性能提升30%
   • 写锁减少50%

---

通过合理配置主从复制，可实现：

• 📊 读写分离：主库写，从库读
• 🛡️ 数据安全：多副本保障
• ⚡ 负载均衡：分散查询压力
• 🚀 高可用：故障快速切换

建议生产环境采用：

异步复制 + GTID + 并行复制 + SSL加密 + 自动故障转移