Fix logrotate mysql error on Ubuntu

Error

Logrotate produces an error email message when it's trying to rotate mysql log files.

/etc/cron.daily/logrotate:
error: error running shared postrotate script for '/var/log/mysql.log /var/log/mysql/*log '
run-parts: /etc/cron.daily/logrotate exited with return code 1

Cause

The creation of the database from scratch don't create the user debian-sys.maint. But logrotate uses it.

cat /etc/logrotate.d/mysql-server
# - I put everything in one block and added sharedscripts, so that mysql gets
#   flush-logs'd only once.
#   Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql.log /var/log/mysql/*log {
       daily
       rotate 7
       missingok
       create 640 mysql adm
       compress
       sharedscripts
       postrotate
               test -x /usr/bin/mysqladmin || exit 0
               # If this fails, check debian.conf!
               MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
               if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
                 # Really no mysqld or rather a missing debian-sys-maint user?
                 # If this occurs and is not a error please report a bug.
                 #if ps cax | grep -q mysqld; then
                 if killall -q -s0 -umysql mysqld; then
                   exit 1
                 fi
               else
                 $MYADMIN flush-logs
               fi
       endscript
}

Mysqladmin uses /etc/mysql/debian.cnf credentials file

cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = DEBIAN_PASSWORD
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = DEBIAN_PASSWORD
socket   = /var/run/mysqld/mysqld.sock

Fix

To avoid the issue, we need to allow the user debian-sys-maint the grant RELOAD to be able to flush logs.

mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
mysqladmin: connect to server at 'localhost' failed

The user doesn't have access, really the user doesn't exists.

error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
echo "GRANT RELOAD ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '`cat /etc/mysql/debian.cnf | grep password | sort -u |cut -d' ' -f 3`';" | mysql -p
Enter password:
mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
mysqld is alive

Once the user is created with the correct password and the grant RELOAD, logrotate should run correctly.

References

• https://www.whitebyte.info/general/how-to-fix-logrotate-cron-job-error-etccron-dailylogrotate

Daniel Simao 09:42, 7 June 2020 (EDT)