Tuesday, June 25, 2002

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