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.
First stop the database server with killall mysqld or /etc/init.d/mysqld stop
Execute the command mysqld –skip-grant-tables –user=root to get the MySQL server running again minus the need for a password.
Use the command line MySQL client:
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
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)
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.