MySQL 主从复制部署

MySQL主从复制是一种常见的数据库高可用性解决方案，通过将主库的数据复制到从库，实现数据备份、读写分离和故障恢复。本文将详细介绍MySQL主从复制的部署、配置、监控和最佳实践。

主从复制原理

1. 复制架构

单主单从：一个主库对应一个从库
单主多从：一个主库对应多个从库
级联复制：从库同时作为其他从库的主库
双主复制：两个库互为主从

2. 复制过程

主库写入：主库将数据变更写入二进制日志（binary log）
从库读取：从库的IO线程读取主库的二进制日志
从库存储：从库将读取的二进制日志写入中继日志（relay log）
从库应用：从库的SQL线程将中继日志中的事件应用到从库

3. 复制类型

基于语句的复制（SBR）：复制SQL语句
基于行的复制（RBR）：复制数据行的变更
混合模式复制（MBR）：根据语句自动选择复制模式

部署前准备

1. 环境准备

服务器要求：
- 主库和从库硬件配置建议一致
- 网络延迟建议小于10ms
- 磁盘空间充足
软件要求：
- MySQL 5.6+ 版本
- 主库和从库版本建议一致
- 已安装MySQL服务

2. 配置文件准备

主库配置文件：

ini

[mysqld]
# 服务器ID，必须唯一
server-id = 1

# 启用二进制日志
log_bin = mysql-bin

# 二进制日志格式（建议使用 ROW）
binlog_format = ROW

# 二进制日志过期时间（天）
expire_logs_days = 7

# 同步的数据库（可选）
# binlog_do_db = db1,db2

# 忽略的数据库（可选）
# binlog_ignore_db = mysql,information_schema

从库配置文件：

ini

[mysqld]
# 服务器ID，必须唯一
server-id = 2

# 启用中继日志
relay_log = mysql-relay-bin

# 只读模式（可选，建议从库开启）
read_only = ON

# 超级用户只读（可选，MySQL 5.7+）
# super_read_only = ON

# 中继日志恢复（可选）
relay_log_recovery = ON

主库配置

1. 重启主库服务

bash

# 重启主库服务
systemctl restart mysqld

# 确认服务状态
systemctl status mysqld

2. 创建复制用户

sql

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';

-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

3. 锁定主库

sql

-- 锁定主库，防止数据写入
FLUSH TABLES WITH READ LOCK;

-- 记录主库状态
SHOW MASTER STATUS;

记录输出信息：

sql

-- 主库状态示例
SHOW MASTER STATUS;
-- 输出格式（中文说明）
-- +------------------+----------+--------------+------------------+-------------------+
-- | 日志文件名称     | 日志位置 | 同步的数据库  | 忽略的数据库     | 已执行的GTID集合   |
-- +------------------+----------+--------------+------------------+-------------------+
-- | mysql-bin.000001 |  107     |              |                  |                   |
-- +------------------+----------+--------------+------------------+-------------------+

4. 备份主库数据

bash

# 使用 mysqldump 备份
mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > full_backup.sql

# 或使用 XtraBackup 备份
xtrabackup --backup --user=root --password=password --target-dir=/backup/full

5. 解锁主库

sql

-- 解锁主库
UNLOCK TABLES;

从库配置

1. 重启从库服务

bash

# 重启从库服务
systemctl restart mysqld

# 确认服务状态
systemctl status mysqld

2. 恢复主库数据

bash

# 使用 mysqldump 恢复
mysql -u root -p < full_backup.sql

# 或使用 XtraBackup 恢复
xtrabackup --copy-back --user=root --password=password --target-dir=/backup/full --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

3. 配置从库复制

sql

-- 配置从库连接主库
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

-- 启动从库复制
START SLAVE;

-- 检查从库状态
SHOW SLAVE STATUS\G;

关键状态信息：
- Slave_IO_Running：IO线程状态，应为 Yes
- Slave_SQL_Running：SQL线程状态，应为 Yes
- Seconds_Behind_Master：复制延迟，应为 0 或较小值

GTID 复制配置

1. 配置文件修改

主库配置：

ini

[mysqld]
# 启用 GTID
gtid_mode = ON
enforce_gtid_consistency = ON

从库配置：

ini

[mysqld]
# 启用 GTID
gtid_mode = ON
enforce_gtid_consistency = ON

2. 重启数据库服务

bash

# 重启主库和从库服务
systemctl restart mysqld

3. 配置 GTID 复制

sql

-- 主库创建复制用户（如果未创建）
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 从库配置 GTID 复制
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;

-- 启动从库复制
START SLAVE;

-- 检查从库状态
SHOW SLAVE STATUS\G;

主从复制监控

1. 内置监控命令

sql

-- 检查从库状态
SHOW SLAVE STATUS\G;

-- 检查主库二进制日志状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- 检查从库中继日志状态
SHOW RELAYLOG EVENTS;

-- 检查复制线程状态
SHOW PROCESSLIST;

2. 监控脚本

简单监控脚本：

bash

#!/bin/bash

# MySQL 主从复制监控脚本
USER="root"
PASSWORD="password"
HOST="localhost"

# 检查从库状态
SLAVE_STATUS=$(mysql -u $USER -p$PASSWORD -h $HOST -e "SHOW SLAVE STATUS\G")

# 提取关键状态
IO_RUNNING=$(echo "$SLAVE_STATUS" | grep Slave_IO_Running | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep Slave_SQL_Running | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep Seconds_Behind_Master | awk '{print $2}')

# 输出监控结果
echo "当前时间: $(date '+%Y-%m-%d %H:%M:%S')"
echo "IO线程状态: $IO_RUNNING"
echo "SQL线程状态: $SQL_RUNNING"
echo "复制延迟: $SECONDS_BEHIND 秒"

# 发送告警（示例）
if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
  echo "主从复制异常，请检查！" | mail -s "MySQL 主从复制告警" admin@example.com
fi

3. 第三方监控工具

Percona Monitoring and Management (PMM)：
- 提供主从复制监控面板
- 支持告警功能
- 可视化复制延迟
Prometheus + Grafana：
- 使用 mysqld_exporter 收集复制指标
- 自定义监控面板
- 灵活的告警规则

主从复制故障处理

1. IO 线程故障

常见原因：
- 网络连接问题
- 主库二进制日志不存在
- 复制用户权限问题

解决方法：

sql

-- 停止从库复制
STOP SLAVE IO_THREAD;

-- 检查主库二进制日志
SHOW BINARY LOGS;

-- 重新配置主库连接
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

-- 启动 IO 线程
START SLAVE IO_THREAD;

-- 检查 IO 线程状态
SHOW SLAVE STATUS\G;

2. SQL 线程故障

常见原因：
- 从库数据与主库不一致
- 从库存在主库没有的表或数据
- 表结构不兼容

解决方法：

sql

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

-- 或重新同步数据
STOP SLAVE;
-- 重新恢复主库数据
START SLAVE;

3. 复制延迟过大

常见原因：
- 主库写入压力大
- 从库硬件性能差
- 网络延迟高
- 大事务
解决方法：
- 优化主库写入性能
- 提升从库硬件配置
- 优化网络连接
- 拆分大事务
- 使用并行复制

读写分离配置

1. 应用层读写分离

配置示例（PHP）：

php

// 主库连接
$master = new PDO("mysql:host=master_host;dbname=database", "user", "password");

// 从库连接
$slave = new PDO("mysql:host=slave_host;dbname=database", "user", "password");

// 写操作使用主库
$master->exec("INSERT INTO table (column) VALUES ('value')");

// 读操作使用从库
$stmt = $slave->query("SELECT * FROM table");

2. 中间件读写分离

MySQL Router：

ini

# MySQL Router 配置
[routing:primary]
bind_address = 0.0.0.0
bind_port = 6446
destinations = master_host:3306
mode = read-write

[routing:replica]
bind_address = 0.0.0.0
bind_port = 6447
destinations = slave_host:3306
mode = read-only

MaxScale：

ini

# MaxScale 配置
[maxscale]
threads=auto

[server1]
type=server
address=master_host
port=3306
protocol=MySQLBackend

[server2]
type=server
address=slave_host
port=3306
protocol=MySQLBackend

[MySQL-Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=monitor
password=password
monitor_interval=10000

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale
password=password

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MySQLClient
port=4006

最佳实践

1. 配置最佳实践

使用 GTID 复制：简化复制配置和故障恢复
设置合理的二进制日志过期时间：避免磁盘空间不足
开启 relay_log_recovery：防止中继日志损坏
从库设置只读模式：防止误写入
使用基于行的复制：提高复制可靠性

2. 部署最佳实践

主库和从库硬件配置一致：避免性能瓶颈
使用专用网络：减少网络延迟
定期备份从库：确保数据安全
监控复制状态：及时发现问题
测试故障切换：确保故障恢复流程有效

3. 维护最佳实践

定期检查复制状态：每周至少检查一次
定期清理二进制日志：避免磁盘空间不足
定期重建从库：避免复制延迟积累
监控复制延迟：设置合理的告警阈值
测试恢复流程：每月至少测试一次

不同MySQL版本的差异

1. MySQL 5.6 vs 5.7

GTID 支持：
- MySQL 5.6：实验性支持
- MySQL 5.7：正式支持，推荐使用
并行复制：
- MySQL 5.6：基于库的并行复制
- MySQL 5.7：基于组提交的并行复制

2. MySQL 5.7 vs 8.0

复制增强：
- MySQL 8.0：增强了并行复制，支持基于写入集的并行复制
- MySQL 8.0：优化了 GTID 复制性能
- MySQL 8.0：新增了复制过滤器
复制监控：
- MySQL 8.0：增强了 Performance Schema 中的复制监控
- MySQL 8.0：新增了复制延迟监控视图

常见问题（FAQ）

Q1: 主从复制延迟如何解决？

A1: 解决复制延迟的方法：

优化主库写入性能
提升从库硬件配置
使用并行复制
减少大事务
优化网络连接

Q2: 如何切换主从角色？

A2: 手动切换主从角色的步骤：

停止从库写入
等待从库追上主库
停止主库复制
将从库设置为主库
将原主库设置为从库
启动复制

Q3: 如何添加新的从库？

A3: 添加新从库的步骤：

备份主库数据
恢复数据到新从库
配置新从库复制
启动复制
验证复制状态

Q4: 主从复制是否会影响主库性能？

A4: 主从复制对主库性能的影响：

主库需要写入二进制日志，增加IO开销
从库读取二进制日志，增加网络开销
建议主库使用高性能存储和网络

Q5: 如何验证主从数据一致性？

A5: 验证主从数据一致性的方法：

使用 pt-table-checksum 工具
对比表行数
对比表的 CHECKSUM 值
定期全量备份验证

Q6: 主从复制支持哪些数据类型？

A6: 主从复制支持所有MySQL数据类型，包括：

数值类型
字符串类型
日期和时间类型
BLOB和TEXT类型
JSON类型

Q7: 如何处理主库二进制日志丢失？

A7: 处理方法：

检查主库二进制日志配置
确认二进制日志是否被误删
重新同步从库数据
优化二进制日志管理

Q8: 主从复制是否支持DDL语句？

A8: 是的，主从复制支持DDL语句，包括：

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX

故障排查

1. 常见错误及解决方法

错误1："Slave_IO_Running: Connecting"
- 原因：网络连接问题或主库配置错误
- 解决方法：检查网络连接和主库配置
错误2："Got fatal error 1236 from master when reading data from binary log"
- 原因：主库二进制日志丢失或损坏
- 解决方法：重新同步从库数据
错误3："Duplicate entry 'xxx' for key 'PRIMARY'"
- 原因：从库数据与主库不一致
- 解决方法：跳过错误或重新同步数据

2. 诊断工具

pt-slave-restart：自动重启失败的复制线程
bash
```
pt-slave-restart --user=root --password=password
```

pt-table-checksum：检查主从数据一致性

bash

pt-table-checksum --user=root --password=password h=master_host

pt-table-sync：修复主从数据不一致

bash

pt-table-sync --execute --sync-to-master h=slave_host,u=root,p=password

通过合理配置和维护MySQL主从复制，可以提高数据库的可用性和可靠性，实现数据备份、读写分离和故障恢复。主从复制是MySQL高可用性架构的基础，掌握其部署、配置和故障处理对于数据库运维至关重要。

MySQL 主从复制部署 ​

主从复制原理 ​

1. 复制架构 ​

2. 复制过程 ​

3. 复制类型 ​

部署前准备 ​

1. 环境准备 ​

2. 配置文件准备 ​

主库配置 ​

1. 重启主库服务 ​

2. 创建复制用户 ​

3. 锁定主库 ​

4. 备份主库数据 ​

5. 解锁主库 ​

从库配置 ​

1. 重启从库服务 ​

2. 恢复主库数据 ​

3. 配置从库复制 ​

GTID 复制配置 ​

1. 配置文件修改 ​

2. 重启数据库服务 ​

3. 配置 GTID 复制 ​

主从复制监控 ​

1. 内置监控命令 ​

2. 监控脚本 ​

3. 第三方监控工具 ​

主从复制故障处理 ​

1. IO 线程故障 ​

2. SQL 线程故障 ​

3. 复制延迟过大 ​

读写分离配置 ​

1. 应用层读写分离 ​

2. 中间件读写分离 ​

最佳实践 ​

1. 配置最佳实践 ​

2. 部署最佳实践 ​

3. 维护最佳实践 ​

不同MySQL版本的差异 ​

1. MySQL 5.6 vs 5.7 ​

2. MySQL 5.7 vs 8.0 ​

常见问题（FAQ） ​

Q1: 主从复制延迟如何解决？ ​

Q2: 如何切换主从角色？ ​

Q3: 如何添加新的从库？ ​

Q4: 主从复制是否会影响主库性能？ ​

Q5: 如何验证主从数据一致性？ ​

Q6: 主从复制支持哪些数据类型？ ​

Q7: 如何处理主库二进制日志丢失？ ​

Q8: 主从复制是否支持DDL语句？ ​

故障排查 ​

1. 常见错误及解决方法 ​

2. 诊断工具 ​

MySQL 主从复制部署

主从复制原理

1. 复制架构

2. 复制过程

3. 复制类型

部署前准备

1. 环境准备

2. 配置文件准备

主库配置

1. 重启主库服务

2. 创建复制用户

3. 锁定主库

4. 备份主库数据

5. 解锁主库

从库配置

1. 重启从库服务

2. 恢复主库数据

3. 配置从库复制

GTID 复制配置

1. 配置文件修改

2. 重启数据库服务

3. 配置 GTID 复制

主从复制监控

1. 内置监控命令

2. 监控脚本

3. 第三方监控工具

主从复制故障处理

1. IO 线程故障

2. SQL 线程故障

3. 复制延迟过大

读写分离配置

1. 应用层读写分离

2. 中间件读写分离

最佳实践

1. 配置最佳实践

2. 部署最佳实践

3. 维护最佳实践

不同MySQL版本的差异

1. MySQL 5.6 vs 5.7

2. MySQL 5.7 vs 8.0

常见问题（FAQ）

Q1: 主从复制延迟如何解决？

Q2: 如何切换主从角色？

Q3: 如何添加新的从库？

Q4: 主从复制是否会影响主库性能？

Q5: 如何验证主从数据一致性？

Q6: 主从复制支持哪些数据类型？

Q7: 如何处理主库二进制日志丢失？

Q8: 主从复制是否支持DDL语句？

故障排查

1. 常见错误及解决方法

2. 诊断工具