Thursday 7 November 2013

MySQL: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

Sometimes even the easy things like master-slave resync may break your nerves....

I faced that error while I was trying to resynchronize my master-slave  servers by doing an export from master/import to slave procedure.

This error was due to the fact that slave was started after an automated installation of MySQL with Opscode Chef. Without taking a look to the Chef recipe, the slave server was re-created with nothing:
no users, no databases

 To cut the long story, when I tried login to it I faced this issue:
MySQL: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

Meaning, even if /etc/my.cnf was set without password:
# By default ask for a password

when I tried to connect I got:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


1. Stop MySQL service
/etc/init.d/mysqld stop

2. Start the service without the grant tables:
mysqld_safe --skip-grant-tables &

3. On the MySQL command prompt enter the following commands:

use mysql;
select * from user;

Now here is the interesting point:
if you see root user from the result set of previous select, you just have to update the root password:
update user set `Password`=PASSWORD('some_pass');

If you don't see root user  then issue the following insert command:

More on creating mysql root user you  can see on MySQL user accounts manual page.

Then flush the MySQL tables and leave:
flush tables;

4. Next step is to stop the service that runs without grants tables:
/etc/init.d/mysqld stop
Shutting down MySQL....131107 13:11:38 mysqld_safe mysqld from pid file /var/run/mysqld/ ended
                                             [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables

5. Restart MySQL normally:
/etc/init.d/mysqld start

6. Try to log with the password you previously set:
mysql -uroot -psome_pass
Welcome to the MySQL monitor. ....

Hope it helps!

No comments: