MySQL/MariaDB logrotation howto

[dkzr]

Hi,

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

 

[factor]

Thanks you might add a copy to the howto section as well.

How-To Guides

How-to guides suitable for use with DirectAdmin.

forum.directadmin.com

 

[dkzr]

Cross-post https://forum.directadmin.com/threads/mysql-mariadb-logrotation.62997/

 

[factor]

/etc/mysql/conf.d

I dont have these Directories mysql/ or conf.d
Did you create them?
I have Debian 10 as well.

 

[dkzr]

I dont have these Directories mysql/ or conf.d
Did you create them?
I have Debian 10 as well.

Is it possible you have MySQL installed instead of MariaDB?

When I run `mysqld --verbose --help` I see the config files that are read by default. In my case:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

And in my /etc/mysql/my.cnf the files in conf.d are incluced.

Perhaps, for your installation, it's best to modify your /etc/my.cnf with the settings I put in /etc/mysql/conf.d/mysqld-log.cnf

 

[factor]

/etc/my.cnf

Yeah I only have this
Looks like my MariaDB is 10.5 and maybe source compiled. Might be the issue.
Thanks