Recover Admin Access

So you lost your Admin password for your mysql server ?

What now ? This is how to regain access and reset the password.

Stop the server

sudo /etc/init.d/mysql stop

Then start the server by hand, with the option to ingnore the server security

sudo mysqld_safe --skip-grant-tables &

Then login to the mysql server without a password

mysql -u root

Then reset the password via sql

root@testtest:~# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

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

mysql> select user,host,password from  mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | testtest  | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
|                  | localhost |                                           | 
|                  | testtest  |                                           | 
| debian-sys-maint | localhost | *A55451DA225FD5D701EAB76B17AA900BF3ADCB28 | 
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> update mysql.user set password=PASSWORD('mysql') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> 
mysql> select user,host,password from  mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | testtest  | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
|                  | localhost |                                           | 
|                  | testtest  |                                           | 
| debian-sys-maint | localhost | *A55451DA225FD5D701EAB76B17AA900BF3ADCB28 | 
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> \q
Bye

Then stop the server

sudo /etc/init.d/mysql stop

Then you should be done, next time you restart your server the password has been reset to what you want.