Main menu

Recover lost MySQL root Password

many times you face such issues when you don’t remember the MySQL root password, some one has changed it or you just wanna break into MySQL. Here in this article we will see how can we recover lost MySQL password. This article divided into two section first when you lost root password and second when someone deleted or renamed root user and lost your password.

Section1: Use these easy steps to recover lost MySQL password.

If your old sysadmin not told you what was Recover lost MySQL root Password without any problem.

Step1 : Stop mysqld service

[root@sector19.net]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]

Step2 : Start mysqld in safe mode with –skip-grant-tables

[root@sector19.net]# mysqld_safe --skip-grant-tables &
[1] 25158
[root@awslamp yum.repos.d]# 140813 16:30:43 mysqld_safe Logging to '/var/log/mys              qld.log'.
140813 16:30:43 mysqld_safe Starting mysqld daemon with databases from /var/lib/              mysql

Step3 : Login with root user

[root@sector19.net]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38-cll-lve MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Step4 : Change MySQL root password

mysql> use mysql;

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

Database changed

mysql> update user set password=PASSWORD("mypassword") where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit

Note: If you are getting this when running update command please refer to section 2

mysql> update user set password=PASSWORD("PASSWORD") where User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Step5 : Restart your MySQL server

[root@sector19.net]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ] Starting mysqld:                                           [  OK  ]

Step6: Login with your new password

[root@sector19.net]# mysql -u root -p

Server version: 5.5.38-cll-lve MySQL Community Server (GPL)
--
--
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Section 2 : If there isn’t any root user available.

Please follow step 1 to 3

mysql> use mysql;
mysql> select user,host from user where user='root' \G
Empty set (0.00 sec)

This mean there isn’t any root user avaible in your MySQL installation.

mysql> INSERT INTO user VALUES ('localhost','root',password('newpassword'),'Y', 'Y ','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y','Y','Y','Y ','Y','Y','Y','Y','Y','' ,'','','',0,0,0,0);

If you get this error means you don’t have equal count of column inserted in your insert command

ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> desc user;

and it will give you the exact count how many column you need to insert modify your query according this and reinsert.

Now follow steps 5 to 6.

FacebookTwitterGoogle+RSS