How to Rotate MySQL Slow and Error Logs Using Logrotate

Databases generate tons of logs, and it’s essential to manage them correctly. Logs help DBAs understand what’s happening behind the scenes, trace errors, and optimize the performance of slow queries. However, these logs can become a hassle over time, especially if they consume too much disk space and are not appropriately managed.

Why Rotate Logs?

Logs are like the diary of a system; they keep track of everything. Logs can grow quite large over time and become an issue for disk space (especially when using higher log_error_verbosity or low long_query_time). Not to mention, sifting through an enormous log file can be a tedious task. This is why rotating logs become essential, especially in databases like MySQL.

Setting Logrotate

While MySQL takes care of some of its logs, certain logs, like the error and slow query logs, aren’t automatically rotated. Thankfully, on most Linux distributions, a tool called logrotate is explicitly designed to handle this task.

Setting up logrotate for MySQL is relatively straightforward. Here’s a step-by-step guide:

1. Create a Configuration File: Create a configuration file specifically for MySQL. Use the command:

1	$ sudo vi /etc/logrotate.d/mysql

2. Add Configuration Content: Inside this file, add the following content:

1 2 3 4 5 6 7 8 9 10 11 12 13

/var/log/mysql/mysql_error.log /var/log/mysql/slow_query.log { compress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate     /usr/bin/mysql -e 'FLUSH SLOW LOGS; FLUSH ERROR LOGS;' endscript rotate 30 }

Note: Ensure you update the MySQL error log and Slow Query log filenames in the first line according to your server’s configuration. You can check your log paths with the query:

1	SELECT @@global.log_error, @@global.slow_query_log_file;

3. Understanding the Configuration:

– compress: Compresses the rotated logs to save space.
– create 660 mysql mysql: Ensures the new log files have the correct permissions and ownership.
– size 1G : Rotates the log once it reaches 1 Gigabyte in size.
– dateext: Adds a date extension to the rotated log filename.
– notifempty : Do not rotate the log file if it is empty.
– missingok: If the log file is missing, go to the next one without issuing an error message.
–sharedscripts: tells logrotate to check all the logs for that configuration block before running the postrotate script. The script runs only once if one or both logs are rotated. If none of the logs is rotated, the postrotate script doesn’t run.
– postrotate: Commands to run after log rotation.
– rotate 30: Keeps 30 rotated log files before deleting the oldest.

4. Activate and Test: After you’ve saved and closed the configuration file, logrotate will automatically pick it up during its next run. You can also force a run to test your configuration:

1	$ logrotate --force /etc/logrotate.d/mysql

5. Further Reading: As with all tools, there’s much more to logrotate than the basic setup. The documentation provides a deep dive: [Logrotate Documentation]

In conclusion, while logs are invaluable for maintaining and troubleshooting systems, managing them efficiently ensures your systems run smoothly. logrotate offers a handy solution to keep your MySQL logs in check.