Resetting a MySQL Root Password is one of the easiest things to do. However, finding documentation on how to do so can be challenging.
We as system administrators, have to reset passwords. Remembering how to do so, is a challenge all in itself. I decided to write a very simple and straight forward article on how to do so. I would assume it could be helpful for future users, as well as myself!
This post will show you how to reset the MySQL Root password on FreeBSD. Although similar to Linux, I can assure you this will work on FreeBSD.
Step #1 is to start MySQL without grant tables. Keep in mind, if this is a live environment, you will have to restart MySQL. Which means, it will drop any open connections to MySQL during this process for a short period of time.
Using your favorite visual text editor. Mine being PICO, do the following when logged into your server.
Add the following to /etc/rc.conf and then save the file.
Once completed, you will have to restart MySQL, so it skips password authentication. Next, do the following :
Once MySQL Restarts, you should be able to get into MySQL Root without an issue.
Run the following command to gain access to MySQL.
mysql -u root -p
If it prompts you for a password, just click enter.
Once in the mysql command like it should look like this
If so, you were able to skip the Grant Tables, now its time to reset the password.
To reset the password do the following :
UPDATE mysql.user SET Password=PASSWORD(‘your new password here’) WHERE USER=’root';
The command should execute without a problem, make sure you write down the password! Otherwise, you will have to reset the password once more.
The last step, is to go back in /etc/rc.conf/ and remove the following line.
Again, you have to open the file by doing the following :
Then Remove, the line you inserted in the beginning of this tutorial. If you don’t, MySQL will be wide open to the world!
Once again restart MySQL with the following command, and make sure you can get back into MySQL with the password you set!
Congratulations, you have now reset your MySQL Root Password on FreeBSD!