How To Recover/Reset Forgotten MySQL/MariaDB root User Password On Linux?

Some times we may forget MySQL/MariaDB root user password.

It’s happening everywhere. It’s human mistake and it known things.

It’s happening for everyone and not only for me.

If so, how to reset the password of MySQL/MariaDB root user?

Are you forgot your MySQL/MariaDB root user password?

Don’t worry, we are here to help you out from the situation.

In this article, we will show you, how to recover forgotten MySQL/MariaDB root user password in few simple steps.

You may be getting the below same error message when you type wrong MySQL/MariaDB root user password.

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

1) How To Stop MySQL/MariaDB Service On Linux?

First, we need to stop the MySQL/MariaDB service before performing the following steps.

Run the following command to stop MySQL/MariaDB service on SysVinit systems.

# service mysqld stop
or
# /etc/init.d/mysqld stop

Run the following command to stop MySQL/MariaDB service on systemd systems.

# systemctl stop mariadb
or
# systemctl stop mariadb.service

2) Start The MySQL/MariaDB Service With The –skip-grant-tables Option?

Use the below command to start the MySQL/MariaDB startup script with --skip-grant-tables option. So, that it will not prompt for password for accessing the MySQL/MariaDB database.

# mysqld_safe --skip-grant-tables &
190512 23:51:04 mysqld_safe Logging to '/var/lib/mysql/daygeek-Y700.err'.
190512 23:51:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Now, you can able to login MySQL/MariaDB interface without entering the MySQL/MariaDB password.

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.14-MariaDB Source distribution

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

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

MariaDB [(none)]>

By default MySQL/MariaDB are containing the following three databases.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.003 sec)

• mysql: It’s the system database that contains tables that store information required by the MySQL server.
• information_schema: It provides access to database metadata.
• performance_schema: Is a feature for monitoring MySQL Server execution at a low level.

We need to navigate to the default MySQL database to set a new password.

MariaDB [(none)]> use mysql;
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

3) How To Update MySQL/MariaDB root User Password?

Run the below command to update/set a new root user password for MySQL/MariaDB. Input your password instead of ours “newpassword”.

MariaDB [mysql]> update user set password=PASSWORD("mysql") where User='root';
Query OK, 3 rows affected (0.002 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Flush the privileges, which makes the new changes to take effect.

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

Finally, exit from the MySQL/MariaDB interface by running “quit” command.

MariaDB [mysql]> quit
Bye

4) How To Launch The MySQL/MariaDB Interface With New Password?

Once you have done the above steps. We need to stop/start the MySQL/MariaDB service once again.

Run the following command to stop MySQL/MariaDB service on SysVinit systems.

# service mysqld stop
or
# /etc/init.d/mysqld stop

Run the following command to stop MySQL/MariaDB service on systemd systems.

# systemctl stop mariadb
or
# systemctl stop mariadb.service

Run the following command to start MySQL/MariaDB service on SysVinit systems.

# service mysqld start
or
# /etc/init.d/mysqld start

Run the following command to start MySQL/MariaDB service on systemd systems.

# systemctl start mariadb
or
# systemctl start mariadb.service

Now, you can able to Login to the MySQL/MariaDB interface with the new Password.

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.14-MariaDB Source distribution

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

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

MariaDB [(none)]>