The error log is an important tool for troubleshooting. The size of the error log file can get quite large if left unattended. For starters, there are not many parameters that can be adjusted to impact its size in any way, unlike the binlog that has a few system variables that control its size, rotation, and retention. Why the difference? Your guess is as good as mine. It might be that binlogs have a specific function that has an inverse correlation with time: meaning, the more time that passes, the less functional the binlog file becomes. In contrast, errors in the error log could be looked at in the future and still provide valuable information.
How much information you want in the error log and how long you want to keep them depends on the needs of your application. Knowing what exactly is in the error log and what types of errors are logged can help. The default error log settings include errors, warnings, and notes. An error is an issue that causes a process or query to stop, whereas a warning is unexpected behavior that occurs but it does not stop a process or query from executing. Other information or notes pertain to events or helpful suggestions like if the server stopped running or a table needs to be repaired. For more details, review error messages and common problems in the MySQL documentation.
The variables that control what content is included in the error log are log_warnings and log_error_verbosity. After MySQL 5.7.2, log-error-verbosity should be used instead of log_warnings. Before adjusting the variables, consider how useful warnings and notes can be to you when an error message is thrown. The table below gives more details on the variables’ function and values.
| Variable | Function | Values | Default |
log-warnings | Controls what additional information is printed to the error log. | 0 – only errors are included1 – also includes warnings2 – errors, warnings, and notes are included | 2 |
log-error-verbosity | Controls which are printed to the error log. | 1 – permits error messages only2 – permits error and warning messages3 – permits errors, warnings, and information | 3 |
As mentioned earlier, the error log size should be monitored because it can get large. Fortunately, it is easy to rotate and flush the error log.
The first step is renaming the current error log. The default location on UNIX/Linux servers is /var/log/mysql. On a Windows server, the error log can be found in the C:\ProgramData\MySQL\MySQL Server [version number]\Data\ directory. You can copy or move the file using the following commands.
Go to the error log directory. The command below is for a UNIX/Linux server.
Rename the file in the same directory or copy the file to another location. On Windows, use rename instead of mv. The command below will append the date to the file name. (Note that those are backticks around the date command.)
The next step is to flush the error log, which can be done from the command line or in MySQL. From the command line, flush the error log using mysqladmin. On a Windows server, this command needs to be executed in the C:\ProgramData\MySQL\MySQL Server [version number]\bin\ directory. The user needs to have RELOAD privileges.
In MySQL, run one of the following commands.
Check if the new file exists and is empty.
Compress the old file to save space.
These steps can be used to create a script that can be executed in a cronjob. Red Hat operating systems already have a script for you, mysql-log-rotate, but should be used with caution. This script also flushes the binary log, which can stop replication if binlogs are deleted before data changes are relayed to the replica.
More information about server log maintenance can be found in the MySQL Reference Manual.
If you have any more questions, feel free to comment below, or contact us!