Difference between revisions of "MySQL, Lost/forgot root password"

From PCLinuxOSHelp Knowledge Base
Jump to: navigation, search
(Created page with "=== 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 We...")
 
Line 1: Line 1:
=== MySQL, Lost/forgot root password ===
+
== MySQL, Lost/forgot root password ==
  
Help I forgot/lost my 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 of phpMyAdmin.
 
I have forgot or have lost my MySQL root password and can not log into the MySQL server using Webmin of phpMyAdmin.
Line 14: Line 14:
 
Open a terminal (console) window as the root user and enter the command as follows:
 
Open a terminal (console) window as the root user and enter the command as follows:
  
/etc/init.d/mysqld stop
+
  ''/etc/init.d/mysqld stop''
  
 
You should see something like this
 
You should see something like this
Line 29: Line 29:
 
In a terminal (console) window as root enter the following command.
 
In a terminal (console) window as root enter the following command.
  
mysqld_safe --skip-grant-tables &
+
  ''mysqld_safe --skip-grant-tables &''
  
Note: The ampersand (&) at the end of the command is required.
+
'''''Note:''''' The ampersand (&) at the end of the command is required.
  
 
You should see something like this.
 
You should see something like this.
Line 51: Line 51:
 
All we need to do now is to log into MySQL and set the password. Enter the following command
 
All we need to do now is to log into MySQL and set the password. Enter the following command
  
mysql -uroot
+
  ''mysql -uroot''
  
 
Note: No password is required at this stage as when we started MySQL we skipped the user privileges table.
 
Note: No password is required at this stage as when we started MySQL we skipped the user privileges table.
Line 77: Line 77:
 
Next, instruct MySQL which database to use: Enter the following command.
 
Next, instruct MySQL which database to use: Enter the following command.
  
use mysql;
+
  ''use mysql;''
  
 
You should now see this
 
You should now see this
Line 107: Line 107:
 
Enter the following command
 
Enter the following command
  
flush privileges;
+
  ''flush privileges;''
  
 
You should see this.
 
You should see this.
Line 121: Line 121:
 
Now the password has been reset, we need to restart MySQL by logging out: Enter the following command
 
Now the password has been reset, we need to restart MySQL by logging out: Enter the following command
  
quit
+
  ''quit''
  
 
You should be greeted with the following
 
You should be greeted with the following
Line 131: Line 131:
 
Now we have to simply start the MySQL server. Enter the following command.
 
Now we have to simply start the MySQL server. Enter the following command.
  
/etc/init.d/mysqld stop
+
  ''/etc/init.d/mysqld stop''
  
 
followed by
 
followed by
  
/etc/init.d/mysqld start
+
  ''/etc/init.d/mysqld start''
  
 
You should see this
 
You should see this
Line 160: Line 160:
 
Test the new password by logging in: Enter the following command.
 
Test the new password by logging in: Enter the following command.
  
mysql -u root -p
+
  ''mysql -u root -p''
  
 
You should see the following
 
You should see the following
Line 189: Line 189:
 
Now enter the following command to exit the MySQL server.
 
Now enter the following command to exit the MySQL server.
  
mysql>quit
+
  ''mysql>quit''
  
 
you should now be returned to the command line prompt
 
you should now be returned to the command line prompt

Revision as of 14:41, 14 April 2018

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 of 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)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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