Resetting a Forgotten or Lost MySQL root Password
Published April 29th, 2013 at 11:49 AM. by Joe Prochazka
The following are the command you can use to change your MySQL root password when in the off chance you have forgotten it or no longer have access to someone knowing the password to the root MySQL account. Changing the MySQL root password is easy and fairly painless. To change a forgotten MySQL root password simply follow the following steps.
Step 1: Stop the MySQL process.
You will need to first stop the MySQL server process.
root@darkstar:~# /etc/init.d/mysql stop
Step 2: Start MySQL in safe mode.
Once MySQL has been stop you will then need to start MySQL using mysqld_safe using the flag –skip-grant-tables.
root@darkstar:~# mysqld_safe --skip-grant-tables
After running this command you should see text stating mysqld_safe has been started.
Step 3: Log into MySQL.
Once this has been done you can now log into the MySQL server as root without supplying the root password using the following command. When it asks you for the password simply leave it blank and hit enter.
root@darkstar:~$ mysql --user=root mysql
Step 4: Change the root password.
Now that you are logged into and able to execute statements in MySQL execute the following lines. be sure when doing so to change 'YOUR-NEW-PASSWORD' to the password you wish your MySQL root account password to have.
mysql> update user set Password=PASSWORD('YOUR-NEW-PASSWORD') WHERE User='root';
mysql> flush privileges;
Step 5: Restart MySQL normally and login.
Now you will want to kill the mysqld_safe process by typing the command “fg” which will bring the mysqld_safe process to the foreground. Once the process is in the foreground simply press “ctl+c” to kill the process. Once you have successfully shut down this process you can restart MySQL.
root@darkstar:~$ /etc/init.d/mysql start
You should now be able to log into you MySQL server using the root login once again using the password you just supplied in step 4.
root@darkstar:~$ mysql --user=root -p