Howto reset forgotten MySQL/MariaDB root password

Have you ever forgotten the root password on one of your MySQL/MariaDB servers? No? Well maybe I’m not as perfect as you. In this mini post, I’ll show you an easy way for resetting MySQL/MariaDB root password in case of forgetting it. All the needed from you is having a root access to your Linux/UNIX server then follow the following simple steps:

Step 1: Stop the running MySQL/MariaDB server

Log in as root and stop the mysql/mariadb daemon. The stopping commands is different from Linux release to another, so I’m sure that you will successfully stop the service.

Step 2: Start MySQL/MariaDB in the save mode

Now lets start up the mysql daemon in the safe mode and skip the grant tables which store the passwords.

# mysqld_safe --skip-grant-tables
161219 09:29:25 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
161219 09:29:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysq

You should see mysqld start up successfully. If not, well you have bigger issues.

Step 3: Resetting the forgotten root password

Now you should be able to connect to mysql without a password. In a new terminal run the following commands:

# mysql --user=root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.50-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 [mysql]>

Now, run the following queries to set the new root password:

MariaDB [mysql]> update user set Password=PASSWORD('new-password') where user='root';
MariaDB [mysql]> flush privileges; 
MariaDB [mysql]> exit;

Now, kill your running mysqld_safe process using this command:

# pkill -9 mysqld_safe

Finally, restart MySQL/MariaDB service normally. Now login to your database server with the new password.