MySQL, Lost/forgot root password

Help I forgot/lost my root password

I have forgot or have lost my MySQL root password and can not log into the MySQL server using Webmin or phpMyAdmin.

First off don't panic. We can fix it.

The first thing to do

stop MySQL.

Open a terminal (console) window as the root user and enter the command as follows:

 /etc/init.d/mysqld stop

You should see something like this

[root@chimpbox dwmoar]# /etc/init.d/mysqld stop Shutting down MySQL: .... [ OK ] [root@chimpbox dwmoar]#

Safe mode

Next we need to start MySQL in safe mode - that is to say, we will start MySQL but skip the user privileges table. Again, note that you will need to have root (su) access for these commands so you don’t need to worry about any user being able to reset the MySQL root password:

In a terminal (console) window as root enter the following command.

 mysqld_safe --skip-grant-tables &

Note: The ampersand (&) at the end of the command is required.

You should see something like this.

[root@chimpbox dwmoar]# 160321 13:13:45 mysqld_safe Logging to '/var/lib/mysql/chimpbox.localhost.localdomain.err'. 160321 13:13:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

We are now running MySQL in safe mode as a background process.. Now press CTRL+C and press the enter key and you should be returned to the command prompt.

[root@chimpbox dwmoar]# 160321 13:13:45 mysqld_safe Logging to '/var/lib/mysql/chimpbox.localhost.localdomain.err'. 160321 13:13:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql ^C bash: $'\003': command not found [root@chimpbox dwmoar]#

Login

All we need to do now is to log into MySQL and set the password. Enter the following command

 mysql -uroot

Note: No password is required at this stage as when we started MySQL we skipped the user privileges table.

You should see something like this

[root@chimpbox dwmoar]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.73 PCLinuxOS - MySQL Standard Edition (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Select the database

Next, instruct MySQL which database to use: Enter the following command.

 use mysql;

You should now see this

mysql> use mysql; Database changed mysql>

Reset Password

Enter the new password for the root user as follows:

update user set password=PASSWORD("mynewpassword") where User='root';

In my example I am using root as the new password so I would enter the following update user set password=PASSWORD("root") where User='root';

You should see something similar to this.

mysql> update user set password=PASSWORD("root") where User='root'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0

mysql>

and finally, == flush the privileges: ==

Enter the following command

 flush privileges;

You should see this.

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql>

Restart

Now the password has been reset, we need to restart MySQL by logging out: Enter the following command

 quit

You should be greeted with the following

mysql> quit Bye [root@chimpbox dwmoar]#

Now we have to simply start the MySQL server. Enter the following command.

 /etc/init.d/mysqld stop

followed by

 /etc/init.d/mysqld start

You should see this

[root@chimpbox dwmoar]# /etc/init.d/mysqld stop Shutting down MySQL: ....160321 13:32:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended [ OK ] [1]+ Done mysqld_safe --skip-grant-tables [root@chimpbox dwmoar]#

Followed by this

[root@chimpbox dwmoar]# /etc/init.d/mysqld start WARNING: mysql_upgrade should be run (as root). The upgrade from mysql-5.1.55 to mysql-5.1.73 may require it. Starting MySQL: ., [ OK ] [root@chimpbox dwmoar]#

At this time disregard the error message.

Login

Test the new password by logging in: Enter the following command.

 mysql -u root -p

You should see the following

[root@chimpbox dwmoar]# mysql -u root -p Enter password:

In my example I use root for my new password so that is whet I will enter. NOTE you will not see what you type so be careful.

If you entered everything correctly you should see the following.

[root@chimpbox dwmoar]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.73 PCLinuxOS - MySQL Standard Edition (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now enter the following command to exit the MySQL server.

 mysql>quit

you should now be returned to the command line prompt

mysql> quit Bye [root@chimpbox dwmoar]#

Congratulations

You have sucessfully reset your lost or forgotten MySQL root password

MySQL, Lost/forgot root password

Contents