Wednesday, 11 June 2008

Recover MySQL password

This tutorial is written in two ways. One way is for the compiled MySQL and one for the installed MySQL by RPM [tested only in Fedora]. It's not big deal, but I hope it is accessible also to newbies because the directory's change in both examples.

--------------------
If you compiled MySQL by yourself, go this way:

Maybe you have to change the directory where you installed MySQL (here it's /usr/local/mysql/ ).

1. Gain root access to your Linux system

Code:

[boby@space boby]$ su -
Password:
[root@space root]#

2. First you have to stop the daemon

Code:

[root@space root]# /etc/init.d/mysql.server stop
[root@space root]#

3. You will now start MySQL in safe mode without reading the grant tables with all MySQL database passwords and also you will disable networking. The "safe_mysqld" command will do this trick for you.

Code:

[root@space root]# /usr/local/mysql/bin/safe_mysqld --user=mysql
--skip-grant-tables --skip-networking &
[root@space root]#

4. The "mysqladmin" command can now reset the root password. In this case we are setting it to "newpassword".

Code:

[root@space root]# /usr/local/mysql/bin/mysqladmin -u root flush-privileges
password "newpassword"
[root@space root]#

5. And finally restart the daemon

Code:

[root@space root]# /etc/init.d/mysql.server restart
[root@space root]#

6. You can use now your new root password

Code:

[root@space root]# /usr/local/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.20-standard
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

--------------------
If you installed MySQL by RPM or use the package that comes with the distribution, go this way:

1. Gain root access to your Linux system

Code:

[boby@space boby]$ su -
Password:
[root@space root]#

2. First you have to stop the daemon

Code:

[root@space root]# /etc/init.d/mysqld stop
[root@space root]#

3. You will now start MySQL in safe mode without reading the grant tables with all MySQL database passwords and also you will disable networking. The "safe_mysqld" command will do this trick for you.

Code:

[root@space root]# /usr/bin/safe_mysqld --user=mysql
--socket=/var/lib/mysql/mysql।sock --pid-file=/var/run/mysqld/mysqld।pid
--datadir=/var/lib/MySQL --skip-grant-tables --skip-networking &
[root@space root]#

4. The "mysqladmin" command will now reset[rewrite] the root password. In this case we are setting it to "newpassword".

Code:

[root@space root]# mysqladmin -u root flush-privileges password "newpassword"
[root@space root]#

5. Stop the running daemon

Code:

kill `cat /var/run/mysqld/mysqld.pid`

6. And finally restart it

Code:

[root@space root]# /etc/init.d/mysqld start
[root@space root]#

7. You can use now your new root password

Code:

[root@space root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.20-standard
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

--------------------

Hope this helped you!
Please post any mistakes or if I forgot something.

No comments: