Efficiently archive and maintain your MySQL slow query logs with logrotate, ensuring optimal performance and a well-managed database audit trail for performance analysisMinervaDB 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 (
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
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
create 640 mysql mysql
# The following line assumes you're using systemd. If you're not, you might use something like 'invoke-rc.d mysql reload > /dev/null'
/bin/systemctl kill -s USR1 mysqld.service
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.
endscript: These directives specify the commands to run after the log file is rotated. The
kill -s USR1command 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:
logrotate -dv /etc/logrotate.d/mysql-slow
-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:
@daily /usr/sbin/logrotate /etc/logrotate.conf
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