Rotating Logs on Unix and Linux

Unix and Linux distributions offer the logrotate utility, which makes it very easy to rotate log files. This page will describe how to configure log rotate for the error log, general query log, and the slow query log.

Configuring Locations and File Names of Logs

The first step is to configure the locations and file names of logs. To make the log rotation configuration easier, it can be best to put these logs in their old directory.

We will need to configure the following:

• The error log location and file name is configured with the log_error system variable.
• The general query log location is configured with the general_log_file system variable.
• The slow query log is configured with the slow_query_log_file system variable.

If you want to enable the general query log and slow query log immediately, then you will also have to configure the following:

• The general query log is enabled with the general_log system variable.
• The slow query log is enabled with the slow_query_log system variable.

These options can be set in a server option group in an option file prior to starting up the server. For example, if we wanted to put our log files in /var/log/mysql/, then we could configure the following:

[mariadb]
...
log_error=/var/log/mysql/mariadb.err
general_log
general_log_file=/var/log/mysql/mariadb.log
slow_query_log
slow_query_log_file=/var/log/mysql/mariadb-slow.log
long_query_time=0

We will also need to create the relevant directory:

sudo mkdir /var/log/mysql/
sudo chown mysql:mysql /var/log/mysql/

After MariaDB is restarted, it will use the new log locations and file names.

Configuring Authentication for Logrotate

The logrotate utility needs to be able to authenticate with MariaDB in order to flush the log files.

The easiest way to allow that is to use unix_socket authentication for the root@localhost user account. This will be enabled by default in MariaDB 10.4 and later. However, in prior versions of MariaDB, you will have to explicitly enable it.

The first step is to install the unix_socket plugin:

INSTALL SONAME 'auth_socket';

The next step is to alter the root@localhost user account to use this plugin. How this is done depends on the version of MariaDB.

ALTER USER root@localhost IDENTIFIED VIA unix_socket;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;

Configuring Logrotate

At this point, we can configure the logrotate utility to rotate the log files. Logrotate configuration files are placed in /etc/logrotate.d/ on many systems. We can create our configuration file with the following command:

sudo tee /etc/logrotate.d/mariadb <<EOF
/var/log/mysql/* {
        create 600 mysql mysql
        notifempty
        daily
        rotate 30
        missingok
        compress
        delaycompress
        sharedscripts 
        olddir archive/
    postrotate
        # just if mysqld is really running
        if test -x /usr/bin/mysqladmin && \
           /usr/bin/mysqladmin ping &>/dev/null
        then
           /usr/bin/mysqladmin --local flush-error-log \
              flush-engine-log flush-general-log flush-slow-log
        fi
    endscript
EOF

This configuration compresses and archives the log files in /var/log/mysql/archive/. It performs rotations daily, and it keeps 30 old copies of each log file.

We will also need to create the relevant directory:

sudo mkdir /var/log/mysql/archive/
sudo chown mysql:mysql /var/log/mysql/archive/

Testing Log Rotation

We can test log rotation by executing the logrotate utility with the --force option. For example:

sudo logrotate --force /etc/logrotate.d/mariadb

After a couple tests, we can see that the log rotation is indeed working:

$ sudo ls -l /var/log/mysql/archive/
total 24
-rw-rw---- 1 mysql mysql 5916 Mar 28 15:56 mariadb.err.1
-rw------- 1 mysql mysql  636 Mar 28 16:39 mariadb.log.1
-rw-rw---- 1 mysql mysql  288 Mar 28 15:58 mariadb.log.2.gz
-rw------- 1 mysql mysql 2506 Mar 28 16:39 mariadb-slow.log.1
-rw-rw---- 1 mysql mysql  448 Mar 28 15:58 mariadb-slow.log.2.gz