Overview

I find far too often that MySQL error and slow query logs are unaccounted for.  Setting up log rotation helps make the logs manageable in the event that they start to fill up and can help make your troubleshooting of issues more efficient.

Setup

All steps in the examples below are run as the root user. The first step is to setup a user that will perform the log rotation.  It is recommended to only give enough access to the MySQL user for the task that it is performing.

Create Log Rotate MySQL User

mysql > CREATE USER 'log_rotate'@'localhost' IDENTIFIED BY '<ENTER PASSWORD HERE>';
mysql > GRANT RELOAD,SUPER ON *.* to 'log_rotate'@'localhost';
mysql > FLUSH PRIVILEGES;</pre>

The next step is to setup the MySQL authentication config as root.  Here are two methods to set this up.  The first method will be the more secure method of storing your MySQL credentials using mysql_config_editor as the credentials are stored encrypted. But this first method is only available with Oracle MySQL or Percona MySQL client greater than 5.5. It is not available with Maria DB MySQL client. Method 2 can be used with pretty much any setup but is less secure as the password is stored in plain text.

Method 1

bash # mysql_config_editor set \
  --login-path=logrotate \
  --host=localhost \
  --user=log_rotate \
  --port 3306 \
  --password

Method 2

bash # vi /root/.my.cnf
[client]
user=log_rotate
password='<ENTER PASSWORD HERE>'
bash # chmod 600 /root/.my.cnf

Now we will test to make sure this is working as expected

Method 1

bash # mysqladmin --login-path=logrotate ping

Method 2

bash # mysqladmin ping

The paths for the error and slow query log will need to be gathered in order to place them in the logrotate config file

Method 1

bash # mysql --login-path=logrotate -e "show global variables like 'slow_query_log_file'"
bash # mysql --login-path=logrotate -e "show global variables like 'log_error'"

Method 2

bash # mysql -e "show global variables like 'slow_query_log_file'"
bash # mysql -e "show global variables like 'log_error'"

Finally we will create the log rotation file with the following content. Make sure to update your error and slow query log paths to match the paths gathered in previous steps. Start by opening up the editor for a new mysql logrotate file in the /etc/logrotate.d directory.

Method 1 Content

bash # vi /etc/logrotate.d/mysql
/var/lib/mysql/error.log /var/lib/mysql/slow.queries.log {
  create 600 mysql mysql
  daily
  rotate 30
  missingok
  compress
  sharedscripts
  postrotate
    if test -x /usr/bin/mysqladmin &&
      env HOME=/root /usr/bin/mysqladmin --login-path=logrotate ping > /dev/null 2>&1
    then
      env HOME=/root/ /usr/bin/mysql --login-path=logrotate -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log=0; select sleep(2); FLUSH ERROR LOGS; FLUSH SLOW LOGS;select sleep(2); set global long_query_time=@lqt_save; set global slow_query_log=1;' > /var/log/mysqladmin.flush-logs 2>&1
    fi
  endscript
}

Method 2 Content

bash # vi /etc/logrotate.d/mysql
/var/lib/mysql/error.log /var/lib/mysql/slow.queries.log {
  create 600 mysql mysql
  daily
  rotate 30
  missingok
  compress
  sharedscripts
  postrotate
    if test -x /usr/bin/mysqladmin &&
      env HOME=/root /usr/bin/mysqladmin ping > /dev/null 2>&1
    then
      env HOME=/root/ /usr/bin/mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log=0; select sleep(2); FLUSH ERROR LOGS; FLUSH SLOW LOGS;select sleep(2); set global long_query_time=@lqt_save; set global slow_query_log=1;' > /var/log/mysqladmin.flush-logs 2>&1
    fi
  endscript
}

Validation

For final validation force a log rotate. Update the path in the ls command to match the path of the logs gathered earlier.

bash # logrotate --force /etc/logrotate.d/mysql
bash # ls -al /var/lib/mysql