Resetting MySQL Admin Users Password when Forgotten

It happens to the best of us, you set a password a long time ago and just cannot remember what it was!. This Documentation will talk you through the process of resetting the admin users password on MySQL

There are two sets of steps, depending on your setup. The initial steps, however, remain the same;

sudo -s (or su)
/etc/init.d/mysql stop

# Keep the next command on one line!
/usr/bin/mysqld_safe --socket=/var/lib/mysql/mysql.sock
 --user=mysql  --pid-file=/var/run/mysqld/mysqld.pid 
 --datadir=/var/lib/mysql --skip-grant-tables --skip-networking &

Now, If you have a root account enabled within MySQL, or know the name of the admin user (replace root with the correct name);

mysqladmin -u root flush-privileges password "newpass"

 

If you disabled the root account and created a different user (e.g. sqladmin) but can't remember what it is.

mysql
USE mysql;

SELECT use, host, password from user;

# Once you've recognised the username, 
#replace username and localhost with the relevant ones displayed in the table
UPDATE user set password = password('newpass') where user='username' and host='localhost';
quit;

Now as the final step, we need to get MySQL running properly again

kill 'cat /var/run/mysqld/mysqld.pid'
/etc/init.d/mysqld start

You can now log in using the password newpass