印刻数据库

外观

MySQL 物理和逻辑复制 ​

复制的基本概念 ​

MySQL复制是一种将数据从一个MySQL服务器（主服务器）复制到一个或多个MySQL服务器（从服务器）的机制。通过复制，可以实现：

  • 数据备份和灾难恢复
  • 负载均衡，将读请求分散到多个服务器
  • 高可用性，当主服务器故障时可以切换到从服务器
  • 数据分析，在从服务器上进行数据分析而不影响主服务器性能
  • 数据库升级和迁移

物理复制的原理和特点 ​

物理复制的基本原理 ​

物理复制（也称为二进制日志复制）是MySQL默认的复制方式，它基于二进制日志（binlog）实现：

  1. 主服务器将数据修改操作记录到二进制日志中
  2. 从服务器启动一个I/O线程，连接到主服务器并请求读取二进制日志
  3. 主服务器启动一个Binlog Dump线程，将二进制日志事件发送给从服务器
  4. 从服务器将接收到的二进制日志事件写入中继日志（relay log）
  5. 从服务器启动一个SQL线程，读取中继日志中的事件并在本地执行，从而实现数据同步

物理复制的特点 ​

  • 基于二进制日志：复制的是二进制日志中的事件，而不是SQL语句本身
  • 异步复制：默认情况下，主服务器不需要等待从服务器确认已接收到或应用了二进制日志事件
  • 支持全量和增量复制：可以通过完全备份+增量二进制日志实现数据恢复
  • 复制效率高：复制的是二进制数据，传输和应用效率较高
  • 主从数据一致性好：只要复制正常运行，主从数据保持一致

物理复制的配置 ​

bash
# 主服务器配置 (/etc/my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

# 从服务器配置 (/etc/my.cnf)
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read-only = ON

# 主服务器上创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# 获取主服务器的二进制日志位置
SHOW MASTER STATUS;

# 从服务器上配置复制连接
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

逻辑复制的原理和特点 ​

逻辑复制的基本原理 ​

逻辑复制是MySQL 8.0引入的一种新的复制方式，它基于行级别的变更捕获和应用：

  1. 源服务器将数据修改操作捕获为行级变更事件
  2. 源服务器将变更事件发送到复制通道
  3. 目标服务器接收变更事件并在本地应用

逻辑复制的特点 ​

  • 基于行级变更：复制的是数据行的变更，而不是二进制日志事件
  • 支持跨版本复制：可以在不同MySQL版本之间进行复制
  • 支持跨平台复制：可以在不同操作系统之间进行复制
  • 支持选择性复制：可以只复制特定的数据库、表或行
  • 支持多源复制：一个从服务器可以从多个主服务器复制数据

逻辑复制的配置 ​

bash
# 源服务器配置 (/etc/my.cnf)
[mysqld]
server-id = 1
binlog-format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

# 源服务器上创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# 目标服务器配置 (/etc/my.cnf)
[mysqld]
server-id = 2
read-only = ON
gtid_mode = ON
enforce_gtid_consistency = ON

# 目标服务器上配置逻辑复制通道
CHANGE MASTER TO
    MASTER_HOST = 'source_host',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_AUTO_POSITION = 1,
    FOR CHANNEL 'source_channel';

# 启动逻辑复制
START SLAVE FOR CHANNEL 'source_channel';

# 检查逻辑复制状态
SHOW SLAVE STATUS FOR CHANNEL 'source_channel'\G

物理复制 vs 逻辑复制的比较 ​

特性物理复制逻辑复制
复制原理基于二进制日志事件基于行级变更
跨版本支持有限，需要版本兼容较好，支持跨版本复制
跨平台支持有限，需要相同的存储引擎支持，与存储引擎无关
选择性复制不支持支持，可以复制特定对象
多源复制不支持（5.7+支持多源复制，但仍基于物理复制）支持
复制效率高相对较低
配置复杂度相对简单相对复杂
应用场景主从复制、负载均衡、灾难恢复数据迁移、跨版本升级、数据分析

物理复制的应用场景 ​

1. 主从复制架构 ​

  • 一个主服务器用于处理写请求，多个从服务器用于处理读请求
  • 提高系统的读性能和并发处理能力
  • 实现数据备份和灾难恢复

2. 级联复制架构 ​

  • 主服务器 → 中间从服务器 → 最终从服务器
  • 减少主服务器的复制压力
  • 适用于大规模的复制拓扑

3. 主主复制架构 ​

  • 两个服务器都可以处理写请求，互相复制数据
  • 提高系统的可用性和故障转移能力
  • 但需要解决数据冲突问题

逻辑复制的应用场景 ​

1. 跨版本数据迁移 ​

  • 将旧版本MySQL数据库的数据迁移到新版本
  • 支持在线迁移，减少业务中断时间
  • 可以在迁移过程中进行数据验证

2. 数据分片和合并 ​

  • 将不同分片的数据合并到一个中央数据库
  • 实现数据仓库和数据分析
  • 支持不同分片之间的数据同步

3. 多源数据整合 ​

  • 将多个不同来源的数据整合到一个目标数据库
  • 支持不同数据库之间的数据同步
  • 适用于微服务架构的数据整合

复制监控和管理 ​

物理复制监控 ​

bash
# 检查复制状态
SHOW SLAVE STATUS\G

# 检查I/O线程和SQL线程状态
SHOW SLAVE STATUS LIKE 'Slave_IO_Running';
SHOW SLAVE STATUS LIKE 'Slave_SQL_Running';

# 检查复制延迟
SHOW SLAVE STATUS LIKE 'Seconds_Behind_Master';

# 监控二进制日志和中继日志
SHOW BINARY LOGS;
SHOW RELAYLOG EVENTS;

逻辑复制监控 ​

bash
# 检查逻辑复制状态
SHOW SLAVE STATUS FOR CHANNEL 'channel_name'\G

# 检查复制通道状态
SELECT * FROM performance_schema.replication_channels;

# 监控复制性能
SELECT * FROM performance_schema.replication_applier_status_by_worker;

# 查看复制错误
SELECT * FROM performance_schema.replication_applier_status_by_coordinator;

复制常见问题及解决方法 ​

1. 复制延迟 ​

问题：从服务器数据与主服务器数据存在明显延迟

解决方法：

bash
# 检查复制延迟原因
SHOW SLAVE STATUS\G

# 优化主服务器二进制日志传输
# 增加主服务器的binlog_cache_size
SET GLOBAL binlog_cache_size = 64M;

# 优化从服务器SQL线程
# 启用并行复制（MySQL 5.7+）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;

# 减少从服务器负载
# 关闭不必要的日志记录
SET GLOBAL slow_query_log = OFF;

2. 复制中断 ​

问题：从服务器I/O线程或SQL线程停止

解决方法：

bash
# 查看复制错误信息
SHOW SLAVE STATUS\G

# 跳过错误（仅在测试环境或确认数据一致性的情况下使用）
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

# 重新配置复制
STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE = 'new_log_file', MASTER_LOG_POS = new_log_pos;
START SLAVE;

3. 主从数据不一致 ​

问题：主服务器和从服务器数据不一致

解决方法：

bash
# 检查主从数据一致性
# 使用pt-table-checksum工具
pt-table-checksum --host=master_host --user=root --password=password

# 修复数据不一致
# 使用pt-table-sync工具
pt-table-sync --execute --host=master_host --user=root --password=password --slave=slave_host

# 重新初始化从服务器
# 在主服务器上创建完全备份
mysqldump --all-databases --master-data=2 > backup.sql

# 在从服务器上恢复备份
mysql -u root -p < backup.sql

复制的最佳实践 ​

1. 合理规划复制拓扑 ​

  • 根据业务需求选择合适的复制架构（主从、级联、主主等）
  • 控制从服务器的数量，避免主服务器复制压力过大
  • 考虑地理分布，实现异地灾备

2. 优化复制配置 ​

bash
# 启用GTID复制（MySQL 5.6+）
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

# 使用ROW格式的二进制日志
binlog-format = ROW

# 启用半同步复制（MySQL 5.5+）
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1

# 优化从服务器性能
slave_parallel_type = 'LOGICAL_CLOCK'
slave_parallel_workers = 8
slave_preserve_commit_order = 1

3. 定期监控和维护 ​

  • 定期检查复制状态和延迟
  • 定期备份主服务器和从服务器数据
  • 定期清理二进制日志和中继日志
  • 定期验证主从数据一致性

4. 实现自动故障转移 ​

  • 使用MHA（Master High Availability）工具实现自动故障转移
  • 使用MySQL Group Replication实现高可用性和自动故障转移
  • 使用ProxySQL或MaxScale实现读写分离和故障转移

版本差异 ​

MySQL 5.6 vs 5.7 vs 8.0 复制差异 ​

特性MySQL 5.6MySQL 5.7MySQL 8.0
GTID复制支持增强增强
并行复制不支持支持，基于逻辑时钟增强，支持更多并行类型
半同步复制支持增强增强
多源复制不支持支持支持
逻辑复制不支持不支持支持
复制过滤支持支持支持，增强
复制监控基本支持增强，更多性能指标增强，更多监控视图

常见问题（FAQ） ​

Q1: 物理复制和逻辑复制的主要区别是什么？ ​

A1: 物理复制基于二进制日志事件，复制效率高，主要用于主从复制和负载均衡；逻辑复制基于行级变更，支持跨版本和跨平台复制，主要用于数据迁移和整合。

Q2: 如何选择物理复制还是逻辑复制？ ​

A2: 选择复制方式应根据业务需求：如果需要高可靠性和高性能的主从复制，建议使用物理复制；如果需要跨版本迁移、数据整合或选择性复制，建议使用逻辑复制。

Q3: 如何解决复制延迟问题？ ​

A3: 解决复制延迟问题的方法包括：优化主服务器二进制日志传输、启用从服务器并行复制、减少从服务器负载、优化SQL查询等。

Q4: 如何确保主从数据一致性？ ​

A4: 确保主从数据一致性的方法包括：定期使用pt-table-checksum检查数据一致性、使用半同步复制或组复制、避免在从服务器上直接修改数据、定期验证和修复数据一致性等。

Q5: 如何实现自动故障转移？ ​

A5: 实现自动故障转移的方法包括：使用MHA工具、使用MySQL Group Replication、使用ProxySQL或MaxScale等中间件实现读写分离和故障转移。

Q6: GTID复制有什么优势？ ​

A6: GTID（全局事务标识符）复制的优势包括：简化复制配置和管理、支持自动故障转移、便于监控和管理、提高复制的可靠性和一致性等。

Q7: 如何配置并行复制？ ​

A7: 配置并行复制的方法包括：在MySQL 5.7+中设置slave_parallel_type为LOGICAL_CLOCK，设置slave_parallel_workers为适当的并行线程数，启用slave_preserve_commit_order确保事务顺序一致。

Q8: 如何监控复制状态？ ​

A8: 监控复制状态的方法包括：使用SHOW SLAVE STATUS命令、查询performance_schema中的复制相关表、使用第三方监控工具（如Prometheus+Grafana、Zabbix等）、设置复制延迟告警等。

Q9: 如何处理复制错误？ ​

A9: 处理复制错误的方法包括：查看错误日志和复制状态、分析错误原因、修复错误（如跳过特定错误、重新配置复制、修复数据一致性等）、防止类似错误再次发生。

Q10: 半同步复制和异步复制有什么区别？ ​

A10: 异步复制是主服务器不需要等待从服务器确认已接收到或应用了二进制日志事件；半同步复制是主服务器需要等待至少一个从服务器确认已接收到二进制日志事件后才会提交事务，从而提高数据一致性，但可能会影响主服务器性能。