Friday, February 29, 2008

Mysql gives "access denied" error for root@localhost

First, stop the mysqld,

/etc/rc.d/init.d/mysql stop

Then stop chkservd to keep it from interfering with mysqld while you work on it with

/etc/rc.d/init.d/chkservd stop

Start up mysqld , but, without the grant tables,

mysqld --skip-grant-tables -u mysql &

Then change the pass..

mysql

$mysql>use mysql;
$mysql>UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
$mysql>FLUSH PRIVILEGES;

Now restart mysql
=====================================================================================
----> Another Method

If at all the mysql password is not set. You can do so using the following commands on shell to set a password to the user root for mysql
*******************************************
killall mysqld
/usr/libexec/mysqld -Sg --user=root &
mysql
USE mysql
UPDATE user
SET password=password("your root password")
WHERE user="root";
flush privileges;
exit;
killall mysqld
/etc/init.d/mysqld start

*************************************

** The command work for only the user root..
=====================================================================================
Another method which will resolve the privileges error too.

$>service mysqld stop
$>safe_mysqld --skip-grant-tables &
$>mysql

mysql>FLUSH PRIVILEGES;

mysql>GRANT ALL PRIVILEGES ON *.* TO root@localhost INDENTIFIED BY 'password' WITH GRANT OPTION;

mysql>exit;

$>service mysqld restart


Done........
=====================================================================================

No comments: