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