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
After that, run the commands below to skip the permission table.
sudo mysqld --skip-grant-tables &
Next, login to the MySQL database as root.
mysql -u root mysql
Finally, run the commands below to reset the root password
UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
Then flush the database permission table.
Start the service again and login.
sudo service mysql start