Chances are, you have at least a dozen passwords for your server, computers, phones, email, social network, and bank account. It is tough enough to remember commonly-used passwords without even thinking about your MySQL password. The general perception is that, if you lose your MySQL administrative password, you are up a creek without a paddle. In reality, there is a way to reset the password and give yourself a much-needed second chance.
Just follow these steps:
1. Log into your server via SSH and then become root:
2. Stop the current MySQL instance (note that this will halt any sites that depend on it, so make sure you do this during the lowest possible traffic time).
3. Start MySQL in safe mode and skip privileges checks:
# mysqld_safe –skip-grant-tables &
4. Next, log into MySQL without a privileges check:
# mysql –user=root mysql
5. Finally, reset the password by updating the user table of the default mysql database:
UPDATE user SET Password=PASSWORD(‘newrootpwd’) WHERE user=’root’;
(Note: replace “newrootpwd” with your actual password, preferably one you can remember)
6. Restart MySQL to get out of safe mode:
# /etc/init.d/mysql restart
After going through this process, you should be able to access MySQL with your new password. Even though it is a hassle to recover a forgotten password, do not use that as an excuse to make a weak password. You can use mnemonic devices or some other method to remember even a complex password. Be safe but also keep track of all your passwords.