面试题：MariaDB binlog专家级故障排查与配置

Question

在MariaDB运行过程中，发现binlog出现异常，部分事务的日志记录不完整，在不影响业务正常运行的前提下，如何通过分析现有binlog相关参数配置（如log_bin_trust_function_creators、binlog_row_image等），结合数据库运行状态来定位并解决该问题？阐述详细的排查步骤和解决方案。

Answer 1

排查步骤

1. 检查 log_bin_trust_function_creators 参数：
   • 运行 SHOW VARIABLES LIKE 'log_bin_trust_function_creators'; 查看该参数值。
   • 如果该参数设置为 OFF，某些含有自定义函数的事务在写入 binlog 时可能会出现问题。因为 OFF 状态下，MariaDB 不允许在主服务器上创建或修改未声明为 DETERMINISTIC 或未使用 SQL SECURITY INVOKER 的存储函数。若函数不符合要求且此参数为 OFF，可能导致 binlog 记录不完整。
2. 检查 binlog_row_image 参数：
   • 运行 SHOW VARIABLES LIKE 'binlog_row_image'; 查看该参数值。
   • 该参数有三个值：FULL、MINIMAL 和 NOBLOB。FULL 会记录行的完整图像，MINIMAL 只记录修改的列，NOBLOB 记录除了 BLOB 类型之外的完整行图像。如果设置为 MINIMAL，在某些复杂更新场景下，可能无法完整记录事务，导致 binlog 不完整。例如，当更新操作涉及到多个关联表，且某些列的更新依赖于其他表的计算结果时，MINIMAL 模式可能无法正确记录。
3. 查看数据库运行状态：
   • 使用 SHOW ENGINE INNODB STATUS; 查看 InnoDB 引擎状态，重点关注 TRANSACTIONS 部分。查看是否有长时间运行的事务，长时间运行的事务可能会影响 binlog 的写入。例如，事务持有大量锁，导致后续事务等待，可能影响 binlog 记录的及时性和完整性。
   • 使用 SHOW STATUS LIKE 'Binlog_%'; 查看 binlog 相关状态信息，如 Binlog_cache_disk_use 和 Binlog_cache_use。如果 Binlog_cache_disk_use 值较高，说明 binlog 缓存频繁使用磁盘，可能存在性能问题影响 binlog 记录。
4. 检查 binlog 日志文件：
   • 查看 binlog 文件的大小和数量，使用 SHOW BINARY LOGS; 命令。如果 binlog 文件过大或数量过多，可能存在写入异常。例如，单个 binlog 文件达到配置的最大大小后，可能在切换过程中出现问题，导致部分事务记录不完整。
   • 使用 mysqlbinlog 工具查看 binlog 文件内容，分析具体的事务记录缺失情况。例如，通过查看 binlog 中的 BEGIN、COMMIT 等事务标记，以及具体的 SQL 操作记录，确定缺失的事务部分。

解决方案

1. 针对 log_bin_trust_function_creators 参数问题：
   • 如果确实存在自定义函数且需要在 binlog 中正确记录，可将 log_bin_trust_function_creators 设置为 ON。但需要注意，这样做会降低安全性，因为允许创建可能非确定性的函数。建议在修改参数后，对所有自定义函数进行审查，确保函数的确定性。可以在 my.cnf 文件中添加或修改 log_bin_trust_function_creators = 1，然后重启 MariaDB 服务使参数生效。
2. 针对 binlog_row_image 参数问题：
   • 如果确定是 MINIMAL 模式导致的 binlog 记录不完整，可根据业务需求调整为 FULL 模式。在 my.cnf 文件中添加或修改 binlog_row_image = FULL，然后重启 MariaDB 服务。这样可以确保在更新操作时，记录完整的行图像，避免因部分列记录缺失导致的 binlog 不完整问题。
3. 针对长时间运行事务问题：
   • 分析长时间运行事务的原因，可能是复杂的业务逻辑、锁争用等。对于复杂业务逻辑，尝试优化业务流程，将大事务拆分为多个小事务。例如，在涉及多个表更新的操作中，按照业务逻辑顺序，依次提交每个表的更新事务。
   • 对于锁争用问题，使用 SHOW ENGINE INNODB STATUS; 中的 LATEST DETECTED DEADLOCK 部分分析死锁原因，调整事务执行顺序或优化锁的使用，避免死锁和长时间的锁等待。
4. 针对 binlog 缓存问题：
   • 如果 Binlog_cache_disk_use 值较高，可适当增大 binlog 缓存大小。在 my.cnf 文件中增加或修改 binlog_cache_size 参数，例如 binlog_cache_size = 64M。增大缓存可以减少 binlog 缓存使用磁盘的频率，提高 binlog 写入性能，从而减少因缓存不足导致的 binlog 记录问题。
5. 针对 binlog 文件问题：
   • 如果 binlog 文件过大，可调整 max_binlog_size 参数，在 my.cnf 文件中修改该参数值，例如 max_binlog_size = 512M。适当减小单个 binlog 文件的最大大小，可以降低在 binlog 文件切换过程中出现问题的概率。同时，定期清理过期的 binlog 文件，使用 PURGE BINARY LOGS 命令，避免 binlog 文件过多占用磁盘空间。例如，PURGE BINARY LOGS BEFORE '2024 - 01 - 01 00:00:00'; 可删除指定时间之前的 binlog 文件。