MySQL日志文件突然暴涨是怎么回事？

1、现象

今天协助其他同学排查问题的时候，发现数据库错误日志文件已经有9G以上了，打开内容查看如下：

=====================================
2020-07-0813:47:430x7fe3723ff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops:28112548 srv_active,0 srv_shutdown,18948137 srv_idle
srv_master_thread log flush and writes:47060685
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count213360683
OS WAIT ARRAY INFO: reservation count218012898
OS WAIT ARRAY INFO: reservation count218624956
OS WAIT ARRAY INFO: reservation count223392430
OS WAIT ARRAY INFO: reservation count213358783
OS WAIT ARRAY INFO: reservation count217996917
OS WAIT ARRAY INFO: reservation count218627068
OS WAIT ARRAY INFO: reservation count223399094
OS WAIT ARRAY INFO: reservation count213372264
OS WAIT ARRAY INFO: reservation count217974752
OS WAIT ARRAY INFO: reservation count218606657
OS WAIT ARRAY INFO: reservation count223387430
OS WAIT ARRAY INFO: reservation count213382268
OS WAIT ARRAY INFO: reservation count218029924
OS WAIT ARRAY INFO: reservation count218619464
OS WAIT ARRAY INFO: reservation count223399870
OS WAIT ARRAY INFO: signal count2558329753
RW-shared spins 0, rounds 2208700138, OS waits 822920663
RW-excl spins 0, rounds 80631903713, OS waits 1603642807
RW-sx spins 1202513351, rounds 33533328545, OS waits 959708531
Spin rounds per wait:2208700138.00 RW-shared,80631903713.00 RW-excl,27.89 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-04-2119:50:050x7fe28a7fd700
...
...
...
Process ID=54642, Main thread ID=140614440048384, state: sleeping
Number of rows inserted 5475421722, updated 433989820, deleted 4122238559, read 669572614313
708.29 inserts/s,34.97 updates/s,573.43 deletes/s,29898.10 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

此内容是InnoDB MONITOR的结果（和show engine innodb status结果一样），也就是说开启了InnoDB监视器。而且每15s刷新一次，导致日志文件的增速较快。

InnoDB MONITOR主要涉及的参数有innodb_status_output 和innodb_status_output_locks ，这两个系统变量是用来启用标准innodb监控和 InnoDB锁监控的，查看本实例参数情况：

mysql> show  global  variables like'%innodb_status%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       |ON|
| innodb_status_output_locks |ON|
+----------------------------+-------+
2 rows inset(0.01 sec)

说明确实已经开启了。

2、关闭INNODB MONITOR

可以在线关闭INNODB MONITOR，但是在关闭前建议先将原日志备份（重命名）

mv  mysqld.log   mysqld.log.20200708

再修改参数，关闭监控

mysql>set global innodb_status_output='OFF';
Query OK,0 rows affected (0.00 sec)
mysql>set global innodb_status_output_locks='OFF';
Query OK,0 rows affected (0.00 sec)
mysql> flush logs;