2012-01-23 Logrotate error for MySQL on Debian 6 Squeeze

When installing a new machine with Debian 6 (Squeeze), I also imported a database from a CentOS 5 box. The next day, the following error appeared in my inbox:

 /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 '
 run-parts: /etc/cron.daily/logrotate exited with return code 1

Turns out that Debian needs a user called 'debian-sys-maint' and that this user needs a number of rights on the database. The username and password are configured in /etc/defaults/debian.cnf so write these down, then create the user with the following commands:

 $ mysql -u root -p
 mysql> GRANT SHUTDOWN ON *.* TO 'debian-sys-maint'@'localhost';
 mysql> GRANT RELOAD ON *.* TO 'debian-sys-maint'@'localhost';
 mysql> GRANT SELECT ON 'mysql'.'user' TO 'debian-sys-maint'@'localhost';

and then set the correct password for the debian user:

 mysql> SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('secret');

Test whether logrotate won't give any problems:

 $ sudo logrotate -f /etc/logrotate.d/mysql-server