How to reset or change the MySQL root password?

To reset or change the root password, first we need to open MySQL in the Administrator mode. There, we can see all the users as well as the host. The following is the query −

mysql> USE mysql;
Database changed
mysql> SELECT user,host from user;

Here is the output.

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| John             | %         |
| Mac              | %         |
| Manish           | %         |
| mysql.infoschema | %         |
| mysql.session    | %         |
| mysql.sys        | %         |
| root             | %         |
| am               | localhost |
+------------------+-----------+
8 rows in set (0.00 sec)

Now, let us see the query to change the password.

mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER USER 'Manish'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.14 sec)

As you saw above, ‘Manish’ is a root and we have altered the password. The above query works in MySQL 5.7.6 and higher versions.

To check if the password is reset or changed, we need to open the CMD and reach the directory in the system where the bin is present. Let us first try to login with the old password.

As you can see in the above screenshot, the previous password that has been reset or changed is tried. The same won’t work. Now, we will try to open MySQL with the new password i.e ‘123456’ and it works.