Saturday, October 31, 2020

How to Reset the MySQL Root Password

Must read

How to Setup a Firewall with UFW on Ubuntu 20.04

Nowadays, a Firewall is an essential utility and property of any system for security; by default Ubuntu Operating system having a firewall...

How to Install Ruby on Ubuntu 20.04 Linux

Nowadays, Ruby is the most popular language, especially for SaaS application development. It has a perfect and elegant syntax structure, and it...

How to Install Eclipse IDE on Ubuntu 20.04 Linux

Eclipse is the most famous and widely used Java integrated development environment (IDE). It supports many plugins to enhance the capabilities to...

The 30 most useful Linux commands

This article will introduce some practical and common Linux or Unix command , which is the Linux system administrators normally use the command . This article is not a...
SATISH KUMAR
I am Satish Kumar, Founder of LinuxConcept. Linux and F.O.S.S enthusiast, love to work on open source platform and technologies.
- Advertisement -

Do you want to reset the MySQL root password? Sometimes we forget the root password and not able to log in on MySQL, and we want to reset the password. It is happening with all of us most of the time.

In this article, we will explain the process to reset the MySQL root password from the command line utility.

Identify the Server version

- Advertisement -

To execute any command on MySQL or MariaDB, first, we should know which version of the database system is running on the system. The different commands to recover root password depends on the MySQL or MariaDB version.

You can find the version of your database system using the following command:

$ mysql --version

If you are using MySQL you will get the output similar to below:

Output:

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper
- Advertisement -

Or you will get the output like below if using MariaDB:

Output:

mysql  Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

How to reset MySQL or MariaDB root password

To reset the MySQL or MariaDB root password, you need to follow the below steps:

Step 1 – Stop the MySQL/MariaDB service

In the first step, you need to stop the MySQL server, by using the following command:

- Advertisement -
$ sudo systemctl stop mysql

Step 2 – Start MySQL/MariaDB server without loading the grant tables.

To start the database server without loading the grant tables using the following command:

$ sudo mysqld_safe --skip-grant-tables &

Using –skip-grant-tables option to ignore grant table loading and it allows anyone to access your database server without a password and with all privileges.

The ampersand & at the end of the command is use to execute or run the program in the background, which allows you to continue to use the shell.

Step 3 – Log in to MySQL/MariaDB shell.

Now you can connect to the database server as the root user without a password as shown below:

$ mysql -u root

Step 4 – Set a new root password.

To set the new password for MySQL 5.7.6 and later or MariaDB 10.1.20 and later, run the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
mysql> FLUSH PRIVILEGES;

If ALTER USER command doesn’t work for you, you can modify the user table directly by using below commands:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
 WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;

Run the following command if you are using MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
mysql> FLUSH PRIVILEGES;

In both of cases if command executes successfully, you will see the following output:

Output

Query OK, 0 rows affected (0.00 sec)

Step 5 – Stop and start MySQL/MariaDB server normally.

Now, the new root password is set, stop the database by running the following command:

$ mysqladmin -u root -p shutdown

Your screen will prompt to enter the new root password for confirmation to stop the service.

Start the database server in normal mode, as shown below:

For MySQL database server, run:

$ sudo systemctl start mysql

For MariaDB database server, run:

$ sudo systemctl start mariadb

Step 6 – Verify the newly created password

You can verify the newly created password is applied correctly try to log in on database server, as shown below:

$ mysql -u root -p

The output of the above command will prompt to enter the new root password. Put your password and hit enter, and you should log in to your database server.

Conclusion

You have learned how to reset the MySQL/MariaDB root password. The given instruction here will work with all Linux distribution including Ubuntu 18.04, 20.04 CentOS 7, 8 and Debian 9, 10 etc.

If you have any doubt or feedback, please comment below.

- Advertisement -
- Advertisement -

More articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Subscribe to our newsletter

To be updated with all the latest news, offers and special announcements.

Latest article

How to Setup a Firewall with UFW on Ubuntu 20.04

Nowadays, a Firewall is an essential utility and property of any system for security; by default Ubuntu Operating system having a firewall...

How to Install Ruby on Ubuntu 20.04 Linux

Nowadays, Ruby is the most popular language, especially for SaaS application development. It has a perfect and elegant syntax structure, and it...

How to Install Eclipse IDE on Ubuntu 20.04 Linux

Eclipse is the most famous and widely used Java integrated development environment (IDE). It supports many plugins to enhance the capabilities to...

The 30 most useful Linux commands

This article will introduce some practical and common Linux or Unix command , which is the Linux system administrators normally use the command . This article is not a...

How to Reset the MySQL Root Password

Do you want to reset the MySQL root password? Sometimes we forget the root password and not able to log in on...