HOWTO recover admin password for MYSQL

HOWTO Reconver admin's password for MYSQL Server - Ubuntu 8.04


1.- Admin's password is lost

wmunguiam@server:/var/www$ mysqlshow -uroot -p
Enter password:
mysqlshow: Access denied for user 'root'@'localhost' (using password: YES)

2.- Stop Mysql Service

wmunguiam@server:/var/www$ su
Contraseña:
root@server:/var/www# /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [ OK ]
root@server:/var/www#

3.- Start Mysql server but without reading Grant Tables
INPUT:

root@server:/var/www# /usr/bin/mysqld_safe --skip-grant-tables &

OUTPUT:

[1] 7764
root@server:/var/www# nohup: se ignora la entrada y se redirige stderr hacia stdout
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[7803]: started

4.- Login to Myqsl console
INPUT:

root@server:/var/www# mysql -u root

OUTPUT:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

5.- Use DB Mysql
INPUT:

mysql> use mysql

OUTPUT:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

6.- Change Password
INPUT:

mysql> UPDATE mysql.user SET Password=PASSWORD('NewPassHere') WHERE User='root';

OUTPUT:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

7.- Quit Mysql console & Restart Mysql service

mysql> quit
Bye
root@server:/var/www# /etc/init.d/mysql restart

* Stopping MySQL database server mysqld STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[9089]: ended
[ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
[1]+ Done /usr/bin/mysqld_safe --skip-grant-tables
root@server:/var/www#

8.- Test updated Admin's password

wmunguiam@server:/var/www$ mysqlshow -uroot -pNewPassHere

root@server:/home/wmunguiam# mysqlshow -uroot -pNewPassHere
+-------------------------+
| Databases |
+-------------------------+
| information_schema |
| gts |
| mysql |
+-------------------------+