Atlantic.Net Blog

How to Reset a MySQL Root Password

Hitesh Jethva
by Atlantic.Net (178posts) under Dedicated Server Hosting
0 Comments

MySQL is a free, open-source, and widely used database management system. It is very popular for web application software. MySQL has a root password that allows a user to perform all database-related tasks.

If you are a system administrator responsible for managing a MySQL server, then you may often need to set or reset the MySQL root password – for example, if you forgot the MySQL root password or want to change the password for security reasons.

In this tutorial, we will show how to set, change, or reset the MySQL root password in Ubuntu 18.04.

Prerequisites

  • A fresh Ubuntu 18.04 VPS with MySQL installed on the Atlantic.Net Cloud Platform
  • A root password configured on your server

Step 1 – Create Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server.  Create a new server, choosing Ubuntu 18.04 as the operating system with at least 1GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged in to your Ubuntu 18.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

apt-get install mysql-server -y

Step 2 – Set the MySQL Password for the First Time

When you install the MySQL server for the first time, a MySQL root password is not set. You can connect to the MySQL shell without providing a password, but this is not recommended for security reasons.

To set the MySQL root password, connect to the MySQL shell with the following command:

mysql

Once connected, change the database to mysql using the following command:

use mysql;

Next, set the MySQL root password with the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secure-
password';

Next, flush the privileges and exit from the MySQL shell with the following command:

FLUSH PRIVILEGES;
EXIT;

Next, verify the MySQL by logging in with the root user:

mysql -u root -p

You will be prompted to enter the newly configured password to connect to the MySQL shell.

Step 3 – Change the MySQL Root Password

The easiest way to change the MySQL root password is with mysql_secure_installation script.

mysql_secure_installation

You will be asked to provide your current MySQL root password as shown below:

Securing the MySQL server deployment.
Enter password for user root:

Provide your MySQL root password and hit Enter. You should see the following output:

Select “No”

You will then be prompted to change the root password. Select YES.

 

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

Type Y and hit Enter to proceed. You will be asked to provide new password as shown below:

New password:
Re-enter new password:

Provide your new password, hit Enter, and complete the remaining steps as shown below:

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

You can also change the MySQL root password with the following command:

mysqladmin -u root -p password your-new-password

You will be asked to provide your current root password to change the password.

Step 4 – Recover Forgotten MySQL Root Password

If you forgot the MySQL root password, then you will need to recover it.

To recover the MySQL root password, first stop the MySQL service using the following command:

systemctl stop mysql

Next, create a mysqld directory and give proper permissions with the following command:

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

Next, start the MySQL service with the –skip-grant-tables:

mysqld_safe --skip-grant-tables &

This will allow you to connect the MySQL server without a password.

Next, log in to MySQL with the root user as shown below:

mysql -u root

After login, change the database to mysql as shown below:

use mysql;

Next, reset your root password with the following command:

UPDATE mysql.user SET authentication_string = PASSWORD('new-password') WHERE User = 
'root' AND Host = 'localhost';

Next, reload the privileges and exit from the MySQL shell with the following command:

FLUSH PRIVILEGES;
EXIT;

Next, stop the MySQL service with the following command:

mysqladmin -u root -p shutdown

You will be asked to provide your new password to stop the MySQL service.

Next, start the MySQL service again with the following command:

systemctl start mysql

Next, log in to MySQL with your new password as shown below:

mysql -u root -pnew-password

Once logged in, you should see the following output:

Conclusion

In the above guide, you learned how to set, update, and recover the MySQL root password on an Ubuntu 18.04 server. This guide should work with Ubuntu 18.04, Debian 10 and CentOS 8. If you are ready to try resetting your MySQL password, get started on dedicated server hosting from Atlantic.Net.

Get A Free To Use Cloud VPS

Free Tier Includes:
G3.2GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G3.2GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources