Forgot MySQL Root Password – How To Reset It?

by Ramesh Natarajan on July 22, 2009

Recover MySQL Root Password on Ubuntu and DebianForgot 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 Sysadmin Course 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..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 13 comments… read them below or add one }

1 S.RAGHU July 22, 2009 at 3:11 am

Ramesh , thank you for sharing this information. We were previously following lot of roundabout methods.

2 Andrey July 22, 2009 at 3:13 am

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.

3 Taiwan Fan July 23, 2009 at 10:14 am

Useful info.

thanks

4 ndr July 23, 2009 at 8:08 pm

very nice info…hopely can practice someday..thanks

5 Ramesh Natarajan July 25, 2009 at 12:09 am

@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.

6 senthil September 1, 2009 at 11:27 pm

its very useful info…

7 sv September 20, 2009 at 9:32 am

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

8 Estelita July 28, 2010 at 7:07 am

My server says: ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES) I’m really stuck.

9 jasonpsage July 25, 2011 at 8:37 am

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

10 jasonpsage July 25, 2011 at 10:01 am

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

11 Ben October 8, 2011 at 3:50 am

Fantastic! Thank-you.
Only thing extra I had to do was before step 5,
mysql> USE mysql;
Very useful article.

12 ZS May 6, 2012 at 9:29 am

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.

13 Akansha February 8, 2014 at 5:45 am

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.

Leave a Comment

Previous post:

Next post: