Today, after copying the content of /var/lib/mysql (which is where my database is stored) from one virtual machine to another, I was unable to restart mysqld. The error was:
- dbserver1:/var/log# /etc/init.d/mysql restart
- Stopping MySQL database server: mysqld failed!
- Starting MySQL database server: mysqld already running.
- /usr/bin/mysqladmin: connect to server at 'localhost' failed
- error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
- dbserver1:/var/log#
I was quite surprised then I realized that the "access denied" error was pointing me to a problem with the password of the special MySQL user "debian-sys-maint". After a few investigations I finally got the root cause of the problem: during the installation MySQL generates a random password for "debian-sys-maint" and stores its credentials inside /etc/mysql/debian.cnf as well as inside the "users" table in the database itself. When I copied the database form one VM to another the two passwords (the one on the local debian.cnf and the one in the original database) were not matching anymore.
So, for the solution, find your "debian-sys-maint" password in /etc/mysql/debian.cnf:
- cat /etc/mysql/debian.cnf | grep password
Then connect to MySQL with root:
- mysql -u root -p<your_root_password>
Use now the following SQL command to recreate the user (but replace <password> with your debian-sys-maint password!):
- GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;
Thanks a lot!!! My SQL-server WORKS now!!!
ReplyDeleteThank you, just what I needed. I knew I had to adjust that password!
ReplyDeletethanks, that worked!
ReplyDeleteWow, was this ever helpful...
ReplyDeleteThanks a lot !!
ReplyDeleteAwesome! Thanks!
ReplyDeletethanks!. This worked for me on Debian Squeeze 6.0.3. No I no longer stare in misery at the red warning during boot up...
ReplyDeleteThanks !
ReplyDeleteFantastic, saved my bacon. Thanks so much - bookmarking this blog!
ReplyDeleteMany Thanks!!!
ReplyDeleteCheers! Very helpful.
ReplyDeleteworks perfect! thx
ReplyDeleteThank you !
ReplyDelete