Resetting mysql root password without knowing the old one

This is an interesting one: I regularly forget the mysql password for my server. It is one of the most irritating feelings especially when I'm ready to use it and develop some server-side component of my project.

I came across this: https://help.ubuntu.com/community/MysqlPasswordReset

The link gives you a step-by-step to resetting your mysql database's root user password in case you forgot it and all you need is the root/admin account on the box to stop the mysql daemon and restart it in a "no-privilege-check" mode.

In light of this working for me, it dawned on me that when I do CTF competitions, we easily get access to the root/admin user on another team's server. Being able to change their password and dump (or destroy) their database might be a useful trick to know.

In other words, I'm just putting this here and one day it'll be put to use in a competition or for my personal use again -- let's face it: If at this stage, I keep forgetting the password, I'll keep forgetting it. Best to remember how to reset it.

In case the link dies:

How can I reset my MySQL password?

 Following this procedure, you will disable access control on the MySQL server. All connexions will have a root access. It is a good thing to unplug your server from the network or at least disable remote access.

To reset your mysqld password just follow these instructions :

• Stop the mysql demon process using this command :

  •    sudo /etc/init.d/mysql stop

• Start the mysqld demon process using the --skip-grant-tables option with this command 

  •    sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Because you are not checking user privs at this point, it's safest to disable networking. In Dapper, /usr/bin/mysqld... did not work. However, mysqld --skip-grant-tables did.

• start the mysql client process using this command 

  •    mysql -u root

• from the mysql prompt execute this command to be able to change any password

  •    FLUSH PRIVILEGES;

• Then reset/update your password 

  •    SET PASSWORD FOR root@'localhost' = PASSWORD('password');

• If you have a mysql root account that can connect from everywhere, you should also do:

  •    UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

• Alternate Method:

  •    USE mysql
       UPDATE user SET Password = PASSWORD('newpwd')
       WHERE Host = 'localhost' AND User = 'root';

• And if you have a root account that can access from everywhere:

  •    USE mysql
       UPDATE user SET Password = PASSWORD('newpwd')
       WHERE Host = '%' AND User = 'root';

For either method, once have received a message indicating a successful query (one or more rows affected), flush privileges:

FLUSH PRIVILEGES;

Then stop the mysqld process and relaunch it with the classical way:

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start