How To Reset Forgotten MySQL 5.7 Root Pass in Linux

Often linux users, and especially server admins, realize they have lost the mysql root pass. When managing databases this is a real issue as without the root password there is no much you can do in terms of database administration.

Whilst there are thousands of articles out there explaining the procedure to recover the password of mysql root user, you may be still facing an issue when trying to replicate the steps from those articles.

Recently, one of our server engineers were called on duty to recover a lost MySQL root password for a client’s CentOS server. In this article we list all necessary steps for a working solution to reset your MySQL root password.

Steps to Recover MySQL Root Account

Step 1: Stop MySQL Server

root@localhost:~$ service mysql stop

Step 2: Start MySQL with skipping grant privileges and networking

The first two commands below are usually omitted from other tutorials, and users are instructed to do only the 3rd command. However, in our case with MySQL 5.7 server we had to do all commands as shown below.

root@localhost:~$ mkdir -p /var/run/mysqld

The command above will create the directory /var/run/mysqld if doesn’t exist; that is the purpose of argument -p.

root@localhost:~$ chown mysql:mysql /var/run/mysqld

Now proceed to starting mysqld with skip-grant-tables and skip-networking arguments.

root@localhost:~$ /usr/sbin/mysqld --skip-grant-tables --skip-networking

Step 3: Password-less login to MySQL as Root

We are now ready to login to mysql as root without the need of a password (we don’t have it anyway!).

root@localhost:~$ mysql -u root

At this point you will see the mysql shell, so continue with the following commands to reset your mysql root password.

Step 4: Flush Privileges & Set New Password

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected, 3 warnings (0.02 sec)

Now let’s set a new root password as follows:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('TypeSomethingStrongHere@#45');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye

Step 5: Restart MySQL Server

Dtop the current mysql process with the following command:

root@localhost:~$ kill %1

Sample Output:

[1]+  Done                    /usr/sbin/mysqld --skip-grant-tables --skip-networking

Start MySQL server normally:

root@localhost:~$ service mysql start

You are good to go! You should be able to access MySQL using your new root password as you set it in Step 4. Congratulations!

Source: https://netshop-isp.com.cy/blog/how-to-reset-forgotten-mysql-5-7-root-pass-in-linux/