debian mysql error running shared postrotate script for /var/log/mysql.log

Solution 1:

The shared postrotate script tries to access the mysql database but fails to do so, probably because the password doesn't match.

In Debian, mysql is controlled via the mysql user 'debian-sys-maint'@'localhost'. The password for this user is stored in /etc/mysql/debian.cnf.

cat /etc/mysql/debian.cnf

Note the password being used in this file. If you have a password for root (like you should), you will need to get into mysql with the following command.

mysql -u root -p

Otherwise, you can just type 'mysql'. In the mysql> prompt, run the following.

GRANT RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'XXXXXXXXXXXX';

Substitute the password found in /etc/mysql/debian.cnf in place of the Xs. And finally...

quit

You should now be able to restart your mysql server with no errors using the command:

/etc/init.d/mysql restart

However, the restart is not required.

Solution 2:

On Ubuntu 18 my fix was this:

mysql -u root -p <<_EOF_
ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY '`sudo grep --only-matching  --max-count=1 --perl-regexp 'password\s*=\s*\K.*' /etc/mysql/debian.cnf | sed --expression='s/'\''/\\'\''/g'`';
_EOF_

This extracts the password from /etc/mysql/debian.cnf making sure to escape any single quotes it might contain:

    sudo grep                           \
        --only-matching                 \
        --max-count=1                   \
        --perl-regexp                   \
        'password\s*=\s*\K.*'           \
        /etc/mysql/debian.cnf           \
|   sed                                 \
      --expression='s/'\''/\\'\''/g'`

and inserts it in this bit of SQL:

ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY '<extracted-and-escaped-pw-inserted-here>';

... which is then fed directly to the standard input of the mysql -u root -p client.