18 Jun 09 Change Root Password MySQL

I don’t know how many times I have had to look up this information in the past. In hopes of learning-through-writing I’ve decided to publish these methods of recovering or resetting the MySQL root password.

Set Initial Password

If you’ve just installed MySQL and have never set the root password a password is not yet needed. Until one is defined you should be able to access your database(s) without the password. To set a password for the first time you can use:

mysqladmin -u root password NEWPASSWORD

Update Root Password
If you want to update or change the existing root password you can use:

mysqladmin -u root -p'oldpassword' password newpass

You should note that this will require that you know the current password.

Recover Lost Password

If you have completely lost the MySQL root password and need to reset it you can. This will require short downtime on your database(s). Below are the steps for:

1. stopping the service.
2. restarting with bypassed security.
3. logging into mysql without authentication
4. inserting a SQL statement, resetting the password.
5. restarting the database.

Stop the database using one of these commands (Linux vs FreeBSD installations):

/etc/init.d/mysql stop
/usr/local/etc/rc.d/mysql-server stop

Start MySQL in safe mode, bypassing security:

mysqld_safe --skip-grant-tables &

Login to MySQL as the root user. No authentication required:

mysql -u root

Provide the following SQL commands to reset the password. Change “NEWPASSWORD” to your password of choice:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Restart MySQL with new authentication in place:

/etc/init.d/mysql restart

I hope this not only helps me remember these steps but helps others who run into the same problem. I have written about Ubuntu specific instructions for resetting the mysql password. These instructions should be global to any distribution or MySQL installation.

Tags: authentication, mysql, reset, root