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');

Comments

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

Popular posts from this blog

Performance vs. load vs. stress testing

Dynamic DNS updates with nsupdate and BIND 9

Running Gatling load tests in Docker containers via Jenkins