≡ Menu

MySQL Reset Root Password

I forgot the MySQL root password. How do I reset MySQL root password?

It is possible to reset the root password. Use the following procedure for resetting the password for any MySQL root accounts on Unix / Linux / BSD like operating systems.

You need to login as root user under UNIX / Linux.

Step # 1: Stop MySQL

Type the following command to stop mysql server under RHEL / Fedora / CentOS Linux:
# /etc/init.d/mysqld stop
Use the following command if you are using FreeBSD:
# /usr/local/etc/rc.d/mysql-server stop
Use the following command if you are using Debian / Ubuntu Linux:
# /etc/init.d/mysql stop
Alternatively, you can stop the MySQL server by sending a kill to the mysqld process, using the path name of the .pid file in the following bash specific command:
# kill $(cat /var/run/mysqld/mysqld.pid)
OR
# pgrep -u mysql mysqld
# kill PID

Or, simply use killall command under Linux:
# killall mysql

Step # 2: Create new mysql password sql script

Create a sql file called /root/mysql.reset.sql as follows (replace YOUR-NEW-MYSQL-PASSWORD with actual new password you would like to use):
# vi /root/mysql.reset.sql
Append the following code:

UPDATE mysql.user SET Password=PASSWORD('YOUR-NEW-MYSQL-PASSWORD') WHERE USER='root';
FLUSH PRIVILEGES;

Save and close the file.

Step # 3: Set new mysql root password

Type the following command:
# mysqld_safe --init-file=/root/mysql.reset.sql &
Sample output:

nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[20970]: started

And you are done with resetting operation. Simply, stop server and start again:
# killall mysqld
# /etc/init.d/mysql start

Share this tutorial on:

Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft, takes a lot of my time and hard work to produce. If you use nixCraft, who likes it, helps me with donations:
Become a Supporter →    Make a contribution via Paypal/Bitcoin →   

Don't Miss Any Linux and Unix Tips

Get nixCraft in your inbox. It's free:



{ 17 comments… add one }
  • yoander (sedlav) April 6, 2009, 1:39 pm

    In RHEL / Fedora / CentOS Linux yun can type also service mysqld stop

  • Raheem April 6, 2009, 4:35 pm

    Thank you ! Nice piece of info.
    In fact, my colleague did the most stupid thing of his life yesterday by deleting all the users in the mysql table :(
    I did get back everything by simply altering the commands in the above given example to insert a user called root, then granting permission to all databases, reset its password etc.

  • Mihai Secasiu April 6, 2009, 4:58 pm

    Or you could simply stop mysql then start it without privilege checking like this:
    mysqld –skip-grant-tables
    connect to it as root, run that update query, then kill the server and start it normally

    • nixCraft April 6, 2009, 5:04 pm

      mysqld –skip-grant-tables method is considered as insecure and in some case it may corrupt the data.

      HTH

  • dardo April 6, 2009, 8:15 pm

    Excuse me for my bad English, I usually read it all time but rather speak or write it.

    Thanks for share this method.

    I install mySQL on my NSLU2 this weekend and with the Debian default install there are no root user, but the debconf ask it’s password twice at install time.

    I use the skip-grant-tables method, but i was thinking on more secure ways to do it because withe the skip-grant-tables any user potentially can take the control of your databases.

  • micman April 11, 2009, 2:59 pm

    thanks :)

  • GaQuay April 16, 2009, 12:00 am

    for gui, you can use webmin, it so easy for change password mysql :)

  • Frank Wang April 20, 2009, 2:07 pm

    Thanks, very nice instruction. A little typo at the end:
    “# /etc/init.d/mysql start” should be “# /etc/init.d/mysqld start”

  • V.Balaviswanathan April 21, 2009, 9:30 am

    Thanks a lot for the tips that you have provided and I would like to know the same for debian and Ubuntu systems as in Ubuntu there is no root login as such right?

  • nixCraft April 21, 2009, 12:44 pm

    @V.Balaviswanathan,

    MySQL has root user too. Do not confuse it with system root user (to become root user Ubuntu enter sudo -s).

    @ Frank,
    /etc/init.d/mysql or /etc/init.d/mysqld depends upon your distro.

  • V.Balaviswanathan April 21, 2009, 12:52 pm

    Thanks a lot Vivek for the tip that you have provided :)

  • Antony Dick M. May 22, 2009, 7:21 am

    When there is a crash in connecting the MySQL Database do the following procedures

    To Stop:
    etc/init.d/mysql stop

    To Start:
    etc/init.d/mysql start

    To Restart:
    etc/init.d/mysql restart

    To Reset The Password:
    /etc/init.d/mysql reset-password

    To Make Your Database To Get Connected When You Are In A Network:
    gedit /etc/mysql/my.cnf
    //search for bind address and then
    change bind address 0.0.0.0

  • rizky June 5, 2009, 10:50 pm

    whoa that’s cool dude

  • Camilo September 19, 2010, 11:40 pm

    I use Fedora 12 and It doesn’t work for me.

  • Paul Jardine March 17, 2012, 8:11 am

    I had problems in Ubuntu, due to apparmor preventing mysql from reading sql files from the root directory.
    Placing the init-file in /usr/share/mysql directory solved the problem. Or you can look at /etc/apparmor.d/usr.sbin.mysqld

  • jenie April 24, 2012, 2:51 pm

    I NEED THE CURRENT PASSWORD FOR ROOT IN XAMPP INORDER TO CHANGE MY PASSWORD.

    THANK YOU!

  • cgz - exito en los negocios por internet January 3, 2013, 7:54 pm

    Thanks for the information, but I can`t go to mysql. this is de error:
    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

    grettings

Security: Are you a robot or human?

Leave a Comment

You can use these HTML tags and attributes: <strong> <em> <pre> <code> <a href="" title="">


   Tagged with: , , , , , , , , ,