Rotating MySQL slow query logs is a process of periodically moving the slow query logs to a new file to keep the logs organized and limit the size of log files. This process is important to prevent the logs from becoming too large and slowing down the system.

There are two ways to automatically rotate MySQL slow query logs:

  1. Using logrotate: Logrotate is a Linux tool for rotating log files. It can be used to rotate the slow query logs by adding a configuration file for the logs in the logrotate configuration directory. The configuration file should specify the path to the slow query log file, the frequency of rotation and the size of the log files.

    Example:

    /var/log/mysql/slow.log {
    size 100M
    rotate 4
    compress
    delaycompress
    missingok
    create 640 mysql MySQL
    }
  2. Using the MySQL configuration file:
    You can also configure MySQL to rotate the slow query logs automatically by adding the following line to the my.cnf file:

slow_query_log_file = /var/log/mysql/slow.log
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
expire_logs_days = 7

In this example, the slow query logs are saved in the /var/log/mysql/slow.log file and will rotate automatically after 7 days. The expire_logs_days variable sets the number of days after which the logs will be rotated.

It is important to keep rotating the slow query logs to prevent them from becoming too large, which can slow down the system. In addition, rotating the logs periodically makes it easier to identify performance issues and track database usage patterns.