For whatever reason it may be, loosing the root password of your MySQL database can be a bugger. By default, MySQL now comes without a password for the root account.
I guess this is good for allowing you to set the password yourself, but sometimes users just don’t really know what they’re doing.
This then leads to complications if one is following a tutorial that just tells them step by step what to do, and then that tutorial doesn’t work, so they follow another tutorial, and that doesn’t work, and then they follow another one and each one requests that they change the root password to something thing that that author uses… You can see how it can get confusing for the newb. I know, I was once there.
But, unlike a Windows computer that has the default admin account disabled and you forget the only user account password to it, (which forces a user to re-image the computer…), with MySQL they allow you to change the root password from the back-end.
The task is relatively simple, and there are a few ways, but for me I will describe the simplest way for me. For a full description of all three solutions based on OS, head to MySQL’s dev documents.
Step one is to add the two lines to the file my.cnf, which will be located most likely inside of …/etc/mysql:
skip-grant-tables
skip-networking
The first line turns off the table lookup for access rights for each user, so this will allow any user all the rights on the server. Keep in mind, this is very un-secure and should be turned off as soon as this operation is finished to avoid any compromising architecture.
The second line turns off any networking, so while you’re making these changes, there are no outside connections to the server.
In order to save these changes, depending on your machine’s security, and depending on how you’re accessing this machine, you may need to alter the write permissions on the file my.cnf. For my case, this was as simple as the command through SSH:
sudo chmod o+w /etc/mysql/my.cnf
This effectively modifies the access rights of the file my.cnf to allow the ‘other’ user write access (o+w).
After saving this, remember to take away those access rights to the my.cnf file back to no write rights for the other user with the reverse command:
sudo chmod o-w /etc/mysql/my.cnf
After this is done, we can now alter the root password. This is accomplished first by entering into the shell in SSH:
shell > mysql
Followed by altering the root password:
mysql >
UPDATE mysql.user SET Password=PASSWORD('MyNewPass')->WHERE User='root';mysql>FLUSH PRIVILEGES;
Flushing the privileges ensures that MySQL reloads all their password tables to make sure the changes take effect. Also note that in ‘MyNewPass’, you change MyNewPass to your new root password, and it must have the additional apostrophe’s.
After accomplishing this statement, log out using:
exit
and you should see that you are now able to log into MySQL using the command:
mysql -uroot -p
which prompts you for the new password you just used.
After you have verified this, make sure to go back and remove (or comment out) the lines added to my.cnf at the start:
skip-grant-tables
skip-networking
and to remove the access rights to the file my.cnf.
Hope it works great!
For the full MySQL developers article on this topic, head to Resetting Root Password.