RU-FI-OOOOOH!

Sohail Mirza, standing in for Peter.

MySQL: “Access denied for user ‘debian-sys-maint’@’localhost'”

with 24 comments

For all you Ubuntu/MySQL developers out there, have you ever seen the following?

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

So, what is this “debian-sys-maint” user?  Well, this MySQL user is created for the Ubuntu to be able to start/stop the database and to carry out other maintenance operations.

Sounds well enough, but then why do I keep running into the “access denied” problem for this user?  Well, the issue is that with each update to MySQL, the user’s password in the database is overwritten.  Ubuntu seems to go to the file /etc/mysql/debian.cnf in order to find this user’s password, but obviously the password is out of sync after the update has been applied.

As a result of this behaviour, I’ll run into the “access denied” problem every so often.  Thankfully, the solution to this issue is fairly simple.

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 <password>

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.

About these ads

Written by Sohail Mirza

January 16, 2009 at 6:51 pm

Posted in Development, Ubuntu

Tagged with , , ,

24 Responses

Subscribe to comments with RSS.

  1. Right tf on!

    Oogliby

    February 11, 2009 at 8:44 am

    • I see this is an older post, but just ran into this, myself. Reseting the debian-sys-maint password in mysql to match the debian.cnf file takes care of it – but do you know if there is a bug filed against this?

      No other mysql passwords are reset during updates – so this seems odd to me.

      Jere

      October 16, 2009 at 11:47 am

      • In my research of the solution, I can’t say I did come across a bug filed for this issue.

        Sohail Mirza

        October 16, 2009 at 11:57 am

    • thanks, it’s very usefull

      mas.aby

      January 31, 2012 at 10:35 pm

  2. Hi

    Perfect solution, now we can use the Mysql again.

    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′;

    Rene Madsen

    December 29, 2009 at 8:36 pm

  3. This did not make the error go away. It did, however, add debian-sys-maint user to my the Privileges list.

    PalaDolphin

    March 13, 2010 at 11:05 am

  4. You do NOT need to grant all permissions, this is a bad habit to get into from a security standpoint.

    All you need to grant is this (as root or whoever):

    GRANT SHUTDOWN ON *.* TO ‘debian-sys-maint’@’localhost';
    GRANT SELECT ON `mysql`.`user` TO ‘debian-sys-maint’@’localhost';

    Because it needs to shutdown/startup, and does a test select from the users table as a sanity check to ensure the root user exists. This select is usually done by /usr/share/mysql/debian-start.inc.sh which is loaded by /etc/mysql/debian-start

    RedScourge

    March 22, 2010 at 12:52 pm

  5. excelent

    abraham

    August 23, 2010 at 5:58 pm

  6. I ran to a similar problem and the solution simply works perfect.

    Cheers!

    tabunon

    October 6, 2010 at 10:10 am

  7. Remember, that if you running MySQL as slave, and you replicate mysql DB, you should use debian-sys-maint user’s password from master server. Spend half a day to understand that :)

    Ether

    October 13, 2011 at 11:24 am

  8. Just finished reinstalling my system and copied my old mysql files from the old disk when i encountered this problem.

    Thanks for the great simple solution for the problem

    Edan

    October 31, 2011 at 3:22 am

  9. Here’s the bash script I ended up with.
    It revokes extraneous privs for debian-sys-maint user, grants only SHUTDOWN and SELECT for mysql.*, then updates the /etc/mysql/debian.cnf script with the same password so that start/stop/restart work correctly from /etc/init.d/mysql.

    DEBMAINTPASSWORD=’YOUR_PASSWORD_GOES_HERE’

    # tighten restrictions on debian-sys-maint user. Deny all privs.
    mysql -e “REVOKE ALL PRIVILEGES ON *.* FROM ‘debian-sys-maint’@’localhost';”
    # grant only SHUTDOWN (and SELECT on mysql.user because it does a SELECT sanity check on startup) and fix password by copying from /etc/mysql/debian.cnf
    mysql -e “GRANT SHUTDOWN ON *.* TO ‘debian-sys-maint’@’localhost’ identified by ‘$DEBMAINTPASSWORD';”
    mysql -e “GRANT SELECT ON mysql.* TO ‘debian-sys-maint’@’localhost';”

    # update debian mysql conf file (which stores the maintenance password used by the debian start/stop maintenance scripts) with new password
    tmpfile=/etc/mysql/debian.cnf.new
    cat /etc/mysql/debian.cnf |sed “s/\(password\ \=\ \)\(.*\)/\1$DEBMAINTPASSWORD/g”>$tmpfile
    if [ -s $tmpfile ];then
    cat $tmpfile>/etc/mysql/debian.cnf
    rm -f /$tmpfile
    fi

    Eric

    November 18, 2011 at 5:07 pm

  10. […] mirzmaster.wordpress.com […]

    linux cheats | dp

    January 15, 2012 at 2:40 pm

  11. It seems the debian-sys-maint user does need more privileges then shutdown and select, otherwise it cannot upgrade the built-in mysql table when needed. The Ubuntu package adds every privilege, even GRANT:
    show grants for ‘debian-sys-maint’@’localhost';
    Result:
    GRANT ALL PRIVILEGES ON *.* TO ‘debian-sys-maint’@’localhost’ IDENTIFIED BY PASSWORD ‘xxx’ WITH GRANT OPTION

    hontvari

    March 27, 2012 at 9:14 am

  12. […] found a solution to this problem on this page, but I have to log in as a root user to do so, that I can’t do. I guess the two problems are […]

  13. Nice one, thanks. Andy (UK)

    Andy

    January 15, 2013 at 6:40 am

  14. Thank a lot :-)

    walid karray

    February 26, 2013 at 4:56 am

  15. […] the fix and this is the reasoning behind it, if you’re […]

  16. […] 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 […]

  17. Thanks! This helped when I hosed the mysql user table with misconfigured replication ;-)

    npn

    August 15, 2013 at 11:12 am

  18. […] with 20 comments […]

  19. […] a freshly updated debian – Lenny to Squeeze – might sometimes have trouble with the debian-sys-maint mysql user. You can fix it by following the instructions […]

  20. Reblogged this on Wilson小站 and commented:
    我也遇到这个问题

    Wilson.L

    October 11, 2014 at 2:16 pm


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: