I forgot my MySQL password. Can you reset the MySQL password of my Vultr instance?
That was a recent support ticket received from one of our server owners as part of our Managed Cloud Services.
It’s common that users forget the MySQL root password. Unfortunately, there is no one click option to reset the MySQL password. We need to reset it from command line.
Today, let’s see how our Support Engineers reset MySQL password on a Vultr instance.
Reset MySQL password on Vultr – Where it’s needed?
Firstly, let’s see some common scenarios where users need to reset MySQL password.
1) Modify database driven websites
Database driven websites such as WordPress, Joomla, etc. store all their data in their databases. So, to manage these websites, server owners often need to edit/change multiple databases on their system. They usually modify the databases after login to the MySQL database as root user. This gives the user to modify any website databases on the server.
2) Securing database application
Similarly, as a part of securing the web applications, server owners often need to reset the MySQL root account password. For example, resetting a simple password to a complex one to improve security.
3) Install new applications
Installing new database management applications like phpMyAdmin require MySQL root password. And, if we don’t remember the password, we need to reset the root password of MySQL from command line.
Reset MySQL password on Vultr – Steps to do it
Now, let’s see how our Dedicated Engineers reset MySQL password on a Vultr Cloud Compute.
a) Check the Database software
Firstly, we check the database software and it’s version installed on the Vultr instance. For example, we use the following command to get the type of database software and it’s version.
mysql -V
Ubuntu servers generally support mariaDB database while Debian support MySQL database by default.
b)Stop MySQL service
To reset the MySQL password, our Support Experts first stop the MySQL service on the Vultr instance. For instance, we use the below command to stop the MySQL service on a CentOS system.
service mysql stop
In addition to that, we check the status of the MySQL service using the below command.
ps aux | grep mysql
In this way, we confirm that the Vultr instance doesn’t have any active MySQL running processes.
c) Grant password less access
After that, we enable password less access to the MySQL database using the below command.
mysqld_safe --skip-grant-tables --skip-networking &
Here, skip-grant-tables ensure that when MySQL is started, it doesn’t look for the grant-tables which contains the user privileges upon connection and query. In other words, anyone can login from anywhere and do anything on the database. But, this is a big security risk. That’s why our Support Engineers add –skip-networking command to only allow connections from the localhost.
d)Reset MySQL password
Further, our Support Engineers login to MySQL using the below command and reset the MySQL password.
mysql -u root
For instance, we reset the MySQL root password using the below steps.
UPDATE user SET password=PASSWORD('YOUR_NEW_PASSWORD') WHERE user='root';
FLUSH PRIVILEGES;
Replace YOUR_NEW_PASSWORD with the new password.
e) Restart MySQL service
Finally, we restart the MySQL service in order to return it to the normal mode. For example, we restart MySQL service from command line using the below command.
service mysql restart
Similarly, we restart MariaDB service using the below command.
service mariadb restart
You’re done!! You can now use the new root password to access MySQL.
But, sometimes server owners request password less login for MySQL root user, so that it avoids the burden of giving the MySQL root password all the time.
In such cases, our Dedicated Engineers create a file /root/.my.cnf and set the MySQL username and password in this file.
[client]
user=root
password=xxxxx
...
Here, replace xxxxx with the MySQL root password. Most importantly, we give suitable permissions for this file to make it readable and writable only by the root user.
chmod 0600 /root/.my.cnf[Trouble resetting MySQL root password on your Vultr VPS? Click here and get one of our Server Experts to get it fixed.]
Conclusion
In short, server owners can reset the MySQL root password on their Vultr instance using the above 5 simple steps. Today, we’ve discussed how our Support Engineers reset the MySQL root password on a Vultr instance.
