STEP BY STEP RECOVER FORGOTTEN MYSQL ROOT PASSWORD
STEP BY STEP RECOVER FORGOTTEN MYSQL ROOT PASSWORD
Introduction
In this article we are going to learn How to recover forgotten MySQL root password. Sometime 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.
Follow the below steps to reset forgotten MySQL root password :
Here I am assuming that you have already installed mariadb mysql server on your system. Here on my practice lab I have already installed the MariaDB MySQL packages. Refer the snapshot below.
[root@localhost ~]# 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 the sample output below.
[root@localhost ~]# 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.
[root@localhost ~]# 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.
[root@localhost ~]# systemctl stop mariadb # Stop the MariaDB MySQL Service
Also Read :
You can check the current status of MySQL service using below command. Here it’s stopped, Refer the sample output below.
[root@localhost ~]# 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 below command to reset MySQL root password. This command will allow you to login in to MySQL Server without using any Password and in this mode you will have full privileges to do any administrative tasks.
[root@localhost ~]# mysqld_safe --skip-grant-tables & # Start the MySQL Server in Safe State
[1] 54754
[root@localhost ~]# 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 in to MySQL Server using the username “root” without password.
[root@localhost ~]# 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 in to MySQL Server with username “root” and without password, change the database to mysql using 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 pass@123 (Highlighted in Blue Color) is the new password Here.
MariaDB [mysql]> update user set password=PASSWORD("pass@123") 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 below command.
[root@localhost ~]# 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 on sample output above currently MariaDB MySQL service is in stopped state. Use the below command to start the service.
[root@localhost ~]# systemctl start mariadb # Start the Service
So we have successfully recover the MySQL root Password. Now let’s login in to the MySQL server using root with new password and perform some database related tasks. Refer the sample output below.
[root@localhost ~]# 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
This is how we can Recover MySQL root password. If you found this article useful then Like Us, Share Us, Subscribe our Newsletter OR if you have something to say then feel free to comment on the comment box below.
