Step By Step Recover Forgotten Mysql Root Password

In this article, we are going to learn how to recover forgotten MySQL root password. Sometimes It happens when we forgot the passwords and Suppose you are a database administrator and forgot the root password of MySQL Server in that case you might come in trouble if you don’t know the ways to recover the root password. But luckily there is a way to recover the forgotten MySQL root password.

Step By Step Recover Forgotten Mysql Root Password

Step By Step Recover Forgotten Mysql Root Password

Follow the below steps to (reset) Recover forgotten MySQL root password:

Here I am assuming 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. Refer to 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 have 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 the scenario is I have forgotten the MySQL root password. As you can see below I am getting the below error when i am trying to login in to MySQL Server.

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

So to reset the MySQL root password first stop the MariaDB MySQL service using below command.

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

Also Read : How to Install LAMP Stack with PhpMyAdmin in Ubuntu

You can check the current status of the MySQL service using the below command. Here it’s stopped, Refer to the 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 Safe State using the below command to reset MySQL root password. This command will allow you to login into MySQL Server without using any Password and in this mode, you will have full privileges to do any 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 login into MySQL Server using 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 login into MySQL Server with username “root” and without password, change the database to MySQL using the below 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 below command to change MySQL root password Where [email protected] (Highlighted in Blue Color) is the new password 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 running status of MariaDB MySQL Server Service using the below 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 currently, the MariaDB MySQL service is in a stopped state. Use the below command to start the service.

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

Also Read : How to Install MySQL 8.0 in Ubuntu

So we have successfully recovered the MySQL root Password. Now let’s login to the MySQL server using root with a new password and perform some database-related tasks. Refer to the sample output below.

[[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 Forgotten Mysql Root Password. I hope you enjoy this article. If you like this article, then just share it. If you have any questions about this article, please comment.

Share this:

ITSmarttricks Team

This is Mangesh Dhulap the Founder and Editor of IT SMART TRICKS have 6+ years of Industrial Experience. We expect from our visitors to like, share, and comment on our posts.

You may also like...

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.