Saturday 7 April 2018

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!!

1 comment:

  1. Doesnt work:
    mysql> update user set password=PASSWORD('sprite') WHERE users='root' AND host='localhost';
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('sprite') WHERE users='root' AND host='localhost'' at line 1

    ReplyDelete