Recovering / Changing Your MySQL Root Password

Recovering / Changing Your MySQL Root Password

Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are:

1) Stop MySQL

[root@bigboy tmp]# service mysqld stop
Stopping MySQL:  [  OK  ]
[root@bigboy tmp]#

2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.

[root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 13007
[root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql
[root@bigboy tmp]#

Note: In Fedora Core 3 and earlier the mysqld_safe command was named safe_mysqld and the general procedure for password recovery was different.

3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.

[root@bigboy tmp]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.

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("ack33nsaltf1sh") WHERE User="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql>

5) Exit MySQL and restart the mysqld daemon.

mysql> exit
Bye
[root@bigboy tmp]# service mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
051224 17:24:56  mysqld ended

Stopping MySQL:  [  OK  ]
Starting MySQL:  [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking
[root@bigboy tmp]#

The MySQL root user will now be able to manage MySQL using this new password.


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image