MySQL/MariaDB logrotation

[dkzr]

Cross-post of https://forum.directadmin.com/threads/mysql-mariadb-logrotation-howto.62996

I found a huge <servername>.err file in /home/mysql/ which is the default location for the MySQL/MariaDB error log. This log is not "logrotated" so it just grows.

With the following you can enable logrotation of the database server file(s). I've also moved the logs to /var/logs. Based on https://mariadb.com/kb/en/rotating-logs-on-unix-and-linux/

I've only tested this on Debian 10.7 with MariaDB 10.4

1. Create the directory for the database logs:

sudo mkdir /var/log/mysql
sudo chmod 755 /var/log/mysql

2. Add database server settings. I created an extra cnf file for this:

sudo tee /etc/mysql/conf.d/mysqld-log.cnf <<EOF
[mysqld]
log_error=/var/log/mysql/mysqld.err

# Enable generic logging
#general_log
#general_log_file=/var/log/mysql/mysqld.log

# Enable Slow Query logging (query_time in seconds.miliseconds)
#slow_query_log
#slow_query_log_file=/var/log/mysql/mysqld-slow.log
#long_query_time=5
EOF

3. Restart the MySQL or MariaDB server, eg

sudo systemctl restart mysqld

4. Configure logrotate (double \\ because of tee command)

sudo tee /etc/logrotate.d/mysql <<EOF
/var/log/mysql/*.err
/var/log/mysql/*.log
{
    missingok
    create 660 mysql mysql
    notifempty
    weekly
    minsize 1M
    rotate 4
    compress
    delaycompress
    sharedscripts
    postrotate
        # just if mysqld is really running
        if test -x /usr/local/bin/mysqladmin && \\
           /usr/local/bin/mysqladmin \\
           --defaults-extra-file=/usr/local/directadmin/conf/my.cnf \\
           ping > /dev/null 2>&1
        then
           /usr/local/bin/mysqladmin \\
             --defaults-extra-file=/usr/local/directadmin/conf/my.cnf \\
             --local \\
             flush-error-log flush-engine-log flush-general-log flush-slow-log
        fi
    endscript
}
EOF

5. You can (should) test using

logrotate --force /etc/logrotate.d/mysql