Labels

Java (8) MySQL (5) linux (4) INNODB_LOCKS (2) INNODB_TRX (2) exclusive (2) innoDB (2) mountrakis (2) processlist (2) transactions (2) x-locks (2) xlocks (2) ERROR 1045 (28000) (1) J2EE (1) Jboss (1) Jboss5.0 (1) Jconsole (1) Master (1) Slave (1) Synchronization (1) VPN (1) bash (1) binlogs (1) command line (1) cook-book (1) cookbook (1) date (1) for (1) handy commands (1) loop (1) michael mountrakis (1) replication (1) sh (1) shell (1) unix epoch (1) unix timestamp (1) while (1)
Showing posts with label ERROR 1045 (28000). Show all posts
Showing posts with label ERROR 1045 (28000). Show all posts

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!