48

I have had problem with mysql. I tried to execute this:

echo "show databases" | mysql -B -N

But I got:

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

But when I exec:

/etc/init.d/mysql restart

I got an 'OK'.

I did

GRANT ALL PRIVILEGES on *.* TO debian-sys-maint@localhost IDENTIFIED BY PASSWORD 'your password' WITH GRANT OPTION; FLUSH PRIVILEGES;

where password is from /etc/mysql/debian.cnf. But it didn't help. (of course I flushed priv and restarted mysql).

tshepang
  • 12,111
  • 21
  • 91
  • 136
Bartosz Kowalczyk
  • 1,479
  • 2
  • 18
  • 34

3 Answers3

126

That’s because Debian has a MySQL account debian-sys-maint used for switching on/off and checking status. The password for that user should be the same as stored in /etc/mysql/debian.cnf. The file looks like this:

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

If the password doesn't match (for example because you changed it manually) the init script won't work anymore. You should set the password according to the file. So

mysql -u root -p
# Then type MySQL root password
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>';
phunehehe
  • 8,618
  • 7
  • 49
  • 79
Omesh
  • 27,801
  • 6
  • 42
  • 51
  • I have fields [client] and [mysql_upgrade] in /etc/mysql/debian.cnf. Passwords are the same in the file and in database for user. – Bartosz Kowalczyk Jul 25 '12 at 08:44
  • 2
    Yes, all you need - update password, because this pass is unique for each installation. For example: you move data_dir from one server to another, so the pass doesn't match – Victor Perov Aug 20 '14 at 15:30
  • 1
    This does not work anymore with MySQL 8. I used ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED BY ''; – user1768761 May 18 '23 at 18:20
18

The problem is, your GRANT statement uses IDENTIFIED BY PASSWORD clause, and in this case mysql expect to get a hashed password, not a plaintext one.

Use IDENTIFIED BY 'your password' instead, if you wish to supply a plaintext password.

poncha
  • 7,726
  • 2
  • 34
  • 38
3

Most easy way to restoring the debian-sys-maint user, is to reconfigure package mysql-server-5.5. That if you know the password for the root user of MySQL, you can try to restore the user and its password in /etc/mysql/debian.cnf.

sudo dpkg-reconfigure mysql-server-5.5

NOTE: if you cannot stop mysql pid, just run sudo killall mysqld. This is needed for reconfiguring the mysql-server-5.5.

shgnInc
  • 2,054
  • 1
  • 23
  • 34