How to Reset the MySQL Root Password

sudo mysqld_safe --skip-grant-tables &​

mysql  Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Identify the Server Version

Depending on the MySQL or MariaDB server version you are running on your system, you will need to use different commands to recover the root password.

You can find your server version by issuing the following command:

mysql --version

 

If you have MySQL installed in your system the output will look something like this:

MariaDB case

mysql  Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

 

 

Be sure to make a note of which version of MySQL or MariaDB you’re running.

How to Reset MySQL or MariaDB Root Password

Follow these steps to reset your MySQL/MariaDB root password:

1. Stop the MySQL/MariaDB service

To change the root password first, you need to stop the MySQL server. To do so type the following command:

sudo systemctl stop mysql

 

 

2. Start the MySQL/MariaDB server without loading the grant tables

Start the database server without loading the grant tables:

sudo mysqld_safe --skip-grant-tables &

The ampersand & at the end of the command above will cause the program to run in the background , so you can continue to use the shell.

 

When the --skip-grant-tables option is used, anyone can to connect to the database server without a password and with all privileges granted.

 

3. Log in to the MySQL shell

Now you can connect to the database server as the root user:

mysql -u root

 

4. Set a new root password #

Run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;

 

 

5. Trouble Shooting

In mysqld_safe mode, ALTER 'root' user command was denied, 

- Information Gathering

 

ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

It will mean "Bloat" status, Hence Query need to be updated.

 

MariaDB> UPDATE mysql.user SET authentication_string = PASSWORD('NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';

MariaDB> FLUSH PRIVILEGES;

---

Or We colud choose FLUSH 

MariaDB> FLUSH PRIVILEGES;
MariaDB> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
MariaDB> FLUSH PRIVILEGES;

5. Stop and Start the database server normally

Now that the root password is set, stop the database server and start it normally:

mysqladmin -u root -p shutdown

 

 

You will be prompted to enter the new root password.

Start the database server normally:

 

For MariaDB, type:

sudo systemctl start mariadb