Need help?

Our experts have had an average response time of 13.14 minutes in February 2024 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

DigitalOcean – Reset MySQL root password in 4 simple steps

by | Dec 26, 2018

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

4 Comments

  1. Pete

    doesnt work after stopping mysql – get ‘cant connect to mysql through local server’

    looks like you missed some crucial info in your description

    Reply
    • Sijin George

      Hi Pete,
      We hope you have started MySQL using the mysqld_safe. If you still need help, we’ll be happy to talk to you on chat (click on the icon at right-bottom).

      Reply
  2. geoff

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

    $ 2020-02-26T20:43:04.755062Z mysqld_safe Logging to syslog.
    2020-02-26T20:43:04.760768Z mysqld_safe Logging to ‘/var/log/mysql/error.log’.
    2020-02-26T20:43:04.765967Z mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists.

    In new terminal window:

    $ mysql -u root
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

    Reply
    • Sijin George

      Hello Geoff,
      It looks like a problem with the MySQL socket. Our Engineers can solve it quickly. We’ll be happy to talk to you on chat (click on the icon at right-bottom).

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Categories

Tags

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF