Step by step recovery of a forgotten MySQL root password

In this article, you will learn how to recover a forgotten MySQL root password. Occasionally it happens when we forget the passwords. Suppose you are a database administrator and have forgotten the MySQL Server root password. In this case, problems can arise if you don’t know how to recover the root password. Fortunately, there is a way to recover the forgotten MySQL root password.

Step by step recovery of a forgotten MySQL root password

Follow these steps to restore (reset) a forgotten MySQL root password:

Here I assume that you have already installed the MariaDB MySQL server on your system. Here is my practice lab. I have already installed the MariaDB MySQL packages. See the snapshot below.

[[email protected] ~]# rpm -qa | grep mariadb   # Check the Installed Package
mariadb-5.5.52-1.el7.x86_64
mariadb-server-5.5.52-1.el7.x86_64
mariadb-libs-5.5.52-1.el7.x86_64

I installed MariaDB MySQL version 5.5.52. Refer to the sample output below.

[[email protected] ~]# mysql --version;   # Version of MariaDB MySQL
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Here is the scenario that I forgot the MySQL root password. As you can see below, the following error message appears when I try to log on to MySQL Server.

[[email protected] ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

To reset the MySQL root password, first stop the MariaDB MySQL service with the following command.

[[email protected] ~]# systemctl stop mariadb   # Stop the MariaDB MySQL Service

Read also: How to install LAMP Stack with PhpMyAdmin in Ubuntu 18.04

You can check the current status of the MySQL service with the following command. It stopped here. See sample output below.

[[email protected] ~]# systemctl status mariadb   # Check the Status of MySQL Service
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Sun 2017-08-06 13:50:00 IST; 6s ago
 Main PID: 20946 (code=exited, status=0/SUCCESS)

Aug 06 13:01:54 localhost.localdomain systemd[1]: Starting MariaDB database server...
Aug 06 13:01:55 localhost.localdomain mysqld_safe[20946]: 170806 13:01:55 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Aug 06 13:01:55 localhost.localdomain mysqld_safe[20946]: 170806 13:01:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Aug 06 13:01:56 localhost.localdomain systemd[1]: Started MariaDB database server.
Aug 06 13:49:59 localhost.localdomain systemd[1]: Stopping MariaDB database server...
Aug 06 13:50:00 localhost.localdomain systemd[1]: Stopped MariaDB database server.

Now start the MySQL service in a secure state with the following command to reset the MySQL root password. This command allows you to log in to MySQL Server without using a password. In this mode you have full permissions to perform administrative tasks.

[[email protected] ~]# mysqld_safe --skip-grant-tables &   # Start the MySQL Server in Safe State
[1] 54754
[[email protected] ~]# 170806 13:51:03 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170806 13:51:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Now log in to MySQL Server with the username “root”Without a password.

[[email protected] ~]# mysql -u root   # Login in to MySQL Server using "root" without Password
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]>

After logging into MySQL server with the username “root”And without a password, change the database to MySQL Use the following command.

MariaDB [(none)]> use mysql;   # Change the Database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Now use the following command to change the MySQL root password Where pass @ 123 (Highlighted in blue) the new password is here.

MariaDB [mysql]> update user set password=PASSWORD("[email protected]") where User='root';   # Change the MySQL root Password
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> quit
Bye

Now check the current execution status of MariaDB MySQL Server Service with the following command.

[[email protected] ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Sun 2017-08-06 13:50:00 IST; 6min ago
 Main PID: 20946 (code=exited, status=0/SUCCESS)

Aug 06 13:01:54 localhost.localdomain systemd[1]: Starting MariaDB database server...
Aug 06 13:01:55 localhost.localdomain mysqld_safe[20946]: 170806 13:01:55 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Aug 06 13:01:55 localhost.localdomain mysqld_safe[20946]: 170806 13:01:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Aug 06 13:01:56 localhost.localdomain systemd[1]: Started MariaDB database server.
Aug 06 13:49:59 localhost.localdomain systemd[1]: Stopping MariaDB database server...
Aug 06 13:50:00 localhost.localdomain systemd[1]: Stopped MariaDB database server.

As you can see in the sample output above, the MariaDB MySQL service is currently in a stopped state. Use the following command to start the service.

[[email protected] ~]# systemctl start mariadb   # Start the Service

Read also: How to install MySQL 8.0 in Ubuntu 18.04

So we successfully restored the MySQL root password. Now let’s log on to the MySQL server root with a new password and perform some database related tasks. See the following sample output.

[[email protected] ~]# mysql -u root -p   # Logging in to MySQL Server
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show databases;   # Check available Databases
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| usersdb            |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use usersdb;   # Change Database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [usersdb]> show tables;   # Check available Tables
+-------------------+
| Tables_in_usersdb |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

MariaDB [usersdb]> quit
Bye

That’s all. In this article we have explained step by step Recover a forgotten MySQL root password, I hope you like this article. If you like this article, just share it. If you have any questions about this article, please comment.

Sidebar