If you’ve ever forgotten or misplaced your mySQL root password, there’s a handy trick to resetting it.

This tutorial assumes you have SSH command line access to your server with super user (sudo) permissions and able to edit using vim (or nano).

Step 1

After you’ve logged in, let’s create the simple sql script that will reset your root password.

vim ~/reset.sql

Once in vim edit mode, you’ll need to insert the statement below. Replace NEW-PASSWORD-HERE with your new root password. You can generate a strong password on this site or use an existing strong password.

UPDATE mysql.user SET Password=PASSWORD('NEW-PASSWORD-HERE') WHERE User='root';
FLUSH PRIVILEGES;

Save and exit out of vim

:wq

Step 2

Next you’ll need to stop the mySQL server. If it’s a production site, you should do this during off hours since it will take down your entire database.

# RHEL, Fedora, or CentOS Linux
/etc/init.d/mysqld stop
 
# optional method 
service mysqld stop
 
 
# Debian or Ubuntu Linux
/etc/init.d/mysql stop
 
# optional method 
service mysql stop

Step 3

Now we’re going to restart mysql in safe mode and execute the script you created in step 1. Note the path (~/reset.sql) and the ampersand (&) at the end.

mysqld_safe --init-file=~/reset.sql &

You should see something like this returned

111226 22:31:42 mysqld_safe Logging to syslog.
111226 22:31:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Step 4

The root password should have been reset so let’s restart the mySQL server in normal mode.

# RHEL, Fedora, or CentOS Linux
/etc/init.d/mysqld restart
 
# optional method 
service mysqld restart
 
 
# Debian or Ubuntu Linux
/etc/init.d/mysql restart
 
# optional method 
service mysql restart

Now try logging into mysql with your new root password.

mysql -u root -p

If it works, then you’ve done it correctly! Now make sure to write down your password somewhere or save it online in a safe location.

Like this tutorial? Subscribe and get the latest tutorials delivered straight to your inbox or feed reader.