MySQL, How to reset lost root password

Did you forget the root password of MySQL ? Here is how get it back.

The following steps tells you how to reset the root password in MySQL (this is specific to Linux environment, I understand the same process would also work in Windows)


1. Stop MySQL service:
# service mysqld stop
Stopping mysqld:                                           [  OK  ]

2. Start Mysql server with "--skip-grant-tables" option (user privileges table). You may optionally provide "--skip-networking" which would prohibit anyone from connecting the server from remote place;

Please note that, one need to start the server using "mysqld_safe" command. You need to send the process in the background by either pressing ctrl+z and send it to the background using "bg" or '&' at the end of the command;

# mysqld_safe --skip-grant-tables --skip-networking
140227 18:39:40 mysqld_safe Logging to '/var/log/mysqld.log'.
140227 18:39:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^Z
[1]+  Stopped                 mysqld_safe --skip-grant-tables --skip-networking

3. Login to Mysql without password (this will not prompt you for password);

[root@Fedora-14 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, 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.

4. Update the password for "root" user with a new 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("root") where user="root";
Query OK, 0 rows affected (0.01 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

5. Stop the MySQL server;

[root@Fedora-14 ~]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]

6. Start back the MySQL server;

# service mysqld start
Starting mysqld:                                           [  OK  ]

7. Login to the server with root user and the new password;

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, 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>