Sunday, October 24, 2010

Mysql issue: Access denied for user ‘root’@'localhost’

You may receive the “Access denied for user ‘root’@'localhost’” message while accessing mysql from the command prompt. The error message states that the Mysql password for user ‘root’ is incorrect and need to reset the password using skip-grant-tables option.

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

How to reset a Mysql password for ‘root’?

# /etc/init.d/mysql stop

Make sure all the mysql processes are stopped by executing the killall command

# killall -9 mysqld


Next, connect to mysql server using the skip-grant-tables method.

# /usr/bin/mysqld_safe –skip-grant-tables &

now, execute ‘mysql’ and you will be at the mysql prompt

# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23056
Server version: xx.xx-community MySQL Community Edition (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Goto the ‘mysql’ database and update the password for user ‘root’ in the “user” table.

mysql> use mysql;

To set a password, execute

mysql> update user set password=PASSWORD(“passhere”) where user=’root’;

OR to set a blank password, execute the mysql ‘update user’ query

mysql> update user set password=PASSWORD(“”) where user=’root’;

Once done, reload privileges and quit

mysql> flush privileges;
mysql> quit

Now, restart the mysql service

# /etc/init.d/mysql restart

and you should be able to connect mysql server:

# mysql
OR
# mysql -uroot -p

0 comments:

  © Blogger templates Newspaper III by Ourblogtemplates.com 2008

Back to TOP