印刻数据库

外观

MariaDB 主从延迟 ​

主从延迟是 MariaDB 复制环境中常见的问题，当从库无法及时同步主库的数据时就会产生主从延迟。主从延迟会影响数据一致性、高可用切换和读写分离等功能，严重影响系统的可用性和可靠性。本文将详细介绍 MariaDB 主从延迟的诊断、处理和预防方法，帮助 DBA 快速定位和解决主从延迟问题。

主从延迟概述 ​

主从延迟定义 ​

主从延迟是指从库执行二进制日志（binlog）的时间与主库生成该日志的时间之间的差值。在 MariaDB 中，可以通过 Seconds_Behind_Master 状态变量查看主从延迟的秒数。

主从延迟的影响 ​

  • 数据一致性：从库数据与主库不一致，影响业务查询结果
  • 高可用切换：主从延迟会导致切换后数据丢失
  • 读写分离：应用程序可能读取到过期数据
  • 备份影响：从库备份的数据可能不完整
  • 监控告警：频繁的主从延迟告警会影响 DBA 的工作效率

复制类型 ​

MariaDB 支持多种复制类型，不同类型的主从延迟表现不同：

  • 异步复制：主库不等待从库确认，主从延迟可能较大
  • 半同步复制：主库等待至少一个从库确认，主从延迟较小
  • 增强半同步复制：主库等待至少一个从库将日志写入磁盘，主从延迟更小
  • 并行复制：从库并行应用 binlog，减少主从延迟

版本差异 ​

不同 MariaDB 版本在复制延迟处理方面存在一些差异：

  • MariaDB 5.5+：支持基本的异步复制和半同步复制
  • MariaDB 10.0+：引入了并行复制，支持基于数据库的并行复制
  • MariaDB 10.1+：支持基于 GTID 的复制，简化了复制管理
  • MariaDB 10.2+：支持基于组提交的并行复制，提高了并行复制的效率
  • MariaDB 10.3+：引入了多源复制，支持从多个主库复制数据
  • MariaDB 10.5+：优化了并行复制的算法，减少了主从延迟
  • MariaDB 10.6+：支持并行复制的动态调整，根据负载自动调整并行度

主从延迟诊断 ​

查看复制状态 ​

使用 SHOW SLAVE STATUS ​

SHOW SLAVE STATUS 是诊断主从延迟的主要工具，可以查看复制的详细状态：

sql
SHOW SLAVE STATUSG

关键状态变量包括：

  • Slave_IO_Running：IO 线程状态
  • Slave_SQL_Running：SQL 线程状态
  • Seconds_Behind_Master：主从延迟秒数
  • Master_Log_File：主库当前的 binlog 文件
  • Relay_Master_Log_File：从库当前应用的 binlog 文件
  • Read_Master_Log_Pos：IO 线程读取的主库 binlog 位置
  • Exec_Master_Log_Pos：SQL 线程执行的主库 binlog 位置
  • Last_IO_Error：IO 线程错误
  • Last_SQL_Error：SQL 线程错误

使用 SHOW STATUS ​

sql
-- 查看复制相关的状态变量
SHOW GLOBAL STATUS LIKE 'Slave%';
SHOW GLOBAL STATUS LIKE 'Binlog%';

监控复制延迟 ​

使用 Performance Schema ​

从 MariaDB 10.2 开始，可以使用 Performance Schema 监控复制延迟：

sql
-- 启用复制监控
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%replication%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%replication%';

-- 查看复制延迟
SELECT * FROM performance_schema.replication_applier_status_by_worker;
SELECT * FROM performance_schema.replication_connection_status;

使用 INFORMATION_SCHEMA ​

sql
-- 查看复制连接状态
SELECT * FROM INFORMATION_SCHEMA.REPLICA_HOST_STATUS;

分析延迟原因 ​

  1. IO 线程问题：

    • 网络延迟或中断
    • 主库 binlog 文件过大
    • 从库 IO 线程异常

  2. SQL 线程问题：

    • 从库执行慢查询
    • 从库资源不足
    • 从库表结构与主库不一致
    • 从库存在未解决的错误

  3. 并行复制问题：

    • 并行复制配置不合理
    • 事务依赖关系导致并行度不高
    • 并行复制线程数不足

  4. 主库问题：

    • 主库负载过高
    • 主库大量写入操作
    • 主库大事务
    • 主库 binlog 刷盘策略不合理

常见主从延迟场景 ​

场景一：大事务导致的主从延迟 ​

问题描述：主库执行了一个大事务，如批量更新或插入大量数据，导致从库需要长时间应用该事务。

诊断方法：

  • 查看主库的慢查询日志，寻找大事务
  • 查看从库的 Seconds_Behind_Master，发现延迟突然增加
  • 查看从库的 Relay_Master_Log_File 和 Exec_Master_Log_Pos，发现长时间停留在同一个位置

处理方法：

  • 优化大事务，将其拆分为多个小事务
  • 调整从库的并行复制配置
  • 考虑在低峰期执行大事务

场景二：从库资源不足 ​

问题描述：从库的 CPU、内存或磁盘资源不足，导致无法及时应用 binlog。

诊断方法：

  • 监控从库的资源使用率，发现 CPU 或 IO 使用率过高
  • 查看从库的进程列表，发现大量慢查询
  • 查看从库的磁盘 I/O 等待时间，发现磁盘性能不足

处理方法：

  • 升级从库硬件，增加 CPU、内存或使用更快的磁盘
  • 优化从库的查询，减少资源消耗
  • 考虑增加从库的数量，分担读压力

场景三：网络问题 ​

问题描述：主从库之间的网络延迟或中断，导致从库无法及时获取主库的 binlog。

诊断方法：

  • 测试主从库之间的网络延迟，发现延迟较大
  • 查看从库的 Last_IO_Error，发现网络相关错误
  • 查看从库的 Slave_IO_Running 状态，发现 IO 线程异常

处理方法：

  • 优化网络连接，减少网络延迟
  • 使用更可靠的网络传输，如专线或 VPN
  • 考虑将从库部署在与主库相同的机房

场景四：并行复制配置不合理 ​

问题描述：从库的并行复制配置不合理，导致并行度不高，无法充分利用从库资源。

诊断方法：

  • 查看从库的 Slave_Pending_Jobs_Size，发现队列积压
  • 查看 Performance Schema 中的并行复制状态，发现并行度不高
  • 查看从库的事务执行情况，发现大量串行执行的事务

处理方法：

  • 调整并行复制的配置参数
  • 升级到支持更高并行度的 MariaDB 版本
  • 优化应用程序，减少事务之间的依赖关系

场景五：主库负载过高 ​

问题描述：主库负载过高，导致 binlog 生成速度快于从库应用速度。

诊断方法：

  • 监控主库的负载，发现 CPU、内存或 IO 使用率过高
  • 查看主库的慢查询日志，发现大量慢查询
  • 查看主库的连接数，发现连接数过多

处理方法：

  • 优化主库的查询，减少资源消耗
  • 考虑读写分离，减轻主库的读压力
  • 考虑水平扩展，增加主库的数量

主从延迟处理 ​

紧急处理 ​

跳过错误 ​

如果从库遇到无法解决的错误，可以临时跳过该错误：

sql
-- 跳过一个事务
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

注意：跳过错误可能导致数据不一致，仅在紧急情况下使用。

重建从库 ​

如果主从延迟严重，且无法快速恢复，可以考虑重建从库：

  1. 在主库上创建全量备份
  2. 在从库上恢复备份
  3. 重新配置复制
  4. 启动复制

长期优化 ​

优化复制配置 ​

  1. 启用并行复制：
ini
[mysqld]
# 启用并行复制
slave_parallel_threads = 8

# 并行复制类型（MariaDB 10.2+）
slave_parallel_mode = optimistic

# 并行复制队列大小
slave_parallel_max_queued = 1000
  1. 优化半同步复制：
ini
[mysqld]
# 启用半同步复制
plugin_load_add = semisync_master.so
semisync_master_enabled = 1

plugin_load_add = semisync_slave.so
semisync_slave_enabled = 1

# 半同步复制超时时间（毫秒）
semisync_master_timeout = 10000
  1. 优化 binlog 配置：
ini
[mysqld]
# 启用 binlog
binlog_format = ROW
log_bin = /var/lib/mysql/mysql-bin

# binlog 过期时间（天）
expire_logs_days = 7

# binlog 大小限制（MB）
max_binlog_size = 100M

# 同步写入 binlog
sync_binlog = 1
  1. 优化从库配置：
ini
[mysqld]
# 禁用查询缓存（从库不需要）
query_cache_type = 0

# 优化 InnoDB 配置
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2

# 优化复制线程
slave_net_timeout = 60
master_connect_retry = 10

优化应用程序 ​

  1. 减少大事务：将大事务拆分为多个小事务
  2. 优化事务顺序：减少事务之间的依赖关系
  3. 使用合理的隔离级别：优先使用 READ-COMMITTED 隔离级别
  4. 避免长事务：尽快提交或回滚事务
  5. 优化写入操作：使用批量插入，减少事务数量

优化架构 ​

  1. 读写分离：将读请求分发到从库，减轻主库压力
  2. 分库分表：将数据分散到多个数据库实例，减少单个实例的负载
  3. 使用多源复制：将多个主库的数据复制到同一个从库
  4. 使用 Galera Cluster：实现真正的多主复制，避免主从延迟
  5. 使用中间件：如 MaxScale 或 ProxySQL，实现智能的读写分离和故障切换

主从延迟预防 ​

监控告警 ​

  1. 设置合理的告警阈值：

    • 当 Seconds_Behind_Master 超过 30 秒时触发警告
    • 当 Seconds_Behind_Master 超过 300 秒时触发严重告警
    • 当复制线程停止时立即触发告警

  2. 监控关键指标：

    • Seconds_Behind_Master：主从延迟秒数
    • Slave_IO_Running：IO 线程状态
    • Slave_SQL_Running：SQL 线程状态
    • Binlog_Disk_Writes：binlog 写入次数
    • Innodb_os_log_written：事务日志写入量

  3. 使用监控工具：

    • Prometheus + Grafana：实时监控复制状态
    • Zabbix：设置复制延迟告警
    • Nagios：监控复制线程状态
    • Percona Monitoring and Management (PMM)：提供专门的复制监控面板

定期维护 ​

  1. 定期检查复制状态：每天查看复制状态，及时发现问题
  2. 定期优化从库：优化从库的查询和配置
  3. 定期清理 binlog：避免 binlog 文件过大，影响复制效率
  4. 定期更新统计信息：确保查询优化器有准确的统计信息
  5. 定期进行复制切换演练：测试复制切换的可靠性和数据一致性

最佳实践 ​

  1. 使用 GTID 复制：简化复制管理，提高复制的可靠性
  2. 启用半同步复制：减少主从延迟，提高数据一致性
  3. 优化并行复制：根据从库资源调整并行度
  4. 监控主从延迟：设置合理的告警阈值，及时发现问题
  5. 定期备份从库：确保从库数据可以恢复
  6. 使用复制过滤：只复制必要的数据库和表，减少复制开销
  7. 避免在从库上执行写操作：从库主要用于读操作，写操作可能导致复制错误
  8. 定期检查从库的表结构：确保从库的表结构与主库一致

主从延迟案例分析 ​

案例一：大事务导致的主从延迟 ​

问题描述： 某电商网站在促销期间，主库执行了一个批量更新订单状态的大事务，导致从库主从延迟达到了 1 小时。

诊断过程：

  1. 查看 SHOW SLAVE STATUS，发现 Seconds_Behind_Master 为 3600 秒
  2. 查看从库的 Relay_Master_Log_File 和 Exec_Master_Log_Pos，发现长时间停留在同一个位置
  3. 查看主库的慢查询日志，发现一个执行时间为 30 分钟的大事务
  4. 查看从库的 CPU 使用率，发现 CPU 使用率达到了 100%

处理方法：

  1. 优化大事务，将其拆分为多个小事务
  2. 调整从库的并行复制配置，将 slave_parallel_threads 从 4 增加到 16
  3. 升级从库的硬件，增加 CPU 核心数
  4. 在低峰期执行大事务

优化结果： 主从延迟从 1 小时降低到了 5 分钟以内，系统稳定性显著提高。

案例二：网络问题导致的主从延迟 ​

问题描述： 某公司的主库和从库部署在不同的机房，由于网络抖动，导致主从延迟频繁波动。

诊断过程：

  1. 查看 SHOW SLAVE STATUS，发现 Seconds_Behind_Master 频繁变化
  2. 查看从库的 Last_IO_Error，发现网络连接超时错误
  3. 测试主从库之间的网络延迟，发现延迟在 100ms 到 1000ms 之间波动
  4. 查看从库的 Slave_IO_Running 状态，发现 IO 线程时有中断

处理方法：

  1. 优化网络连接，使用专线连接两个机房
  2. 调整从库的 slave_net_timeout 从 360 秒增加到 600 秒
  3. 启用半同步复制，确保至少一个从库确认收到 binlog
  4. 考虑在主库机房部署从库，减少网络延迟

优化结果： 主从延迟稳定在 1 秒以内，网络抖动对复制的影响显著降低。

案例三：并行复制配置不合理导致的主从延迟 ​

问题描述： 某公司的从库启用了并行复制，但主从延迟仍然较高。

诊断过程：

  1. 查看 SHOW SLAVE STATUS，发现 Seconds_Behind_Master 为 300 秒
  2. 查看 Performance Schema 中的并行复制状态，发现并行度只有 1
  3. 查看从库的配置，发现 slave_parallel_mode 配置为 conservative
  4. 查看主库的事务，发现大量事务之间存在依赖关系

处理方法：

  1. 将 slave_parallel_mode 从 conservative 改为 optimistic
  2. 调整 slave_parallel_threads 从 8 增加到 16
  3. 优化应用程序，减少事务之间的依赖关系
  4. 升级到 MariaDB 10.5+，使用更高效的并行复制算法

优化结果： 从库的并行度提高到了 8，主从延迟降低到了 10 秒以内。

FAQ ​

Q1: 如何准确测量主从延迟？ ​

A1: 可以使用以下方法准确测量主从延迟：

  1. 使用 SHOW SLAVE STATUS 中的 Seconds_Behind_Master
  2. 在主库上创建时间戳表，定期更新时间戳，在从库上查询时间差
  3. 使用第三方工具，如 pt-heartbeat 或 mk-heartbeat

Q2: Seconds_Behind_Master 为 NULL 是什么意思？ ​

A2: Seconds_Behind_Master 为 NULL 通常表示：

  • 从库的 IO 线程或 SQL 线程未运行
  • 从库未连接到主库
  • 复制配置有误

Q3: 如何区分真延迟和假延迟？ ​

A3: 真延迟是指从库确实无法及时应用 binlog，假延迟是指 Seconds_Behind_Master 显示有延迟，但实际数据已经同步。可以通过以下方法区分：

  1. 在主库上创建测试数据，在从库上查询是否存在
  2. 查看从库的 Relay_Master_Log_File 和 Exec_Master_Log_Pos，是否与主库的 Master_Log_File 和 Position 接近
  3. 查看从库的 binlog 应用速度，是否正常

Q4: 如何处理从库的复制错误？ ​

A4: 可以采取以下步骤处理从库的复制错误：

  1. 查看 Last_SQL_Error，了解错误原因
  2. 根据错误原因，修复从库的数据或表结构
  3. 如果无法修复，可以考虑跳过错误或重建从库
  4. 启动复制，验证复制是否正常

Q5: 半同步复制可以完全消除主从延迟吗？ ​

A5: 半同步复制可以减少主从延迟，但无法完全消除。因为半同步复制只保证至少一个从库收到了 binlog，但不保证从库已经应用了 binlog。

Q6: 并行复制的最佳并行度是多少？ ​

A6: 并行复制的最佳并行度取决于从库的 CPU 核心数和事务特性，一般建议：

  • 并行度设置为 CPU 核心数的 1-2 倍
  • 根据实际负载调整并行度
  • 启用动态并行复制（MariaDB 10.6+）

Q7: 如何使用 GTID 复制减少主从延迟？ ​

A7: GTID 复制可以简化复制管理，提高复制的可靠性，但本身不会直接减少主从延迟。可以结合并行复制和半同步复制，进一步减少主从延迟。

Q8: MariaDB 和 MySQL 的主从延迟处理有什么区别？ ​

A8: MariaDB 和 MySQL 的主从延迟处理基本相似，但 MariaDB 提供了更多的优化功能：

  • MariaDB 10.2+ 支持基于组提交的并行复制，效率更高
  • MariaDB 10.5+ 优化了并行复制的算法，减少了主从延迟
  • MariaDB 10.6+ 支持并行复制的动态调整
  • MariaDB 支持多源复制，可以从多个主库复制数据
  • MariaDB 对半同步复制进行了优化，提高了可靠性

附录：复制延迟监控脚本 ​

使用 pt-heartbeat 监控主从延迟 ​

bash
# 在主库上安装并启动 pt-heartbeat
pt-heartbeat --update --user=root --password=password --database=mysql --interval=1 --daemonize

# 在从库上监控主从延迟
pt-heartbeat --monitor --user=root --password=password --database=mysql --master-server-id=1 --interval=1

自定义监控脚本 ​

bash
#!/bin/bash

# 主从延迟监控脚本

MASTER_HOST="master.example.com"
SLAVE_HOST="slave.example.com"
USER="root"
PASSWORD="password"
THRESHOLD=30

# 获取从库延迟
SLAVE_DELAY=$(mysql -h $SLAVE_HOST -u $USER -p$PASSWORD -e "SHOW SLAVE STATUSG" | grep "Seconds_Behind_Master" | awk '{print $2}')

# 检查延迟
if [ "$SLAVE_DELAY" -gt "$THRESHOLD" ]; then
    echo "主从延迟超过阈值：$SLAVE_DELAY 秒" | mail -s "MariaDB 主从延迟告警" dba@example.com
fi

将此脚本添加到 crontab 中，定期执行：

bash
*/5 * * * * /path/to/slave_delay_monitor.sh