Fix logrotate error regarding mysql on Ubuntu

mysql_debian_user.md

Fix logrotate error regarding mysql on Ubuntu

Sometimes if you are mailed of cronjobs, will may got an error like this:

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

Well, don't panic! Also don't ignore it! :)

This error message means your server is not logrotating mysql due to an error and if you're under Debian/Ubuntu chances are there should be an user named "debian-sys-maint" (which is auto-created during mysql installation) with the password stored on /etc/mysql/debian.cnf (only root can read it) which is not wokrking!

Maybe you have moved all your databases from another server and the first password for "debian-sys-maint" doesn't match the one stored on the file.

Luckly we have this one-liner:

First Test Debian User Access on MySQL

mysql -u debian-sys-maint -p"$(cat /etc/mysql/debian.cnf | grep password | head -n 1 |
awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }')"

If the access is working, you may be running through another error and you probably don't need to execute this fix. But if the access is not working, the following command will do it for you:

Fix Debian User Access Password on MySQL

This command will prompt you for root password, just type in and press enter

For MySQL Version 8+

echo "ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY '`sudo cat /etc/mysql/debian.cnf | grep password | head -n 1 | awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }'`';" | mysql -u root -p

For MySQL Version 5.7 and older

echo "SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('`sudo cat /etc/mysql/debian.cnf | grep password | head -n 1 | awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }'`');" | mysql -u root -p

If you would like to know the access is now really fixed you can re-run the Test command.

You may still get a different error, like this:

/etc/cron.daily/logrotate:
/usr/bin/mysqladmin: refresh failed; error: 'Unknown error'
error: error running shared postrotate script for '/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/error.log '
run-parts: /etc/cron.daily/logrotate exited with return code 1

In this case you might need to check the ownership of log folder/files

ls -la /var/log/mysql

If you got owner:group different than mysql:mysql for the "." folder and/or the files within, you should fix it by running:

chown mysql:mysql /var/log/mysql -R

You can test if you'll get this error again by running:

mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-log

If you got no message after that, then you're all set.

Awesome!! I recently moved to a new server and must have overwritten the debian.cnf file with a copy from the older server; this fixed it!! Thank you!

Simple and concise - great! Thanks.

This doesn't work with MySQL 8+, but the following 2nd command to change the password will work:

echo "ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY '`sudo cat /etc/mysql/debian.cnf | grep password | head -n 1 | awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }'`';" | mysql -u root -p

This doesn't work with MySQL 8+, but the following 2nd command to change the password will work:

echo "ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY 'sudo cat /etc/mysql/debian.cnf | grep password | head -n 1 | awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }'';" | mysql -u root -p

Thanks for your contribution!
Just added your solution to the file!