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:
[client]
# By default ask for a password
#password

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

Treatment

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:
INSERT INTO `user` VALUES(
'localhost','root',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','');

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

Then flush the MySQL tables and leave:
flush tables;
quit;

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/mysqld.pid 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: