How to disable MySQL error logging?

Question

My error file(/var/log/mysql/error.log) is increased rapidly（10% of disk usage per hour） with only this entry:

[ERROR] Error in accept: Bad file descriptor

and we can only shutdown the server down in the midnight to investigate further, now I need to find a way to stop the error file taking up all space of my disk.

I've tried something like set GLOBAL general_log = 0, but it's not working at all, also tried to flush error logs, a new error file(error.log.1) is created, but the process(mysqld) keeps writing the the old file(error.log).

please help, ask me if you need more information.

DEBUG INFO

Mysql Server version: 5.7.18-0ubuntu0.16.04.1

Answer 1

To find error log location, run the below command

mysql> show global variables like '%log_error%';

As you mentioned, set GLOBAL general_log = 0 - this command used to disable general log. Since it is a space issue, identify, whether it is due to error log or general log. If general log, disable it, if it's due to error log; flush and rename the error log.

Note: If you flush the logs using FLUSH LOGS or FLUSH ERROR LOGS commands, the MySQL will closes and reopens the error log, but it will keep on writing to same error log file.

In order to create a new file, rename the original file before performing the FLUSH operation, so that, when you run FLUSH LOGS or FLUSH ERROR LOGS the MySQL will close and open new file and start writing to new file.

mv /var/log/mysql/error.log /var/log/mysql/error_old.log
mysqladmin flush-logs

If the error log file size keep on increasing, the root cause should be identified and removed. Managing the size is not a solution, just a temporary workaround. Open the error log and start debugging and remove the errors, this will keep the error log size within limit.

If troubleshooting errors not in your hand, just make the FLUSHING operation as scheduled task and take backup of the error log. This can be done as below:

mv /var/log/mysql/error.log /var/log/mysql/error_old.log
mysqladmin flush-logs
mv /var/log/mysql/error_old.log /backupdir

I hope this answer will help you, Thanks!