MySQL: “Access denied for user ‘debian-sys-maint’@’localhost'”
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.
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
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
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
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
excelent
abraham
August 23, 2010 at 5:58 pm
I ran to a similar problem and the solution simply works perfect.
Cheers!
tabunon
October 6, 2010 at 10:10 am
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
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
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
[…] mirzmaster.wordpress.com […]
linux cheats | dp
January 15, 2012 at 2:40 pm
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
[…] 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 […]
Fail to stop mysql (Unable to lock ./ibdata1, error: 11) video
January 8, 2013 at 10:35 pm
Nice one, thanks. Andy (UK)
Andy
January 15, 2013 at 6:40 am
Thank a lot 🙂
walid karray
February 26, 2013 at 4:56 am
[…] the fix and this is the reasoning behind it, if you’re […]
Debian – Can’t Stop MySQL; Permissions? | Click & Find Answer !
April 21, 2013 at 5:43 am
[…] to the local IP address in order to allow connections remotely. I got this from another website. (https://mirzmaster.wordpress.com/2009/01/16/mysql-access-denied-for-user-debian-sys-maintlocalhost/) but I figured I’d share it none the […]
Debian mysql error – MySQL: “Access denied for user ‘debian-sys-maint’@’localhost’” | The Sociopathic Mind of a Certified Eclectic
June 17, 2013 at 11:47 am
Thanks! This helped when I hosed the mysql user table with misconfigured replication 😉
npn
August 15, 2013 at 11:12 am
[…] with 20 comments […]
Access denied for user 'debian-sys-maint'@'localhost'
June 7, 2014 at 3:00 am
[…] 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 […]
Installation of Koha 3.x on Debian Squeeze | FoxTech Lab
July 3, 2014 at 4:38 am
[…] https://mirzmaster.wordpress.com/2009/01/16/mysql-access-denied-for-user-debian-sys-maintlocalhost/ […]
MySQL: “Access denied for user ‘debian-sys-maint’@’localhost’” ← Bookmarks
July 5, 2014 at 5:35 pm
Reblogged this on Wilson小站 and commented:
我也遇到这个问题
Wilson.L
October 11, 2014 at 2:16 pm
[…] “Access denied for user ‘debian-sys-maint’@’localhost’”가 발생하면 링크를 참고하여 […]
MariaDB Galera Cluster 설치 | codeSanctum
April 4, 2015 at 3:43 am
[…] MySQL: “Access denied for user ‘debian-sys-maint’@’localhost’”-RU-FI-OOOOOH! […]
修復錯誤 MySQL: “Access denied for user ‘debian-sys-maint’@’localhost’” - Jerry Wang's blog
May 24, 2015 at 3:12 am