mysql

How to reset MySQL root password on Linux


It’s a pretty common situation when you lost your MySQL root password. In earlier versions there was a possibility to open my.cnf file and get the password. But in our situation it’s not that easy. Anyway, we can stop MySQL service, then start with certain key, reset password and restart the service. That’s method advised on the official website.

Now we are on Centos 7 machine, but the guide can still be used on different Linux systems.

At first you need to stop MySQL service. To do it just type in this command:

systemctl stop mysqld

Now, we need to start MySQL service in safe mode. Run the command below:

mysqld_safe --skip-grant-tables &

After that you have to connect to MySQL client over SSH. Type in this command:

mysql -u root

It’s time to reset MySQL password. You need to perform multiple commands:

use mysql;
 update user set password = PASSWORD ( "NewPassword") where User = 'root';
 flush privileges;
 quit

If you did everything right, your terminal should look like that:

mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 5.5.37-MariaDB-wsrep MariaDB Server, wsrep_25.10.r3980
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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
MariaDB [mysql]> update user set password=PASSWORD("NewPassword") where User='root';
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

Once it is done, just press CTRL+C to stop MySQL service.

READ ALSO  Fix Spotify Error 17 (Installer)

That’s pretty much it. Now you can start it again by using:

systemctl start mysqld

Connect to it by using your new password:

mysql -u root -p

You may also like:

Recover SA Password on Microsoft SQL Server 2012 If you want to connect to Microsoft SQL Server, but the SA password is successfully forgotten and there is no user with the SQL Administrator rights —...
How to Recover Lost or Forgotten Password for MS A... Hello guys! Today we are going to show you how to recover lost or forgotten password for MS Access database file (*.MDB). There are a lot of tools tha...