RSS Feed

How to solve the MySQL replication Access denied for user ‘debian-sys-maint’@’localhost’ issue

While testing a MySQL master -> slave replication I was unable to connect to the slave remotely, despite this working on the master. Originally I thought the issue was simple, I forgot to comment out the binding of MySQL to 127.0.0.1 in /etc /mysql/my.cnf

bind-address = 127.0.0.1

Hash/comment it out:

#bind-address = 127.0.0.1

And then restart mysql

sudo /etc /init.d/mysql restart

While I thought this would have been sufficient enough to solve the problem I noticed the following error upon restarting MySQL on the slave:

user@hostname:~$ sudo /etc /init.d/mysql restart
* Stopping MySQL database server mysqld
* Starting MySQL database server mysqld
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘debian-sys-maint’@'localhost’ (using password: YES)’

While it appeared that MySQL had restarted and reloaded the configuration successfully, despite the access problems, it hadn’t. Remotely connecting to the slave still didn’t work.

Looking at the error shows that the debian specific user is unable to connect to the slave. This user, debian-sys-maint is by default similar to root. This user is used by the majority of debian distros for running maintenance scripts. This happens to include the binding of mysql to an address which evidently has not occurred.

Each installation of Ubuntu (As well as other Debian distros) has a unique password for the debian-sys-maint user stored in /etc /mysql/debian.cnf.

Due to replicating the master server databases to the slave(s), including the mysql table, the debian-sys-maint user password in the mysql.user table will not match that in the configuration file on the slave(s).

To fix this issue simply take the password from /etc /mysql/debian.cnf file on the master server and update the passwords (There are two) on the slave servers.

Example /etc /mysql/debian.cnf file

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

You can ignore the “DO NOT TOUCH!” comment as without making this modification restarting mysql and maintenance scripts will not work correctly.

In the above example you would replace “password = i1TAMAL9lq6SLvWr” with the new password.

Restart mysql and you should no longer receive this error and remote connectivity should now work.

sudo /etc /init.d/mysql restart

I have modified the Simple MySQL replication with Ubuntu – Master to slave howto to resemble these updates.

Posted in Linux on the 21st October 2010

SPEAK YOUR MIND...

Your email address will not be published. Required fields are marked *

*