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 rotation 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 a dedicated log 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 and file name is configured with the general_log_file system variable.
• The slow query log location and file name 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=5

We will also need to create the relevant directory:

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

If you are using SELinux, then you may also need to set the SELinux context for the directory. See SELinux: Setting the File Context for Log Files for more information. For example:

sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /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 the logrotate utility to authenticate with MariaDB is to configure the root@localhost user account to use unix_socket authentication.

In MariaDB 10.3 and before, you need to install the unix_socket plugin before you can configure the root@localhost user account to use it. For example:

INSTALL SONAME 'auth_socket';

After the plugin is installed, the root@localhost user account can be configured to use unix_socket authentication. 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.

On many systems, the primary logrotate configuration file is located at the following path:

• /etc/logrotate.conf

And the logrotate configuration files for individual services are located in the following directory:

• /etc/logrotate.d/

We can create a logrotate configuration file for MariaDB with the following command:

sudo tee /etc/logrotate.d/mariadb <<EOF
/var/log/mysql/* {
        missingok
        create 660 mysql mysql
        notifempty
        daily
        minsize 1M # only use with logrotate >= 3.7.4
        maxsize 100M # only use with logrotate >= 3.8.1
        rotate 30
        # dateext # only use if your logrotate version is compatible with below dateformat
        # dateformat .%Y-%m-%d-%H-%M-%S # only use with logrotate >= 3.9.2
        compress
        delaycompress
        sharedscripts 
        olddir archive/
        createolddir 770 mysql mysql # only use with logrotate >= 3.8.9
    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

Each specific configuration directive does the following:

• missingok: This directive configures it to ignore missing files, rather than failing with an error.
• create 660 mysql mysql: This directive configures it to recreate the log files after log rotation with the specified permissions and owner.
• notifempty: This directive configures it to skip a log file during log rotation if it is empty.
• daily: This directive configures it to rotate each log file once per day.
• minsize 1M: This directive configures it to skip a log file during log rotation if it is smaller than 1 MB. This directive is only available with logrotate 3.7.4 and later.
• maxsize 100M: This directive configures it to rotate a log file more frequently than daily if it grows larger than 100 MB. This directive is only available with logrotate 3.8.1 and later.
• rotate 30: This directive configures it to keep 30 old copies of each log file.
• dateext: This directive configures it to use the date as an extension, rather than just a number. This directive is only available with logrotate 3.7.6 and later.
• dateformat .%Y-%m-%d-%H-%M-%S: This directive configures it to use this date format string (as defined by the format specification for strftime) for the date extension configured by the dateext directive. This directive is only available with logrotate 3.7.7 and later. Support for %H is only available with logrotate 3.9.0 and later. Support for %M and %S is only available with logrotate 3.9.2 and later.
• compress: This directive configures it to compress the log files with gzip.
• delaycompress: This directive configures it to delay compression of each log file until the next log rotation. If the log file is compressed at the same time that it is rotated, then there may be cases where a log file is being compressed while the MariaDB server is still writing to the log file. Delaying compression of a log file until the next log rotation can prevent race conditions such as these that can happen between the compression operation and the MariaDB server's log flush operation.
• olddir archive/: This directive configures it to archive the rotated log files in /var/log/mysql/archive/.
• createolddir 770 mysql mysql: This directive configures it to create the directory specified by the olddir directive with the specified permissions and owner, if the directory does not already exist. This directive is only available with logrotate 3.8.9 and later.
• sharedscripts: This directive configures it to run the postrotate script just once, rather than once for each rotated log file.
• postrotate: This directive configures it to execute a script after log rotation. This particular script executes the mysqladmin utility, which executes the FLUSH statement, which tells the MariaDB server to flush its various log files. When MariaDB server flushes a log file, it closes its existing file handle and reopens a new one. This ensure that MariaDB server does not continue writing to a log file after it has been rotated. This is an important component of the log rotation process.

If our system does not have logrotate 3.8.9 or later, which is needed to support the createolddir directive, then we will also need to create the relevant directory specified by the olddir directive:

sudo mkdir /var/log/mysql/archive/
sudo chown mysql:mysql /var/log/mysql/archive/
sudo chmod 0770 /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

Keep in mind that under normal operation, the logrotate utility may skip a log file during log rotation if the utility does not believe that the log file needs to be rotated yet. For example:

• If you set the notifempty directive mentioned above, then it will be configured to skip a log file during log rotation if the log file is empty.
• If you set the daily directive mentioned above, then it will be configured to only rotate each log file once per day.
• If you set the minsize 1M directive mentioned above, then it will be configured to skip a log file during log rotation if the log file size is smaller than 1 MB.

However, when running tests with the --force option, the logrotate utility does not take these options into consideration.

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

$ sudo ls -l /var/log/mysql/archive/
total 48
-rw-rw---- 1 mysql mysql  440 Mar 31 15:31 mariadb.err.1
-rw-rw---- 1 mysql mysql  138 Mar 31 15:30 mariadb.err.2.gz
-rw-rw---- 1 mysql mysql  145 Mar 31 15:28 mariadb.err.3.gz
-rw-rw---- 1 mysql mysql 1007 Mar 31 15:27 mariadb.err.4.gz
-rw-rw---- 1 mysql mysql 1437 Mar 31 15:32 mariadb.log.1
-rw-rw---- 1 mysql mysql  429 Mar 31 15:31 mariadb.log.2.gz
-rw-rw---- 1 mysql mysql  439 Mar 31 15:28 mariadb.log.3.gz
-rw-rw---- 1 mysql mysql  370 Mar 31 15:27 mariadb.log.4.gz
-rw-rw---- 1 mysql mysql 3915 Mar 31 15:32 mariadb-slow.log.1
-rw-rw---- 1 mysql mysql  554 Mar 31 15:31 mariadb-slow.log.2.gz
-rw-rw---- 1 mysql mysql  569 Mar 31 15:28 mariadb-slow.log.3.gz
-rw-rw---- 1 mysql mysql  487 Mar 31 15:27 mariadb-slow.log.4.gz