Fixing logrotate errors and other MySQL issues on Ubuntu / Debian

For our MySQL databases on EC2, we back up the data by taking hourly snapshots of the volume that stores the MySQL data folder. This is a common practice, and in fact we do it using the popular ec2-consistent-snapshot script by Alestic. For backups this works great; in addition, having volume snapshots of MySQL data is very useful when we want to quickly launch a new MySQL server with a fairly recent state of our data – whether as a way to speed up synchronization of a new DB node or for testing purposes.

When launching a new EC2 instance we simply mount a new volume created from the latest DB snapshot on /var/lib/mysql and then start MySQL, and everything “just works” from there.

However, there are some quirks to this method: recently, I’ve encountered some errors from daily logrotate tasks which fail to flush the logs on a post-rotate script (this is from the logs of cron that runs 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

A little bit of investigating showed that Debian (and hence Ubuntu) installations of MySQL create a special MySQL user called ‘debian-sys-maint’. This user is used for system maintenance tasks such as verifying the table integrity on startup and also running some log rotation tasks. It has close-to-root privileges (which I’m not sure is good, but what the hell), and a password of its own which is created at installation time and is stored in /etc/mysql/debian.cnf, a file readable only by root.

The trouble is, when migrating DB snapshots from a different server, the password stored in that config file no longer matches the one saved in the DB (as it was copied along with the data). Fixing this is easy: either fix the password in the file, or better yet, fix the password stored in the DB to match the random generated one (this way you have different passwords on each server).

Here is a one-liner I crafted to handle that:

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

You will be asked to type in your MySQL root user password. That’s it.

Oh and yes, this is the sort of post I make when I need to remember something – hopefully its useful for others but if not, at least I’ll be able to find it.

Also, I’m thinking there might be a much simpler way to do this, for example by re-running the Debian mysql-server package post-install configuration phase that probably handles the creation of debian-sys-maint in the first place, but I didn’t try it. If you try let me know!

Comments are closed.