印刻数据库

外观

MySQL 复制环境部署与配置 ​

MySQL 复制是实现高可用性、读写分离和数据备份的基础技术。正确部署和配置 MySQL 复制环境对于保障数据安全和业务连续性至关重要。本文将详细介绍 MySQL 复制环境的部署与配置方法，包括传统复制、GTID 复制、半同步复制和并行复制，兼顾不同 MySQL 版本的差异。

复制基础概念 ​

复制原理 ​

MySQL 复制通过以下步骤实现数据同步：

  1. 主库将数据变更记录到二进制日志（binlog）
  2. 从库通过 IO 线程连接主库，请求获取二进制日志
  3. 主库通过 dump 线程将二进制日志发送给从库
  4. 从库将接收到的二进制日志写入中继日志（relay log）
  5. 从库通过 SQL 线程读取中继日志，并执行其中的 SQL 语句，将数据变更应用到从库

复制类型 ​

  1. 异步复制：主库提交事务后立即返回，不等待从库确认
  2. 半同步复制：主库提交事务后，等待至少一个从库确认收到二进制日志后才返回
  3. 全同步复制：主库提交事务后，等待所有从库确认应用完二进制日志后才返回（MySQL 5.7+ 支持）

复制格式 ​

  1. STATEMENT：基于 SQL 语句的复制
  2. ROW：基于行数据的复制
  3. MIXED：混合模式，根据语句自动选择复制格式

传统复制部署与配置 ​

传统复制是基于二进制日志文件名和位置的复制方式，是 MySQL 最基础的复制类型。

部署前准备 ​

  1. 服务器规划：

    • 主库：192.168.1.100
    • 从库：192.168.1.101
    • MySQL 版本：5.6/5.7/8.0

  2. 环境准备：

    • 确保主从库 MySQL 版本兼容
    • 确保主从库网络连通
    • 确保主从库数据初始一致

主库配置 ​

  1. 配置文件修改：
ini
# 启用二进制日志
log_bin = mysql-bin
server_id = 1

# 设置复制格式
binlog_format = ROW

# 设置二进制日志过期时间
expire_logs_days = 7

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

# 启用 sync_binlog（可选，提高数据安全性）
sync_binlog = 1
  1. 重启 MySQL 服务：
bash
# 系统服务方式
systemctl restart mysqld

# 或使用 mysqld_safe
mysqld_safe --defaults-file=/etc/my.cnf &
  1. 创建复制用户：
sql
-- 创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_password';

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

-- MySQL 8.0 需要额外授予 REPLICATION CLIENT 权限
GRANT REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';

-- 刷新权限
FLUSH PRIVILEGES;
  1. 获取主库状态：
sql
-- 锁定表，防止数据变更
FLUSH TABLES WITH READ LOCK;

-- 获取主库状态
SHOW MASTER STATUS;

输出示例：

FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
mysql-bin.000001154
  1. 备份主库数据：
bash
# 使用 mysqldump 备份
mysqldump -u root -p --all-databases --master-data=2 --single-transaction > full_backup.sql

# 解锁表
UNLOCK TABLES;

从库配置 ​

  1. 配置文件修改：
ini
# 设置服务器 ID
server_id = 2

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

# 启用从库只读
read_only = ON

# 启用 super_read_only（MySQL 5.7+）
super_read_only = ON

# 启用 relay_log_recovery（MySQL 5.6+）
relay_log_recovery = ON
  1. 重启 MySQL 服务：
bash
systemctl restart mysqld
  1. 恢复主库数据：
bash
mysql -u root -p < full_backup.sql
  1. 配置复制连接：
sql
-- 停止从库复制线程
STOP SLAVE;

-- 配置主库连接信息
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 154;

-- 启动从库复制线程
START SLAVE;
  1. 检查复制状态：
sql
SHOW SLAVE STATUS\G

关键状态检查：

  • Slave_IO_Running: 应为 Yes
  • Slave_SQL_Running: 应为 Yes
  • Seconds_Behind_Master: 应为 0 或较小值

GTID 复制部署与配置 ​

GTID（Global Transaction ID）复制是 MySQL 5.6+ 引入的复制方式，它使用全局事务 ID 来标识和跟踪事务，简化了复制管理和故障切换。

GTID 概念 ​

GTID 是由服务器 ID 和事务 ID 组成的唯一标识符，格式为 server_uuid:transaction_id。

主库配置 ​

  1. 配置文件修改：
ini
# 启用二进制日志
log_bin = mysql-bin
server_id = 1

# 设置复制格式为 ROW
binlog_format = ROW

# 启用 GTID 复制（MySQL 5.6/5.7）
gtid_mode = ON
enforce_gtid_consistency = ON

# 启用 GTID 复制（MySQL 8.0）
# gtid_mode = ON
# enforce_gtid_consistency = ON
# binlog_gtid_simple_recovery = ON

# 启用 binlog_checksum
binlog_checksum = CRC32

# 启用 sync_binlog
sync_binlog = 1
  1. 重启 MySQL 服务：
bash
systemctl restart mysqld
  1. 创建复制用户：
sql
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;
  1. 备份主库数据：
bash
mysqldump -u root -p --all-databases --master-data=2 --single-transaction --gtid-purged=ON > full_backup.sql

从库配置 ​

  1. 配置文件修改：
ini
# 设置服务器 ID
server_id = 2

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

# 启用从库只读
read_only = ON

# 启用 super_read_only
super_read_only = ON

# 启用 relay_log_recovery
relay_log_recovery = ON

# 启用 GTID 复制

gtid_mode = ON
enforce_gtid_consistency = ON
  1. 重启 MySQL 服务：
bash
systemctl restart mysqld
  1. 恢复主库数据：
bash
mysql -u root -p < full_backup.sql
  1. 配置复制连接：
sql
-- 停止从库复制线程
STOP SLAVE;

-- 配置主库连接信息（使用 GTID）
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl_password',
  MASTER_AUTO_POSITION = 1;

-- 启动从库复制线程
START SLAVE;
  1. 检查复制状态：
sql
SHOW SLAVE STATUS\G

关键状态检查：

  • Slave_IO_Running: 应为 Yes
  • Slave_SQL_Running: 应为 Yes
  • Seconds_Behind_Master: 应为 0 或较小值
  • Retrieved_Gtid_Set: 显示从主库获取的 GTID
  • Executed_Gtid_Set: 显示从库已执行的 GTID

半同步复制配置 ​

半同步复制是主从复制的增强版，它提高了数据一致性，减少了数据丢失的风险。

主库配置 ​

  1. 安装半同步复制插件：
sql
-- MySQL 5.6/5.7
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- MySQL 8.0
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
  1. 启用半同步复制：
sql
-- 启用半同步复制
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- 设置超时时间（毫秒）
SET GLOBAL rpl_semi_sync_master_timeout = 10000;
  1. 配置文件持久化：
ini
# 半同步复制配置
plugin_load_add = 'semisync_master.so'
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000

从库配置 ​

  1. 安装半同步复制插件：
sql
-- MySQL 5.6/5.7
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- MySQL 8.0
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
  1. 启用半同步复制：
sql
-- 启用半同步复制
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库复制线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
  1. 配置文件持久化：
ini
# 半同步复制配置
plugin_load_add = 'semisync_slave.so'
rpl_semi_sync_slave_enabled = 1

检查半同步复制状态 ​

sql
-- 主库检查
SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_master%';

-- 从库检查
SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_slave%';

并行复制配置 ​

并行复制可以提高从库的复制速度，特别是在主库有大量并发写入的场景下。

MySQL 5.6 并行复制 ​

MySQL 5.6 仅支持基于数据库的并行复制，即不同数据库的事务可以并行应用。

配置方法：

ini
# 设置并行复制线程数
slave_parallel_workers = 4

MySQL 5.7 并行复制 ​

MySQL 5.7 引入了基于组提交的并行复制（Enhanced Multi-threaded Slaves, MTS），支持同一数据库内的并行复制。

配置方法：

ini
# 设置并行复制线程数
slave_parallel_workers = 8

# 设置并行复制类型为基于组提交
slave_parallel_type = LOGICAL_CLOCK

# 启用 slave_preserve_commit_order（确保事务提交顺序与主库一致）
slave_preserve_commit_order = 1

MySQL 8.0 并行复制 ​

MySQL 8.0 增强了并行复制功能，支持更多的并行复制模式和优化。

配置方法：

ini
# 设置并行复制线程数
slave_parallel_workers = 16

# 设置并行复制类型为基于组提交
slave_parallel_type = LOGICAL_CLOCK

# 启用 slave_preserve_commit_order
slave_preserve_commit_order = 1

# 启用 binlog_transaction_dependency_tracking（MySQL 8.0.1 引入）
binlog_transaction_dependency_tracking = WRITESET

复制监控与维护 ​

复制状态监控 ​

  1. 查看从库状态：
sql
SHOW SLAVE STATUS\G
  1. 查看主库二进制日志状态：
sql
SHOW MASTER STATUS;
SHOW BINARY LOGS;
  1. 查看从库中继日志状态：
sql
SHOW RELAYLOG EVENTS;
  1. 监控复制延迟：
sql
-- 方法 1：通过 Seconds_Behind_Master
SHOW SLAVE STATUS\G

-- 方法 2：通过心跳检查（MySQL 5.7+）
SHOW GLOBAL VARIABLES LIKE 'slave_net_timeout';
  1. 使用 Performance Schema 监控复制：
sql
-- 查看复制相关的性能指标
SELECT * FROM performance_schema.replication_applier_status_by_worker;
SELECT * FROM performance_schema.replication_connection_status;

复制维护 ​

  1. 重置复制：
sql
-- 停止复制
STOP SLAVE;

-- 重置复制配置
RESET SLAVE ALL;

-- 重新配置复制
CHANGE MASTER TO ...;
START SLAVE;
  1. 清理二进制日志：
sql
-- 手动清理指定日志之前的日志
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 手动清理指定时间之前的日志
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- 自动清理（通过 expire_logs_days 配置）
SET GLOBAL expire_logs_days = 7;
  1. 修复复制错误：
sql
-- 跳过一个事务（不推荐）
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 跳过特定 GTID 事务（GTID 复制）
STOP SLAVE;
SET GTID_NEXT = 'server_uuid:transaction_id';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

不同版本的复制差异 ​

MySQL 5.6 复制特点 ​

  1. 支持传统复制和实验性 GTID 复制
  2. 仅支持基于数据库的并行复制
  3. 支持半同步复制（实验性）
  4. 复制性能一般
  5. 配置相对简单

MySQL 5.7 复制特点 ​

  1. 支持成熟的 GTID 复制
  2. 支持基于组提交的并行复制
  3. 支持成熟的半同步复制
  4. 支持全同步复制
  5. 增强了复制的可靠性和性能
  6. 支持多源复制

MySQL 8.0 复制特点 ​

  1. 增强了 GTID 复制
  2. 增强了并行复制，支持 WRITESET 依赖跟踪
  3. 增强了半同步复制
  4. 支持异步连接 failover
  5. 支持 replica 延迟监控
  6. 增强了复制的安全性
  7. 重命名了部分复制相关的变量和命令（例如 SLAVE 改为 REPLICA）

复制最佳实践 ​

1. 配置建议 ​

  1. 使用 GTID 复制：简化复制管理和故障切换
  2. 使用 ROW 复制格式：提高复制的可靠性和一致性
  3. 启用半同步复制：减少数据丢失的风险
  4. 配置合理的并行复制线程数：根据 CPU 核心数和业务负载设置
  5. 启用 relay_log_recovery：防止中继日志损坏
  6. 设置合理的二进制日志过期时间：避免磁盘空间耗尽
  7. 从库设置为只读：防止误写

2. 部署建议 ​

  1. 确保主从库版本兼容：建议使用相同或相近的版本
  2. 确保主从库配置一致：特别是字符集、时区、参数等
  3. 使用专用的复制用户：限制权限，提高安全性
  4. 部署在不同的物理服务器上：避免单点故障
  5. 确保网络稳定：使用专用网络或高速网络

3. 监控建议 ​

  1. 监控复制状态：定期检查 Slave_IO_Running 和 Slave_SQL_Running
  2. 监控复制延迟：设置合理的告警阈值
  3. 监控二进制日志和中继日志：确保日志正常生成和应用
  4. 监控主从库的硬件资源：CPU、内存、磁盘、网络等
  5. 设置告警机制：当复制出现问题时及时通知

4. 维护建议 ​

  1. 定期备份数据：确保数据安全
  2. 定期测试复制：确保复制正常工作
  3. 定期清理日志：避免磁盘空间耗尽
  4. 定期检查复制配置：确保配置最优
  5. 定期进行故障切换演练：确保故障切换的可靠性

常见问题与解决方案 ​

1. 复制延迟 ​

症状：Seconds_Behind_Master 值较大

解决方案：

  • 优化主库和从库的硬件配置
  • 启用并行复制，增加并行复制线程数
  • 减少大事务，将大事务拆分为小事务
  • 优化网络，减少网络延迟
  • 考虑使用半同步复制或 MGR
  • 检查从库是否有慢查询或锁等待

2. 复制中断 ​

症状：Slave_IO_Running 或 Slave_SQL_Running 为 No

解决方案：

  • 查看 Last_Error 信息，分析错误原因
  • 根据错误信息修复问题
  • 重启复制线程：`START SLAVE;
  • 必要时跳过错误事务（谨慎使用）

3. 主库二进制日志丢失 ​

症状：从库请求的二进制日志文件不存在

解决方案：

  • 检查主库的二进制日志保留策略
  • 增加 expire_logs_days 参数值
  • 使用备份恢复从库数据，重新配置复制
  • 考虑使用 GTID 复制，减少对二进制日志文件名和位置的依赖

4. 从库数据不一致 ​

症状：主从库数据不一致

解决方案：

  • 使用 pt-table-checksum 工具检测数据一致性
  • 使用 pt-table-sync 工具修复数据不一致
  • 重新初始化从库：备份主库数据，恢复到从库，重新配置复制
  • 考虑使用半同步复制或 MGR，提高数据一致性

5. 复制用户权限问题 ​

症状：从库无法连接主库，报权限错误

解决方案：

  • 检查复制用户的用户名和密码是否正确
  • 检查复制用户的主机权限是否正确
  • 检查主库的防火墙是否允许从库连接
  • 重新创建复制用户，授予正确的权限

总结 ​

MySQL 复制是实现高可用性、读写分离和数据备份的基础技术。正确部署和配置 MySQL 复制环境对于保障数据安全和业务连续性至关重要。本文详细介绍了传统复制、GTID 复制、半同步复制和并行复制的部署与配置方法，以及不同 MySQL 版本的复制差异和最佳实践。

在实际部署和配置 MySQL 复制环境时，需要根据业务需求、规模和团队能力选择合适的复制类型和配置。同时，需要建立完善的监控和维护机制，确保复制环境的稳定运行。通过合理的配置和维护，可以充分发挥 MySQL 复制的优势，为业务提供可靠的数据服务。