Debian mysql error – MySQL: “Access denied for user ‘debian-sys-maint’@’localhost’”

Having not used debian in quite a few years, I was naturally tasked with setting up mysql on Debian Squeeze in a VM to replace our aging DB server. MySQL is pretty straight forward for the most part, but I ran into a debian-specific thing that can be an issue. The error when starting mysql: MySQL: “Access denied for user ‘debian-sys-maint’@’localhost'”
This was after I changed the bind-address in /etc/mysql/my.cnf from 127.0.0.1 to the local IP address in order to allow connections remotely.
I got this from another website. (http://mirzmaster.wordpress.com/2009/01/16/mysql-access-denied-for-user-debian-sys-maintlocalhost/) but I figured I’d share it none the less.

First, list the contents of the /etc/mysql/debian.cnf file:

neo@thematrix:~$ sudo cat /etc/mysql/debian.cnf

The contents of the file should look something like the following:

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

See that password?  That’s what we’re looking for!

Next, we want to issue a command to MySQL that tells it to grant the debian-sys-maint user all necessary privileges using the new password.

Login to your mysql server using your root account and the root password you had originally set:

neo@thematrix:~$ mysql -u root -p
Issue the GRANT command now to grant those permissions:

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘debian-sys-maint’@’localhost’ IDENTIFIED BY ‘n4aSHUP04s1J32X5’;
Voila! If you restart MySQL, you’ll find that you should no longer be getting the “access denied” error message.

neo@thematrix:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
Bear in mind, because we just switched the password, and the change hasn’t been affected yet, you may need to kill the MySQL server processes in order to get MySQL to shut down at all.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s