Lost MySQL Root Password

It happens everynow and again, you login to a server to do something with MySQL only to find the root password is nowhere to to be found.

Never fear its not too hard to reset and you should be back up running again in a couple of minutes.

Step 1

First stop the database server with killall mysqld or /etc/init.d/mysqld stop

Step 2

Execute the command mysqld –skip-grant-tables –user=root to get the MySQL server running again minus the need for a password.

Step 3

Use the command line MySQL client:

root@server:/home/user# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.0.51a-3ubuntu5 (Ubuntu)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> use mysql;
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
mysql> UPDATE user SET password=password(“NEWPASSWORD“)  WHERE user=”root”;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;

Then kill the mysql processes again with killall mysqld then restart with /etc/init.d/mysql start, or whatever command you use to start MySQL on your distro of choice.

Upon restart, your done you should be able to login using your new MySQL root password.