mysql磁盘暴增问题

问题描述

在排查容器存储的时候，忽然发现有部分mysql的数据库磁盘空间消耗极大，最高甚至飙升到22G左右。为了防止把应用分配的磁盘空间撑爆；开始对这几个数据进行问题排查和分析

问题分析

首先怀疑是使用这几个数据的应用向数据库中写入了大量异常数据，并且还没有做定时清理的操作。经过在数据库中表格中的排查，发现实际在表格中存储的数据并不多

然后在检查数据库持久化文件夹的时候发现一大堆binlog日志，并且每个日志文件大小有差不多1G左右。这里基本上可以确认导致磁盘数据量剧增的原因就是这些binlog日志了

binlog基本概念

binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，记录了所有的DDL和DML(除了数据查询语句)语句，并以事务的形式保存在磁盘中，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

binlog的三种存储模式

Row
优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。
缺点：row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，会产生大量的日志内容。
Statement
优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要在Master上锁执行的语句的细节，以及执行语句的上下文的信息。
缺点：由于只记录语句，所以，在statement level下已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。
Mixed
在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

命令查看配置
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name                   | Value                                       |
+---------------------------------+---------------------------------------------+
| log_bin                         | ON                                          |
| log_bin_basename                | D:\Program Files\MySQL\data\mysql-bin       |
| log_bin_index                   | D:\Program Files\MySQL\data\mysql-bin.index |
| log_bin_trust_function_creators | OFF                                         |
| log_bin_use_v1_row_events       | OFF                                         |
| sql_log_bin                     | ON                                          |
+---------------------------------+---------------------------------------------+
6 rows in set (0.07 sec)

查看binlog文件列表
mysql>  show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       202 | No        |
| mysql-bin.000002 |      2062 | No        |
+------------------+-----------+-----------+
2 rows in set (0.07 sec)

查看日志状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     2062 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.08 sec)

刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)

mysql>  show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       202 | No        |
| mysql-bin.000002 |      2109 | No        |
| mysql-bin.000003 |       155 | No        |
+------------------+-----------+-----------+
3 rows in set (0.07 sec)

重置(清空)所有binlog日志
mysql> reset master;

通过查找mysql的默认配置发现，mysql是默认开启binlog日志的并且对于已经存储的binlog日志没有设置自动清理时间，最蛋疼的是mysql在5.7.7版本之后默认binlog存储格式改为了ROW（也就是最详细最消耗磁盘的一种）刚好那几个磁盘飙升的应用恰好是有大量数据新增和删除的应用，所以会出现binlog日志20多G的情况。

问题解决

在mysql的my.cnf中进行如下修改

# 30天过期清理binlog日志
expire_logs_days = 30
# 修改记录模式为statement
binlog_format = statement

参考

https://www.cnblogs.com/brucetang/p/9733999.html

https://www.cnblogs.com/hongdada/p/10983768.html

https://www.jianshu.com/p/5f39c486561b