Access restriction with passwords is a great way to secure the MySQL database server.

But, if you do not remember the password, no one can access it.

Forgetting MySQL password is a common scenario that happens in newly created servers or Droplets. And, all you can do is a reset of the password via command line.

At Bobcares, we help server owners to regain MySQL root access as part of our Managed Cloud Services.

Today, we’ll see how our Support Engineers reset the MySQL root password in DigitalOcean Droplets using 4 simple steps.

Where do you need MySQL root password?

Firstly, we’ll see the common scenarios where we need MySQL root password.

1. Installation of programs

In DigitalOcean droplets, when you install database management programs like PHPMyAdmin, it asks for MySQL root password. And, if you cannot remember the password, the installation cannot be completed.

2. Modify existing databases

Most web applications are database driven. These databases store various details like login, user preferences, sessions, etc.Therefore, server owners often need to edit and modify the databases in the Droplet. Having root MySQL password gives the privilege to edit any database on the server.

Steps to reset MySQL root password in DigitalOcean

When you do not remember the MySQL root password, the quick work around is to reset the password.

Now, let’s see the 4 steps that our Support Engineers do to reset the root MySQL password in the DigitalOcean Droplet.

1. Stop MySQL

To reset the root password, we need to first stop the MySQL service on the server. For this, our Support Engineers execute the following command:

service mysql stop

This would stop all MySQL processes running on the server. Additionally, we check once again using the command,

ps ax | grep mysql

This confirms that no mysqld process runs on the Droplet.

2. Start MySQL Without Grant Tables

In MySQL server, the user privileges are stored in the “grant tables“.

Now, we need to stop the database server from loading the grant tables. And, that helps us to connect to the MySQL server as root user without password.

For this we execute,

mysqld_safe —skip-grant-tables —skip-networking &

Here, the skip-networking option isolates the service from other networks.

3. Change MySQL root password

Now, we need to access Droplet via another ssh window. In the new window, our Engineers connect to MySQL using the command

mysql -u root

Here, it will not ask for root password as we have already skipped the privileges. After this, we change the root password using the exact steps below:

mysql> use mysql;
​mysql> update user set authentication_string=password('NEWPASSWORD') where user='root';
​mysql> flush privileges;
​mysql> quit

Here, we substitute ‘NEWPASSWORD’ to the strong MySQL password that we would like to use.

4. Restart MySQL

Now, we need to revert MySQL service back to normal mode. Making it run in the skip grant privileges mode affects all database users and can be a security risk. That’s why, our Support Engineers restart MySQL with the command:

service mysql restart

That’s it. We have now reset the MySQL root password.

Things to do after MySQL password reset

After completing the MySQL root password, it is worth to take steps to secure it further. Thus, it avoids the need for further password reset.

That’s why, our Support Engineers help customers to avoid the inconvenience of providing the password on the command line.

We set the MySQL root password in the file /root/.my.cnf using the following steps.

Create a file /root/.my.cnf

touch /root/.my.cnf

Then add the following content in it.

[client]
user = root
password = xxx

Replace ‘xxx’ with your root MySQL password.

[Trouble retrieving password of your server? We can reset the MySQL root password for you.]

Conclusion

DigitalOcean Droplet owners often need to reset MySQL root password in scenarios of forgotten password, server reinstall, etc. Today, we’ve seen how our Cloud Specialists quickly reset MySQL root password in 4 simple steps.