Thursday, May 10, 2007

Resetting MySQL account passwords

I recently needed to reset the MySQL root account password. Here are the steps, for future reference:

1) Stop mysqld, for example via 'sudo /etc/init.d/mysqld stop'

2) Create text file /tmp/mysql-init with the following contents (note that the file needs to be in a location that is readable by user mysql, since it will be read by the mysqld process running as that user):

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');

3) Start mysqld_safe with following option, which will set the password to whatever was specified in /tmp/mysql-init:

$ sudo /usr/bin/mysqld_safe --init-file=/tmp/mysql-init &

4) Test connection to mysqld:

$ sudo mysql -uroot -pnewpassword

5) If connection is OK, restart mysqld server:

$ sudo /etc/init.d/mysqld restart

Also for future reference, here's how to reset a normal user account password in MySQL:

Connect to mysqld as root (I assume you know the root password):

$ mysql -uroot -prootpassword

Use the SET PASSWORD command:

mysql> SET PASSWORD for 'myuser'@'localhost' = PASSWORD('newuserpassword');

1 comment:

Mark said...

Don't forget to clean up:

6) rm /tmp/mysql-init

Otherwise a world readable file contains your mysql root password.

Also step 4 will reveal your mysql root password to anybody viewing a list of processes.

But anyway ...