≡ Menu

Forgot MySQL Root Password – How To Reset It?

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>
Add your comment

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

Comments on this entry are closed.

  • S.RAGHU July 22, 2009, 3:11 am

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

  • Andrey July 22, 2009, 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.

  • Taiwan Fan July 23, 2009, 10:14 am

    Useful info.

    thanks

  • ndr July 23, 2009, 8:08 pm

    very nice info…hopely can practice someday..thanks

  • Ramesh Natarajan July 25, 2009, 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.

  • senthil September 1, 2009, 11:27 pm

    its very useful info…

  • sv September 20, 2009, 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

  • Estelita July 28, 2010, 7:07 am

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

  • jasonpsage July 25, 2011, 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

  • jasonpsage July 25, 2011, 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^)>

  • Ben October 8, 2011, 3:50 am

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

  • ZS May 6, 2012, 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.

  • Akansha February 8, 2014, 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.

  • Qamre alam April 30, 2015, 10:05 am

    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

  • praxis22 December 4, 2015, 10:26 am

    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!