How to reset MySQL root password in Linux

If you encounter problems when you try to connect to the MySQL server and you don’t have root password to login in MySQL.

Step 1: Stop MySQL service:

[root@linuxcnf ~]# service mysqld stop Stopping MySQL:                                          [ OK ] [root@linuxcnf ~]#

Step 2: Start MySQL service in safe mode with skipping grants:

[root@linuxcnf ~]# mysqld_safe --skip-grant-tables & [1] 14910 Starting mysqld daemon with databases from  /var/lib/mysql [root@linuxcnf ~]#

Step 3: Login with root user without password:

[root@linuxcnf ~]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 44863 Server version: 5.6.38 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Step 4: Update the root user password in user table:

mysql> update user set password=PASSWORD("xxxxxxxxxx") where User='root' and Host=’localhost’; Query OK, 1 rows affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>

Step 5: Flush privileges and quit from MySQL prompt:

mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) mysql> \q Bye [root@linuxcnf ~]#

Step 6: restart MySQL service:

[root@linuxcnf ~]# service mysqld restart STOPPING server from pid file /var/run/mysqld/mysqld.pid 213784 02:05:26 mysqld ended Stopping MySQL:                                          [ OK ] Starting MySQL:                                          [ OK ] [1]+ Done mysqld_safe --skip-grant-tables [root@linuxcnf ~]#

Step 7: Finally logging with root user and password to verify the credentials:

[root@linuxcnf ~]# mysql -u root -p  Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 44863 Server version: 5.6.38 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>\q Bye [root@linuxcnf ~]#

Done!!