MySQL日志突然暴涨

1. 现象

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

=====================================2020-07-0813:47:430x7fe3723ff700 INNODB MONITOR OUTPUT=====================================Per second averages calculated fromthe last1 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 28112548srv_active,0srv_shutdown,18948137 srv_idle

srv_master_thread log flush and writes: 47060685----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 213360683OS WAIT ARRAY INFO: reservation count 218012898OS WAIT ARRAY INFO: reservation count 218624956OS WAIT ARRAY INFO: reservation count 223392430OS WAIT ARRAY INFO: reservation count 213358783OS WAIT ARRAY INFO: reservation count 217996917OS WAIT ARRAY INFO: reservation count 218627068OS WAIT ARRAY INFO: reservation count 223399094OS WAIT ARRAY INFO: reservation count 213372264OS WAIT ARRAY INFO: reservation count 217974752OS WAIT ARRAY INFO: reservation count 218606657OS WAIT ARRAY INFO: reservation count 223387430OS WAIT ARRAY INFO: reservation count 213382268OS WAIT ARRAY INFO: reservation count 218029924OS WAIT ARRAY INFO: reservation count 218619464OS WAIT ARRAY INFO: reservation count 223399870OS WAIT ARRAY INFO: signal count 2558329753RW-shared spins0, rounds2208700138, OS waits822920663RW-excl spins0, rounds80631903713, OS waits1603642807RW-sx spins1202513351, rounds33533328545, OS waits959708531Spin rounds per wait: 2208700138.00RW-shared,80631903713.00RW-excl,27.89RW-sx------------------------LATEST DETECTED DEADLOCK------------------------2020-04-2119:50:050x7fe28a7fd700...

...

...

Process ID=54642, Main thread ID=140614440048384, state: sleeping

Number of rows inserted 5475421722, updated433989820, deleted4122238559, read669572614313708.29inserts/s,34.97updates/s,573.43deletes/s,29898.10reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

此内容是InnoDB MONITOR的结果（和show engine innodb status结果一样），也就是说开启了InnoDB监视器。

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

mysql>show  global  variableslike'%innodb_status%';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|innodb_status_output|ON||innodb_status_output_locks|ON|+----------------------------+-------+2rowsinset(0.01sec)

 说明确实已经开启了。

2. 关闭INNODB MONITOR

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

mv  mysqld.logmysqld.log.20200708

再修改参数，关闭监控

mysql>setglobal innodb_status_output='OFF';

Query OK, 0rows affected (0.00 sec)

mysql>setglobal innodb_status_output_locks='OFF';

Query OK, 0rows affected (0.00 sec)

mysql>flush logs;

时间有限，今天暂时就简单说明现象及引起日志暴涨的原因，后续有机会在关注日志内容及相关参数意义。

想了解更多内容或参与技术交流可以关注微信公众号【数据库干货铺】或进技术交流群沟通。