由于binlog日志太大，导致服务崩了……

背景

有点戏剧性的：在某一天夜里钉钉告警群里疯狂反馈着服务不可用……

排查

看kibana日志，发现服务都是在执行update、insert这些DML命令的时候，报的数据库执行超时。

这……就得喊运维了呀，啥情况这是。

果然是由于硬件资源不足引起的，干了……

问题原因：由于数据库一直在服务，binlog日志记录的DML和DDL的操作都会被记录下来，日积月累，binlog日志竟然比mysql数据文件所占用的空间还多。

说实话，这个错误，我也是第一次见。

借着这次机会，回顾一下mysql的三大日志：undo log、redo log以及binlog

undo log、redo log以及binlog

MySQL中的undo_log（通常简称为undo log）、bin_log（二进制日志，也常称为binlog）以及redo log是三大重要的日志，它们在数据库的事务处理、数据恢复和复制等方面发挥着关键作用。以下是它们各自的作用及一个简化的例子，以及它们写入的顺序。

一、各自作用

1. undo log（回滚日志）

   • 作用：undo log主要用于事务的回滚和多版本并发控制（MVCC）。在事务提交之前，MySQL会记录更新前的数据到undo log中。如果事务执行失败或需要回滚，MySQL可以利用undo log将数据恢复到事务开始前的状态，从而保障事务的原子性。
   • 例子：假设有一个转账操作，从账户A转账100元到账户B。在事务提交前，MySQL会记录账户A和账户B在转账前的余额到undo log中。如果转账过程中发生错误或需要回滚，MySQL会根据undo log中的记录，将账户A和账户B的余额恢复到转账前的状态。

2. bin_log（二进制日志）

   • 作用：binlog记录了数据库中所有的更改操作（如插入、更新、删除等），但不包括查询操作。它主要用于数据恢复、增量备份和MySQL的主从复制。
   • 例子：继续上面的转账操作例子，当事务成功提交后，MySQL会将这个转账操作（包括账户A的扣款和账户B的加款）记录到binlog中。如果数据库发生故障，管理员可以使用binlog来恢复数据；同时，在MySQL的主从复制环境中，从服务器会读取主服务器上的binlog来同步数据。

3. redo log（重做日志）

   • 作用：redo log是InnoDB存储引擎特有的日志，用于记录事务操作的变化。这些变化首先写入redo log中，然后再应用到数据库的数据文件中。redo log的主要作用是在数据库崩溃后，通过重放redo log中的操作来保证数据的持久性。
   • 例子：还是以转账操作为例，当InnoDB存储引擎更新账户A和账户B的余额时，这些更新操作会先写入redo log中。即使数据库在更新过程中崩溃，只要redo log没有损坏，MySQL就可以在重启后通过重放redo log中的操作来恢复数据，确保数据的持久性。

二、写入顺序

在MySQL中，当执行一个事务时，三大日志的写入顺序大致如下：

1. undo log：在事务开始时，首先记录更新前的数据到undo log中。
2. redo log：接着，将事务的修改操作记录到redo log中。这一步是先将操作写入redo log buffer，然后根据配置的不同，可能会立即写入磁盘或稍后写入。
3. bin_log：最后，将事务的修改操作记录到binlog中。需要注意的是，在MySQL 5.6及以后的版本中，引入了二阶段提交（2PC）机制来确保redo log和binlog的一致性。在提交事务时，会先将redo log标记为prepare状态并写入磁盘，然后写入binlog并标记为commit状态，最后再将redo log标记为commit状态。这样，即使在写入binlog过程中发生崩溃，也可以根据redo log和binlog的状态来决定是提交事务还是回滚事务。

总的来说，undo log、redo log和binlog在MySQL中各自承担着不同的职责，通过它们的协同工作，确保了数据库的事务性、持久性和数据一致性。

三、日志文件大小

在MySQL中，哪个日志文件会比较大，这个问题并没有一个绝对的答案，因为它取决于多种因素，包括数据库的负载、事务的频繁程度、配置参数的设置等。根据一般情况和其它参考文章中的信息，对这三个日志文件（undo_log、binlog、redo log）的大小进行一些分析和归纳。

1. undo_log（回滚日志）

• 大小因素：undo_log的大小主要取决于事务的频繁程度和每个事务中修改的数据量。如果事务非常频繁，且每个事务修改的数据量很大，那么undo_log可能会占用较多的空间。
• 空间回收：MySQL会定期通过purge线程来清理不再需要的undo日志，以释放空间。因此，undo_log的大小并不会无限制地增长。

2. binlog（二进制日志）

• 大小因素：binlog记录了数据库中所有的DDL和DML语句（除了SELECT和SHOW），因此它的大小可能会随着数据库活动的增加而迅速增长。特别是当数据库中存在大量数据更新操作时，binlog会迅速变大。
• 配置参数：max_binlog_size参数可以控制单个binlog文件的大小，当文件达到这个大小时，会自动切换到下一个文件。但是，这并不意味着binlog的总体大小会受到限制，因为随着数据库活动的持续进行，会不断产生新的binlog文件。
• 过期时间：binlog_expire_logs_seconds参数可以设置binlog文件的过期时间，超过这个时间的binlog文件会被自动删除，但这需要管理员手动执行清理操作或配置相应的自动清理机制。

3. redo log（重做日志）

• 大小因素：redo log的大小通常是由配置参数innodb_log_file_size决定的，每个redo log文件的大小是固定的。但是，MySQL允许配置多个redo log文件组成一个redo log组（innodb_log_files_in_group参数），以支持更大的日志空间。
• 循环使用：当redo log文件被写满后，MySQL会开始覆盖最旧的日志记录，以实现日志的循环使用。这意味着redo log的大小虽然受到配置参数的限制，但在实际使用中，其占用的磁盘空间并不会持续增加。

总结

• 在一般情况下，如果数据库活动非常频繁，特别是存在大量数据更新操作时，binlog可能会成为最大的日志文件，因为它记录了所有的数据变更操作。
• undo_log的大小会受到事务频繁程度和每个事务修改数据量的影响，但MySQL会定期清理不再需要的undo日志，以控制其大小。
• redo log的大小由配置参数决定，并通过循环使用来限制其占用的磁盘空间。

需要注意的是，以上分析仅基于一般情况，实际情况中日志文件的大小还会受到其他因素的影响，如数据库的配置、硬件性能、负载模式等。因此，在评估日志文件大小时，需要综合考虑多种因素。

总结

1. 线上由于binlog日志太大，服务器磁盘硬件资源不足，导致服务不可用。
2. 借着这个问题，回顾了一下mysql的三大日志文件的功能以及写入顺序和影响各日志文件大小的因素。