面试题：MariaDB主从复制故障排查及延迟处理策略

Question

假设你负责的MariaDB主从复制出现故障，从库延迟严重。请描述你会采取哪些步骤进行故障排查，以及如何制定有效的策略来解决从库延迟问题。

Answer 1

故障排查步骤

1. 检查网络连接
   • 利用ping命令检查主从服务器之间的网络连通性，确保网络没有丢包或高延迟情况。例如在从库服务器执行ping 主库IP。
   • 使用traceroute命令查看网络路由，确认数据传输路径是否正常，命令如traceroute 主库IP。
2. 查看主库状态
   • 在主库执行SHOW MASTER STATUS\G，检查主库的二进制日志文件和位置，确保主库正常写入二进制日志。
   • 检查主库的负载情况，使用top命令查看CPU、内存等资源使用情况，过高的负载可能导致主库处理复制相关事务缓慢。
3. 查看从库状态
   • 在从库执行SHOW SLAVE STATUS\G，重点关注以下参数：
     • Slave_IO_Running和Slave_SQL_Running，确保这两个线程都处于Yes状态。如果Slave_IO_Running为No，可能是网络或权限问题；如果Slave_SQL_Running为No，则通常是SQL执行错误。
     • Seconds_Behind_Master，这个值表示从库延迟主库的大概时间（秒），若数值很大则表明延迟严重。
     • Last_IO_Errno和Last_IO_Error、Last_SQL_Errno和Last_SQL_Error，查看是否有相关错误信息，根据错误提示解决问题。
4. 检查复制用户权限
   • 确认从库连接主库使用的复制用户具有正确的权限。在主库上查看用户权限，例如SHOW GRANTS FOR '复制用户名'@'从库IP';，确保该用户有REPLICATION SLAVE权限。
5. 排查大事务
   • 在主库上查看是否存在长时间运行的大事务。可以通过SHOW ENGINE INNODB STATUS\G，在TRANSACTIONS部分查看活跃事务情况，大事务可能导致从库延迟。
   • 在从库上也执行同样命令，查看是否有长时间未提交的事务阻塞了复制。
6. 检查从库硬件资源
   • 利用top、free -h等命令查看从库的CPU、内存、磁盘I/O等资源使用情况。磁盘I/O性能低下（如磁盘空间不足、I/O队列过长等）可能导致从库写入中继日志或执行SQL缓慢。

解决策略

1. 优化网络
   • 如果网络存在问题，联系网络管理员优化网络配置，如调整网络带宽、修复网络设备故障等。
   • 可以考虑在主从服务器之间增加网络链路冗余，提高网络可靠性。
2. 处理主库负载
   • 如果主库负载过高，对主库的业务进行优化，如优化SQL查询，对频繁读写的表进行合理的索引优化。
   • 可以考虑将部分读操作分担到从库，减轻主库压力。
3. 解决从库复制错误
   • 如果Slave_IO_Running为No且是权限问题，在主库上重新授予复制用户正确权限，并在从库上重新配置复制。
   • 如果Slave_SQL_Running为No，根据Last_SQL_Error的错误提示解决问题。例如，如果是表结构不一致导致的错误，需要先暂停从库复制（STOP SLAVE;），在从库上修正表结构，然后再启动复制（START SLAVE;）。
4. 处理大事务
   • 在主库上，尽量避免长时间运行的大事务，将大事务拆分成多个小事务执行。
   • 如果从库因大事务导致延迟，在确认主库上该事务已经提交后，可以在从库上通过SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;跳过该事务（此方法需谨慎使用，可能会导致数据不一致，仅适用于特殊情况）。
5. 优化从库硬件资源
   • 如果磁盘空间不足，清理从库上不必要的文件，释放磁盘空间。
   • 如果CPU或内存资源不足，可以考虑升级硬件配置，或者优化从库上运行的其他服务，减少资源竞争。
6. 调整复制参数
   • 可以适当调整从库的innodb_flush_log_at_trx_commit参数，设为2（默认为1），减少I/O操作，但可能会在崩溃时丢失部分未刷入磁盘的日志数据。
   • 在从库上启用多线程复制，通过设置slave_parallel_type = LOGICAL_CLOCK和slave_parallel_workers参数（根据服务器CPU核心数合理设置），提高从库复制效率。