Here’s a quick tutorial that will come in handy when you’re in a serious trouble. Forgetting the root password for MySQL server isn’t something that happens everyday, but when it does, it can be a real mess. This brief tutorial is going to show you how to easily reset the root password for MySQL server in Ubuntu 12.10. This can also be applied to other Linux systems, including Centos, Fedora and OpenSuse.

Here’s a scenario in which you’re going to need this. Recently I had to recover one of my sites which had failed because of system issues. Fortunately for me, I had a complete backup (content & database). All I had to do is install a fresh version of Ubuntu along with all packages and servers that were being used in my previous setup.

So, I installed all servers (apache, php, MySQL). After setting up MySQL server, I fat-fingered the password and didn’t remember it. I was screwed. I couldn’t restore the database without the root password. This is when this blog post comes in. You will have to reset the root password in order to login as root and restore the database.

To perform this, first stop MySQL server by running the commands below. You cannot have the service running while you change the root password.

sudo service mysql stop

 

ubuntu_mysql_root_password

 

After that, run the commands below to skip the permission table.

sudo mysqld --skip-grant-tables &

 

ubuntu_mysql_root_password_1

 

Next, login to the MySQL database as root.

mysql -u root mysql

 

ubuntu_mysql_root_password_2

 

Finally, run the commands below to reset the root password

UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';

 

ubuntu_mysql_root_password_3

 

Then flush the database permission table.

FLUSH PRIVILEGES; 

and quit.

 

Start the service again and login.

sudo service mysql start

 

Enjoy!