MySQL logrotate error

Question

I'm new to Linux and have setup a VPS running Ubuntu 16.04 with postfix, dovecot and rouncube using mariadb for the database. It all seem to be working okay but I regularly receive the following email:

/etc/cron.daily/logrotate:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
error: error running shared postrotate script for '/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/mariadb-slow.log /var/log/mysql/error.log '
run-parts: /etc/cron.daily/logrotate exited with return code 1

I've done some searching and found a post suggesting I need to create a /root/.my.cnf file which I have done and have since restarted the server etc. but I am still receivng the error. The contents of my /root/.my.cnf are as follows:

[mysqladmin]
password = *mypassword*
user = root

[mysql]
password = *mypassword*

Does anyone know if there is something I'm missing or anything else I need to configure to get this to use the mysql password perhaps?

Thanks

Answer 1

This might be a bug in the 'stock' configuration when using mariadb as opposed to mysql that hasn't been stamped out yet, as I vaguely remember running into something similar myself when moving over to mariadb from mysql.

You will want to look in /etc/logrotate.d/mysql-server to see what is running at postrotate. You will likely have something like this:

test -x /usr/bin/mysqladmin || exit 0
if [ -f `my_print_defaults --mysqld | grep -oP "pid-file=\K[^$]+"` ]; then
    # If this fails, check debian.conf!
    mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
fi

Ubuntu is probably looking there, so try taking a look in /etc/mysql/debian.cnf and seeing what is set.

Answer 2

AvatarKava gets us pointed in the right direction and his answer will work for most people. If you're still scratching your head after adding a user and password to the /etc/mysql/debian.cnf file (even though it says not to touch this file haha!) then:

make sure you have quotes around your password

especially if there are special characters in your password.

Another hack is to remove the --defaults-file=/etc/mysql/debian.cnf option from the mysqladmin command in /etc/logrotate.d/mysql-server. If you remove this option, the root user (the user that runs logrotate) will still need a valid user and password to connect to mysql so make sure you have a proper .my.cnf file in /root. In either case, the changes to debian.cnf or mysql-server could be overwritten during the next MariaDB upgrade but as AvatarKava points out, you will probably get prompted before the upgrade overwrites these files. Worst case, you'll start getting logrotate errors again after the upgrade and will need to make the same changes again.

Answer 3

In my case, recreate the debian-sys-maint user was the solution.

Here is how to do that :

1. Use the SQL tab in your phpmyadmin or, on your linux console, log in mysql with :

   mysql -u root -p

enter your root password and continue

2. run the following command:

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

you can find 'your-passowrd' in /etc/mysql/debian.cnf

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

---

If you have the following message :

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

you will need to change your password or you can run the following commands to temporarily deactivate the password policy:

1. uninstall plugin validate_password;

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

3. INSTALL PLUGIN validate_password SONAME 'validate_password.so';