Automating MySQL Slow Query Log Management with Logrotate

Efficiently archive and maintain your MySQL slow query logs with logrotate, ensuring optimal performance and a well-managed database audit trail for performance analysis

MinervaDB MySQL Maintenance Operations Teams

To archive the slow query log in MySQL using logrotate, you usually need to create a configuration file for logrotate. This file specifies the frequency of log rotation, the number of rotations to retain, and any other actions to perform during rotation.

Here is a step-by-step guide with detailed scripts:

1. Locate or Set Up the Slow Query Log in MySQL: First, ensure that the slow query log is enabled in your MySQL configuration file (my.cnf or my.ini):

The above configuration enables the slow query log and specifies the file where the logs are written. It also sets the threshold for slow queries (long_query_time) to 2 seconds.

2. Create a Logrotate Configuration: Create a new logrotate configuration file, such as /etc/logrotate.d/mysql-slow.:

Explanation of the directives:

  • weekly: This directive rotates the logs each week.
  • rotate 4: It keeps 4 weeks of backlogs.
  • compress: This directive compresses (gzip) the log files on rotation.
  • delaycompress: Delay the compression until the next log rotation cycle.
  • missingok: It does not output an error if the log file is missing.
  • notifempty: The log will not be rotated if it is empty.
  • create 640 mysql mysql: This directive creates new log files with the specified permissions, user, and group.
  • postrotate/endscript: These directives specify the commands to run after the log file is rotated. The kill -s USR1 command sends a signal to MySQL to flush the logs.

3. Testing Your Logrotate Configuration: You can test your logrotate configuration by using the following command:

The -d flag instructs logrotate to run in debug mode, while the -v flag enables verbose mode. Using these flags won’t rotate the logs but will instead show you the actions that would be taken if the command were to run.

4. Automating with Cron:logrotate is typically configured to run automatically via cron. Depending on your system, there may already be a cron job set up for logrotate. You can check the existing crontab by using the following command:

Or you can look in /etc/cron.daily, /etc/cron.weekly, and other directories for a logrotate script. If it is not already set up, you can add an entry to your crontab.

And add the following to run logrotate daily:

5. Activate Changes: Once you have saved your logrotate configuration and set up the cron job, logrotate will handle the slow query log according to the specified settings.

Make sure that you have the necessary permissions to create and modify these files, and adjust the paths and commands to match your server setup. Additionally, there is no need to restart anything after modifying the logrotate configuration; the changes will take effect during the next cron cycle when logrotate runs.

About Shiv Iyer 390 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.