It’s not recommended to use single password for all of your logins. If someone cracks your password for a site or software, the hacker will have access to all of your accounts. You can avoid that by using different passwords. But the problem is that you might forget the password if you’re not using it regularly. Same happened to me with my MySQL root password yesterday.
Fortunately, I remembered my root password for Ubuntu 12.04 server. So it wasn’t that hard to reset MySQL root password from command line. I’m going to show you how exactly I did it. But it’s important that you have server root password with you. If not, you can reset it easily from your VPS control panel.
Being logged into VPS as root or any other SUDO user, you’ll have to restart MySQL server in safe mode. Safe mode will skip user privileges table.
sudo service mysql stop
That’ll stop MySQL server.
sudo mysqld_safe --skip-grant-tables &
Now you’ll be able to login to MySQL without having asked for a password. Let’s login as root user,
mysql -u root
Now that you’re logged in, let’s reset root password. But you have to select the database first. MySQL is the database that holds password of any MySQL user. So let’s change the database in use,
use mysql;
Following will change MySQL root password to new_password, replace it in following command to set your own password.
update user set password=PASSWORD("new_password") where user='root';Finally flush privileges,
flush privileges;
Quit MySQL,
quit
Now restart MySQL,
sudo service mysql restart
Now you’ll be able to login to MySQL with your new password. In case you already don’t know, following is the command to login to MySQL as root user,
mysql -u root –p
Enter the password you just set when prompted, it’s new_password in my case.
