Fixing mysql warning message

Published on May 3, 2009 · 1 min

After restoring databases from one server to another I sometimes get this error on Ubuntu or Debian:

error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

This makes a lot of sense, and the solution is pretty simple. If you look in:

cat /etc/mysql/debian.cnf

You’ll see the defaults for your system. Copy the password listed there, and open a connection to MySQL as root (or some other user). Next, enter this (lets say your password specified in debian.cnf was ‘abracadabra’:

mysql> select PASSWORD('abracadabra');
+-------------------------------------------+
| PASSWORD('abracadabra')                   |
+-------------------------------------------+
| *38794E19D534EBA4F0F78903FA00F1DA2989DCA2 | 
+-------------------------------------------+
1 row in set (0.00 sec)

Next, since we already have the prompt open, do this command:

mysql> USE mysql;
mysql> UPDATE user SET password='*38794E19D534EBA4F0F78903FA00F1DA2989DCA2' where user='debian-sys-maint';
mysql> FLUSH privileges;

Restart MySQL, and the error should have gone away.