印刻数据库

外观

MySQL 基本主从复制配置 ​

主从复制工作原理 ​

复制流程 ​

核心组件 ​

  • 主库（Master）：接收写入请求，记录二进制日志
  • 从库（Slave）：读取主库二进制日志，执行相同的更新
  • I/O 线程：从主库读取二进制日志，写入本地中继日志
  • SQL 线程：读取中继日志，执行更新操作

主从复制前提条件 ​

软件要求 ​

  • MySQL 版本：主从库版本兼容
    • 推荐使用相同版本
    • 从库版本可以高于主库，但不建议低于主库
    • 版本差异应在一个主版本内（如 5.7 → 8.0 可能需要特殊处理）

硬件要求 ​

  • 主库：

    • 足够的 CPU 和内存处理写入请求
    • 高速磁盘用于存储二进制日志
    • 足够的带宽用于复制

  • 从库：

    • 与主库相当的硬件配置
    • 足够的存储空间用于存储数据和中继日志
    • 高速网络连接到主库

配置要求 ​

  • 服务器 ID：每个服务器必须有唯一的 server_id
  • 二进制日志：主库必须启用二进制日志
  • 中继日志：从库自动创建中继日志
  • 用户权限：主库需要创建具有复制权限的用户

主库配置 ​

配置文件修改 ​

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

# 设置服务器 ID（必须唯一）
server_id = 1

# 使用 row 格式（推荐）
binlog_format = row

# 二进制日志保留时间（秒）
# expire_logs_days = 7  # MySQL 8.0 后推荐使用 binlog_expire_logs_seconds
binlog_expire_logs_seconds = 604800

# 二进制日志缓存大小
binlog_cache_size = 32m
max_binlog_cache_size = 512m

# 单个二进制日志大小限制
max_binlog_size = 1024m

# 同步写入二进制日志（可选，提高可靠性）
sync_binlog = 1

# 启用 binlog_checksum（MySQL 5.6+）
binlog_checksum = CRC32

# 跳过错误（生产环境不推荐）
# slave_skip_errors = 1062,1032

重启主库 ​

bash
# 使用 systemd 重启
systemctl restart mysqld

# 使用 service 重启
service mysqld restart

创建复制用户 ​

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

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

-- 刷新权限
FLUSH PRIVILEGES;

锁定主库并备份 ​

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

-- 查看主库状态，记录文件名和位置
SHOW MASTER STATUS;

输出示例：

文件名称: mysql-bin.000001
位置: 154
记录数据库: 
忽略数据库: 
已执行GTID集合:

备份主库数据 ​

bash
# 使用 mysqldump 备份
mysqldump --single-transaction --master-data=2 --all-databases --triggers --routines --events > master_backup.sql

# 使用 xtrabackup 备份（推荐）
xtrabackup --backup --user=root --password=password --target-dir=/backup/mysql

解锁主库 ​

sql
-- 解锁主库，允许数据写入
UNLOCK TABLES;

从库配置 ​

配置文件修改 ​

ini
# 启用二进制日志（级联复制需要）
log_bin = mysql-bin

# 设置服务器 ID（必须与主库不同）
server_id = 2

# 使用 row 格式
binlog_format = row

# 中继日志相关配置
relay_log = relay-bin
relay_log_index = relay-bin.index

# 从库只读（可选，提高安全性）
read_only = ON

# 从库超级用户只读（可选）
super_read_only = ON

# 自动清理中继日志
relay_log_recovery = ON

# 中继日志保留时间
relay_log_purge = ON
relay_log_space_limit = 4G

# 复制线程配置
slave_parallel_workers = 4  # MySQL 5.7+ 支持并行复制
slave_parallel_type = LOGICAL_CLOCK  # 并行复制类型

# 跳过错误（生产环境不推荐）
# slave_skip_errors = 1062,1032

重启从库 ​

bash
# 使用 systemd 重启
systemctl restart mysqld

# 使用 service 重启
service mysqld restart

恢复主库数据到从库 ​

bash
# 使用 mysqldump 备份恢复
mysql -u root -p < master_backup.sql

# 使用 xtrabackup 备份恢复
xtrabackup --prepare --target-dir=/backup/mysql
xtrabackup --copy-back --target-dir=/backup/mysql --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

配置从库连接主库 ​

sql
-- 停止从库复制线程
STOP SLAVE;

-- 重置从库复制信息
RESET SLAVE;

-- 配置主库连接信息
CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',  -- 从 SHOW MASTER STATUS 获取
    MASTER_LOG_POS = 154;  -- 从 SHOW MASTER STATUS 获取

-- 启动从库复制线程
START SLAVE;

验证从库状态 ​

sql
-- 查看从库状态
SHOW SLAVE STATUS\G

关键状态指标：

  • Slave_IO_Running：I/O 线程状态，应为 Yes
  • Slave_SQL_Running：SQL 线程状态，应为 Yes
  • Seconds_Behind_Master：从库落后主库的秒数，应为 0
  • Master_Log_File 和 Read_Master_Log_Pos：I/O 线程读取的主库日志位置
  • Relay_Master_Log_File 和 Exec_Master_Log_Pos：SQL 线程执行的主库日志位置

主从复制管理 ​

查看主库状态 ​

sql
-- 查看主库状态
SHOW MASTER STATUS;

-- 查看二进制日志列表
SHOW BINARY LOGS;

-- 查看二进制日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 查看当前正在使用的二进制日志
SHOW MASTER STATUS LIKE 'File';

查看从库状态 ​

sql
-- 查看从库状态
SHOW SLAVE STATUS\G

-- 查看从库线程状态
SHOW PROCESSLIST;

-- 查看从库的 GTID 执行情况（如果启用 GTID）
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

暂停和恢复复制 ​

sql
-- 暂停复制
STOP SLAVE;

-- 暂停特定线程
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;

-- 恢复复制
START SLAVE;

-- 恢复特定线程
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;

重置复制 ​

sql
-- 重置从库复制信息
RESET SLAVE;

-- 重置从库所有复制信息，包括 GTID
RESET SLAVE ALL;

-- 重置主库二进制日志
RESET MASTER;

跳过错误 ​

sql
-- 跳过一个错误
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;

-- 通过配置文件跳过特定错误
slave_skip_errors = 1062,1032

主从复制常见问题及解决方案 ​

复制延迟 ​

  • 原因：

    • 从库硬件性能不足
    • 主库写入压力过大
    • 从库有长时间运行的查询
    • 网络延迟
    • 并行复制配置不当

  • 解决方案：

    • 升级从库硬件
    • 优化主库写入性能
    • 避免在从库执行长时间查询
    • 优化网络连接
    • 调整 slave_parallel_workers 参数
    • 使用半同步或异步复制

复制错误 ​

  • 常见错误代码：

    • 1062：主键冲突
    • 1032：找不到记录
    • 1146：表不存在
    • 1007：数据库已存在

  • 解决方案：

    • 检查数据一致性
    • 修复数据冲突
    • 跳过错误（生产环境谨慎使用）
    • 重新同步数据

从库 I/O 线程错误 ​

  • 原因：

    • 主库连接信息错误
    • 复制用户权限问题
    • 主库二进制日志文件不存在
    • 网络连接问题

  • 解决方案：

    • 检查主库连接信息
    • 验证复制用户权限
    • 检查主库二进制日志文件
    • 测试网络连接

从库 SQL 线程错误 ​

  • 原因：

    • 数据不一致
    • 表结构不一致
    • 权限问题
    • 存储引擎不兼容

  • 解决方案：

    • 检查数据一致性
    • 修复表结构
    • 验证权限
    • 确保存储引擎兼容

主从复制最佳实践 ​

配置最佳实践 ​

  • 使用 row 格式：提高复制可靠性和一致性
  • 启用中继日志自动恢复：减少复制故障恢复时间
  • 设置合理的二进制日志保留时间：避免磁盘空间耗尽
  • 从库设置只读：提高安全性，防止误写入
  • 使用并行复制：提高从库复制速度

部署最佳实践 ​

  • 主从库版本一致：避免版本差异导致的兼容性问题
  • 主从库配置一致：确保相同的字符集、时区等配置
  • 使用独立的复制用户：提高安全性，便于权限管理
  • 定期备份主库和从库：确保数据安全
  • 测试复制功能：定期测试主从复制是否正常

监控最佳实践 ​

  • 监控复制状态：

    • 复制线程运行状态
    • 复制延迟
    • 二进制日志和中继日志状态

  • 监控工具：

    • MySQL Enterprise Monitor
    • Prometheus + Grafana
    • Zabbix
    • 自定义脚本

维护最佳实践 ​

  • 定期检查数据一致性：

    • 使用 pt-table-checksum 工具
    • 使用 mysqldiff 工具比较表结构

  • 定期清理二进制日志和中继日志：

    • 设置合理的保留时间
    • 定期手动清理旧日志

  • 定期更换复制用户密码：提高安全性

主从复制升级 ​

从传统复制升级到 GTID 复制 ​

  • 升级步骤：

    1. 确保主从库版本支持 GTID
    2. 配置 GTID 参数
    3. 逐步启用 GTID 模式
    4. 切换到 GTID 复制

  • 具体操作：

    sql
    -- 主从库都执行
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL enforce_gtid_consistency = WARN;

SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = ON_PERMISSIVE;

-- 等待所有事务完成
-- 检查没有使用传统复制的事务
SELECT COUNT(*) FROM performance_schema.replication_applier_status WHERE CHANNEL_NAME = '';

-- 主从库都执行
SET GLOBAL gtid_mode = ON;

-- 从库执行
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;

升级 MySQL 版本 ​

  • 升级顺序：

    1. 先升级从库
    2. 测试从库功能正常
    3. 升级主库
    4. 验证主从复制正常

  • 注意事项：

    • 备份所有数据和配置
    • 测试升级后的功能
    • 准备回滚计划
    • 监控升级过程

常见问题（FAQ） ​

Q1: 主从复制可以跨版本吗？ ​

A1: 可以，但需要注意版本兼容性：

  • 从库版本可以高于主库，但不建议低于主库
  • 版本差异应在一个主版本内（如 5.7 → 8.0 可能需要特殊处理）
  • 跨版本复制可能存在功能差异和性能问题
  • 建议使用相同版本的主从库

Q2: 如何解决主从复制延迟问题？ ​

A2: 可以通过以下方式解决：

  • 升级从库硬件，尤其是 CPU 和磁盘
  • 优化主库写入性能，如使用批量写入
  • 避免在从库执行长时间查询
  • 调整并行复制参数 slave_parallel_workers
  • 使用 SSD 磁盘提高 I/O 性能
  • 考虑使用半同步或异步复制

Q3: 主从复制中的数据一致性如何保证？ ​

A3: 可以通过以下方式保证数据一致性：

  • 使用 row 格式的二进制日志
  • 启用 GTID 复制
  • 定期使用 pt-table-checksum 工具检查数据一致性
  • 使用半同步或全同步复制
  • 避免在从库手动修改数据

Q4: 如何添加新的从库到现有复制架构？ ​

A4: 可以通过以下步骤添加新从库：

  1. 配置新从库的 server_id 和其他参数
  2. 从主库或现有从库备份数据
  3. 恢复数据到新从库
  4. 配置新从库连接主库
  5. 启动复制并验证状态

Q5: 主库故障时如何切换到从库？ ​

A5: 可以通过以下步骤切换：

  1. 停止应用程序写入主库
  2. 等待从库追上主库（复制延迟为 0）
  3. 检查从库数据一致性
  4. 将从库提升为主库
  5. 更新应用程序连接到新主库
  6. 配置其他从库连接到新主库

Q6: 如何监控主从复制状态？ ​

A6: 可以通过以下方式监控：

  • 使用 SHOW SLAVE STATUS 命令查看复制状态
  • 使用 MySQL Enterprise Monitor 进行专业监控
  • 使用 Prometheus + Grafana 进行可视化监控
  • 使用 Zabbix 监控复制线程状态和延迟
  • 编写自定义脚本定期检查复制状态，发送告警