MySQL Fix/Recover Root Password

John the CEO of TextbookValet.com asked if I would be able to help them with some updates to the site. I was previously in the team and I left there to join GivingFire.com. The app runs on MySQL. I hadn’t installed MySQL in my new computer so I did…

#~ sudo apt-get update
#~ sudo apt-get install mysql-server libmysqlclient-dev

I didn’t give a password in the installation pages. I actually didn’t even read what those messages said. When it was done, I tried to access the database and it said I couldn’t login. I red that newer version of MySQL would use the user password. So I tried my password with no luck. Then I tried the root password with no luck. I tried ‘password’ and had no luck. Then I decided to reset the password and figured some things had changed. So I thought of writing a blog post on how to.

First lets access the shell as root. We need root privileges to run the following commands.

#~ sudo -s

Then we need to start stop mysql and start it skipping auth checking.

#~ /etc/init.d/mysql stop
#~ mysql_safe --skip-grant-table &

Then we need to use the mysql database. There is a users table in it and we will update the password hash there. Now most documentation will say you need to update the password attribute but in newer versions of MySQL that is not the case. You need to update the authentication_string attribute. Also just to be safe, lets set the authentication method to use mysql_native_password. Here is how…

use mysql;
update user set authentication_string=PASSWORD('password') where user='root';
update user set plugin="mysql_native_password" where user='root';

Now that we have updated the password, we need to flush privileges and restart the server.

flush privileges;
exit;

Then in the shell…

#~ /etc/init.d/mysql stop
#~ /etc/init.d/mysql start

Now you can login with your password. Note: I used ‘password’ above for illustration purposes. Use a password of your choosing, the more secure the better.