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>
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
Comments on this entry are closed.
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.
As soon as I enter password.. which is ideally wrong. Mysql screen close down.
I am unable to write anything anywhere ( Step 1 : mentioned)
Please guide.
Hi Ramesh,
I am unable to find the bellow line in /etc/init.d/mysqld
Old Line: $bindir/mysqld_safe –skip-grant-tables –datadir=$datadir –pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
Please assist
Thans Regard
Qamre Alam
I’ve probably spent 8 hours over two days trying to get this to work on Solaris, (with 5.1 Enterprise-advanced) Which due to the peculiar nature of how they were built, don’t have an /etc/my.cnf
However, we then have somebody saying, we want this server to look like that server. Which is the same. Then I have to change the password on the new one, only it doesn’t work, no matter what I do, no mater what the interwebs tell me. Argh! (in fact make that double Argh! with a side order of Argh! to go 🙂
My error was invariably:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
This is what worked for me, when nothing else did:
1. /etc/init.d/mysql stop
2. edit /etc/init.d/mysql add –skip-grant-tables to the $bindir/mysqld_safe –datatdir line
3 /etc/init.d/mysql start
4 mysql -u root mysql
5. UPDATE mysql.user SET Password=PASSWORD(‘mypassword’) WHERE User=’root’;
6. FLUSH PRIVILEGES;
7. quit;
8. /etc/init.d/mysql stop
9. edit /etc/init.d/mysql remove –skip-grant-tables from the $bindir/mysqld_safe –datatdir line
10. /etc/init.d/mysql start
11. mysql -u root mysql -p
The key things to understand is that you have to change the init script, and you have to change the password to something different, otherwise it will not be updated. If you keep the same password as you know you have, without doing it this it won’t work.
Good luck!