MySQL Reset Root Password

Posted on in Categories , , , , , , , , , , , , , last updated April 6, 2009

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

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

Share this on (or read 17 comments/add one below):

17 comment

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

    Reply Report comment

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

    Reply Report comment

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

    Reply Report comment

  8. 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?

    Reply Report comment

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

    Reply Report comment

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

    Reply Report comment

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

    Reply Report comment

Leave a Comment

Tagged as: Tags , , , , , , , , ,