Forgot your MySQL root user password? Don’t worry. We are here for rescue.
When you tried to login to root without entering a password, you may get ‘Access Denied’ message, as MySQL is expecting a password.
This article explains how to recover mysql root password by setting a new MySQL password when you don’t remember your old one.
When you don’t remember root password and enter a wrong password, you will get the following MySQL error message.
# mysql -u root mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
1. Stop MySQL Server
As a first step, stop the mysql server using one of the following method.
# service mysql stop (or) # /etc/rc.d/init.d/mysql stop
2. Add –skip-grant-tables to mysqld_safe Startup Command
Open the mysql startup script and add -skip-grant-tables as shown below.
# vi /etc/rc.d/init.d/mysql Old Line: $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 & New Line: $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
3. Start MySQL Server With –skip-grant-tables
Start the mysql server with skip-grant-tables option, which will allow anybody to login to mysql without entering a password.
# service mysql start Starting MySQL. [ OK ] [Note: This is using the updated /etc/rc.d/init.d/mysql script]
4. Login Using MySQL Root User Without Entering Password
Since you’ve skipped the grant table, this time when you try to login to mysql, it will not ask for password.
# mysql -u root mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.25-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> [Note: MySQL did not ask for any password]
5. Set MySQL Root Password to a New Password Using UPDATE Command
Follow the strong password rules while setting new password for the mysql root account.
mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
6. Stop the MySQL Server
Stop the mysql server using one of the following method.
# service mysql stop (or) # /etc/rc.d/init.d/mysql stop
7. Update /etc/rc.d/init.d/mysql Startup Script and Remove –skip-grant-table
Open the mysql startup script and remove –skip-grant-tables as shown below.
# vi /etc/rc.d/init.d/mysql Old Line: $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 & New Line: $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
8. Start MySQL Server
Start the mysql server without the skip-grant-tables option. This time mysql will ask for password when someone tries to login.
# service mysql start Starting MySQL. [ OK ]
9. Login to MySQL With the New Password
Login to mysql root account with the new password.
# mysql -u root -pnewpassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.25-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.Get the Linux Sysadmin Course Now!
If you enjoyed this article, you might also like..
|
|
|
|
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
{ 12 comments… read them below or add one }
Ramesh , thank you for sharing this information. We were previously following lot of roundabout methods.
This looks like the right way to do that.
Some of my clients insist on having Internet access to their MySql databases. For such cases, I would add steps to disconnect and reconnect network or just add –skip-networking to the command line.
Useful info.
thanks
very nice info…hopely can practice someday..thanks
@Raghu,
Yeah. There are lot of ways to reset mysql root password. I even know a sysadmin, who copied the data files to another mysql instance and tried to reset the root password in that instance by doing lot of juggling.
Using -skip-grants is probably the easiest and most safe method to reset the mysql root password. I’m happy to hear that you are planning to use this method from now-on-wards.
@Taiwan Fan, @Ndr,
Thanks for your comments. I’m very glad that you found this article helpful.
@Andrey,
Thanks for suggesting –skip-networking option. Yeah. I can definitely see a good use of skip-networking in the particular scenario you’ve suggested.
its very useful info…
if you find difficult to make changes with conf file then. follow the steps as follows.
1) /etc/init.d/mysql stop
2) mysqld_safe –skip-grant-tables &
3) Then follow the above steps from step – 3
My server says: ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES) I’m really stuck.
Going through a “server” inoculation/sterilization due to the bad guys getting lucky on a Joomla vulnerability… (not finished yet but….) I went through a period of fast password changes and the like – but out did myself on mysql… SAFELY locked down and secured!…. um… from me too! (B^)>
This article gave me a no non-sense responsible way for a sys admin to clean house, and for that: This article is most appreciated by me.
–Jason P Sage
NOTE: I had to do something weird but it worked. I have multiple hosts per user – so only users from certain origins can connect.
When I ran the update command, as suggested above, even after switching to “use mysql” database.. it found matches but didn’t update the password. “5 matches, 0 Rows Effected” (????? Geesh (B^)>
The ultimate fix for me – risky – but – worked – was – networking was on, I connected remotely with MySQL Administrator (while MySQL running with GRANT options off) (risky but…) I connected with MySQL Administrator, changed the password on the “root@localhost” entry in my user/host entry and applied the password that way.
I then logged out, shut down MySQL, reset the /etc/rc.d/init.d/mysqld file back how it was (no skip grant option) – and Whella! Success!! Just thought I’d share the bit with you folks.
–Jason P Sage – (B^)>
Fantastic! Thank-you.
Only thing extra I had to do was before step 5,
mysql> USE mysql;
Very useful article.
How do you perform this when the client starts up asking for a password?
Enter Password:
From where are you writing these commands the actual mysql client or the MS DOS prompt. I can’t get these commands to work on the mysql client because of the start up password screen and DOS is not recognizing the # sign. Please provide clarity.