MySQL 中Relay Log打满磁盘问题的排查方案

MySQL 中Relay Log打满磁盘问题的排查方案

引言：

MySQL?Relay Log（中继日志）是MySQL复制过程中的一个重要组件，它用于将主数据库的二进制日志事件传递给从数据库。然而，当中继日志不断增长并最终占满磁盘空间时，可能导致数据库复制出现问题。本文将介绍一些常见的排查方案，帮助解决MySQL Relay Log打满磁盘的问题。

实际生产过程中出现的问题：在进行大量的数据同步过程中，主备库之间进行数据复制导致relay log打满磁盘。

1. 检查复制延迟

首先，确认是否存在复制延迟。当数据库无法及时处理中继日志，中继日志会不断积累，从而导致磁盘空间被占满。通过执行以下查询语句，可以获取主数据库和从数据库之间的复制延迟情况：

SHOW SLAVE STATUS\G

从结果中可以看出是回访线程出错，导致中继日志不断累积，导致磁盘打满。那接下来的排查重点就是回放线程出错的原因，

select * from  performance_schema.replication_applier_status_by_worker limit 10\G

错误信息： Could not execute Delete rows event on table database?dm?rsznfxk.file job search entrepreneurshp_graduate 2023; Can’t find record in ‘file job search entrepreneurship graduate 2023’

导致回放失败的原因就是在执行delete操作时出现了问题，怀疑是slave删除了记录，然后回放时，尝试删除不存在的记录导致失败。排查思路是:

• 先找到这条事务中delete语句。
• 然后去slave的binlog中查找这条语句，确认是否有用户做了删除操作。

2.检查relay log大小

确定中继日志文件的大小，以及每个中继日志文件的数量。执行以下查询语句获取中继日志相关信息：

SHOW RELAYLOG EVENTS;

如果发现中继日志文件过大，可以考虑进行日志文件的轮转和清理。可以使用以下方法进行操作：

• 执行PURGE BINARY LOGS TO?<Relay_Log_File>; 清理旧的中继日志文件。
• 配置MySQL参数relay_log_space_limit限制中继日志的总体积，防止无限制增长。

在上述问题中，slave是自动执行过purge操作的，导致slave上部分的中继日志被清理，影响后续的问题排查（事实是的确影响了最后的排查）

3. 检查从数据库的复制状态

确认从数据库是否正常复制主数据库的数据。可以通过以下方法进行检查：

• 执行SHOW SLAVE STATUS\G，检查Slave_IO_Running和Slave_SQL_Running字段的值是否为Yes。
• 检查从数据库的错误日志，查找任何与复制相关的错误信息。

4. 检查主数据库的二进制文件

确保主数据库的二进制日志状态正常，避免出现无法复制的情况。执行以下查询语句：

SHOW MASTER STATUS;

检查File和Position字段的值，并与从数据库的中继日志进行对比。如果从数据库无法追上主数据库的日志位置，可能会导致中继日志不断增长。

5. 优化中继日志的写入和读取

针对中继日志的写入和读取过程进行性能优化，可以采取以下措施：

• 配置适当的硬件设备，如快速磁盘和RAID阵列，以提高IO性能。
• 调整MySQL参数，如relay_log_buffer和relay_log_recovery等，以优化中继日志的写入和读取效率。

结论：

解决MySQL Relay Log打满磁盘的问题，可以通过检查复制延迟、中继日志大小、复制状态、二进制日志状态以及性能优化，保证数据库复制的顺利进行，并避免中继日志占满磁盘的情况发生。但是实际环境中的日志打满磁盘问题排查起来非常困难，主要是卡在找出引起回放线程出错的那条语句，很大可能是被purge了，最后的解决方案是不保留备库的数据，因为执行的是迁移任务，满足客户的需求为先。