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.
FLUSH PRIVILEGES;and quit.
Start the service again and login.
sudo service mysql start
Enjoy!
Frequently Asked Questions
How to reset MySQL root password in Ubuntu 12.10?
To reset the MySQL root password in Ubuntu 12.10, you need to stop the MySQL server, skip the permission table, log in to the MySQL database as root, reset the root password, flush the database permission table, and start the service again.
Can I use the same method to reset MySQL root password in Centos or Fedora?
Yes, the method to reset the MySQL root password in Ubuntu 12.10 can also be applied to other Linux systems like Centos and Fedora.
What should I do if I forget the MySQL root password in Ubuntu?
If you forget the MySQL root password in Ubuntu, you can follow the steps to reset it by stopping the MySQL server, skipping the permission table, resetting the password, and restarting the service.
What is the command to stop MySQL server in Ubuntu?
To stop the MySQL server in Ubuntu, you can use the command 'sudo service mysql stop'.
How do I log in to the MySQL database as root?
To log in to the MySQL database as root, you can use the command 'mysql -u root mysql'.
What is the purpose of flushing the database permission table?
Flushing the database permission table in MySQL is important after resetting the root password to ensure that the changes take effect immediately.
Is it necessary to start the MySQL service after resetting the root password?
Yes, after resetting the root password, you need to start the MySQL service again using the command 'sudo service mysql start'.
Can this method be used to recover a forgotten MySQL root password on other Linux distributions?
Yes, the method described in the article can be used to recover a forgotten MySQL root password on other Linux distributions like OpenSuse as well.
