Re: [cobalt-users] Best instructions for getting lost passwords for MySQL

[Dmitry Alexeyev <dmitry@xxxxxxxxxxxxxxxxx>]

On Monday 10 May 2004 18:52, Tim Walter wrote:
> Hi,
>
> Somehing appears to have corrupted my MySQL passowrds, and therefore
> getting access denied messages.
>
> Tried following instructions on RaqFAQ, and MySQL websites.  Can't
> get into MySQl.  Any suggestions as to best practice.
>
> Thanks

First, make sure you do backups before doing anything: 

#cd ~admin
# tar zcvf mysqldb.tar.gz /var/lib/mysql

and then as root

(stop mysql)

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

(run w/o checking user permissions) 

# safe_mysqld --skip-grant-tables

Open another ssh session, do

# mysql -Uroot

it will log you into mysql database without password

in mysql prompt, type

mysql> use mysql

Check user existance: 

mysql> select * from user where user='root';
+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| Host      | User | Password         | Select_priv | Insert_priv | 
Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | 
Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv 
| Index_priv | Alter_priv |
+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| localhost | root | 573****57d51ee1 | Y           | Y           | Y           
| Y           | Y           | Y         | Y           | Y             | 
Y            | Y         | Y          | Y               | Y          | 
Y          |
+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
1 row in set (0.07 sec)


Emptify root password: 

mysql> update user set password = '' where user='root';

(or set some password for known account - do 'select password from user 
where user='some_user_whose_password_you_know')

End mysql session pressing Ctrl+D

You have now cleared both password for localhost and your host for user 
root, so make sure to restore both.

Return mysql to normal operation state: 

# killall mysqld

(this will shut down mysql running without grant tables checking)

restart mysql

# /etc/rc.d/init.d/mysql restart

now set passwords for mysql: 
# mysqladmin -u root password "my new pw"
# mysqladmin -u root -h `hostname` password "my new pw"

Simple, isn't ? ;) 

WBR
Dmitry