Monday, January 26, 2015

How to fix: ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

If you have seen the follow message when  trying to shut down MySQL or Percona XTRADB Server and you see the following message:

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

and the process did not shutdown, the problem is not a bug in MySQL or Percona, the problem is that the password stored on disk for that account doesn't match what is stored in the 'mysql.Users' table.

The following is all one line and should fix the problem temporarily.

 echo "update mysql.user set password = PASSWORD('`sudo cat /etc/mysql/debian.cnf | grep password | tail -1 | awk '{print $3}'`') where user = 'debian-sys-maint';flush privileges;" | mysql -uroot -p

The likely cause of this root cause problem is that one of two things:

1. You have moved the '/var/lib/mysql/' directory from one server to another server possibly with rsync/scp with downtime or with Percona's backup tools with out causing down time.

2. You have replication setup and any updates to the 'mysql.Users' table was propagated out to all the slaves.

If you think that the root cause is more like the first case, I would recommend the treatment at the beginning of this post. If your root cause is more like the second case, I would recommend simply synchronizing the 'debian.cnf' file across all the MySQL nodes.

Alternatively, I've seen some people trying to use a combination of settings to not replicate the updates to the 'mysql.Users' table and they seem to have varying success in that approach. Unless you have a need to have different users on each node, I would recommend against this as it does seem to be tricky especially if your using ROW based replication.

No comments: