Resetting a forgotten MySQL root password

Posted by Steve on Thu 28 Sep 2006 at 09:12

Tags: ,

Resetting the root password of a MySQL database is trivial if you know the current password if you don't it is a little tricker. Thankfully it isn't too difficult to fix, and here we'll show one possible way of doing so.

If you've got access to the root account already, because you know the password, you can change it easily:

steve@steve:~$ mysql --user=root --pass mysql
Enter password:

mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

However if you don't know the current password this approach will not work - you need to login to run any commands and without the password you'll not be able to login!

Thankfully there is a simple solution to this problem, we just need to start MySQL with a flag to tell it to ignore any username/password restrictions which might be in place. Once that is done you can successfully update the stored details.

First of all you will need to ensure that your database is stopped:

root@steve:~# /etc/init.d/mysql stop

Now you should start up the database in the background, via the mysqld_safe command:

root@steve:~# /usr/bin/mysqld_safe --skip-grant-tables &
[1] 6702
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6763]: started

Here you can see the new job (number "1") has started and the server is running with the process ID (PID) of 6702.

Now that the server is running with the --skip-grant-tables flag you can connect to it without a password and complete the job:

root@steve:~$ mysql --user=root mysql
Enter password:

mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

Now that you've done that you just need to stop the server, so that you can go back to running a secure MySQL server with password restrictions in place. First of all bring the server you started into the foreground by typing "fg", then kill it by pressing "Ctrl+c" afterwards.

This will now allow you to start the server:

root@steve:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

Now everything should be done and you should have regained access to your MySQL database(s); you should verify this by connecting with your new password:

root@steve:~# mysql --user=root --pass=new-password-here
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.24a-Debian_4-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

If you'd like to automate this process you could start by looking at this simple shell script which will allow you to reset a password with one command.

 

 


Posted by micky (147.162.xx.xx) on Thu 28 Sep 2006 at 09:35
You can also (on a Debian system) do it without shutting down mysql, just use the password of the debian user store in /etc/mysql/debian.cnf to wipe out the password of the root user.

[ Parent | Reply to this comment ]

Posted by websissy (75.161.xx.xx) on Wed 23 Jul 2008 at 23:55
Jesus H Christobal and his mom Maria... Whose GENIUS idea was THIS? Why would ANYONE create a server-wide password that trumps other passwords (Notably in this case the DBA's password??) on the server and then store it as plain text in a config file in a known location? I don't know how anyone else feels, but THAT looks like an ENORMOUS security hole to ME. Did I somehow overlook some key detail of this discussion?

Can anyone please tell me what happens if I REMOVE those two passwords from that file? What apps on the system actually use or rely on them?

Thanks!

[ Parent | Reply to this comment ]

Posted by chris (193.30.xx.xx) on Thu 24 Jul 2008 at 07:38
[ View Weblogs ]

The debian.cnf file? Well - its used by the mysql startup scripts as the config file for mysqladmin, its used (AFAIK) by the install scripts of the mysql deb's for upgrades, perhaps others too.

On a properly configured system - the debian.cnf file is root access only. If you're already root then you can restart mysql without the grant tables anyway.

[ Parent | Reply to this comment ]

Posted by websissy (75.161.xx.xx) on Thu 24 Jul 2008 at 12:17
That's small consolation IMHO. The difference with the root password is IT's NOT stored anywhere on the server except in MD5 encrypted format. Anyone who has ever had the experience of having their server hacked and their databases destroyed knows why one does NOT want to store a password more IMPORTANT than the DBA's password in clearly readable unencrypted form in an easy-to-find file on the server. Jimminy Cricket, guys!

Worse yet, we find one of the first comments in a visible-to-anyone public discussion on how to change the DBA's password is an open explanation about how to DEFEAT the DBA's password and take control of the database anyway. That's a pristine moment of George Bush-class arrogance and stupidity.

Heck, why don't we just post a ROADMAP for where to find the ultimate database password on billboards at major highway intersections worldwide? In fact, why make it HARD for hackers? Why not print the ultimate password on POSTAGE stamps? After all, our servers are all totally bulletproof and protected, right?

My last webhost exhibited similarly collosal arrogance by storing MySQL's root password in plain text in a file that could easily be opened and read by anyone who had root-level file access to the server. They also had a know-it-all tech on staff who tried feeding me a line of pure-BS one night until I realized what he was doing, called him on it and asked to speak to a senior tech. That tech agreed I was right and resolved my issue in less than a minute.

A few days later that same renegade-tech did a deliberate eggdrop on my server and planted a copy of cgiemail -- a popular open mail relay there. The rest as they say is history. Later, when the databases were attacked and destroyed, the path to that destruction ran straight past that "hidden password" file and into the database itself. Based on the time stamps, the database hacking and installation of cgiemail were done the same night within an hour of one another.

It turned out someone who knew as much about their servers as that renegade tech had used MySQL's root password to insert a new user into the database who had root-level privileges and could login through port 3306 from anywhere in the world.

One night the database was attacked and systematically destroyed. Later I found the muddy footprints of that secret port 3306 user all over the rubble. Need I say more?

Needless to say, the FIRST thing I did during the server rebuild was completely REMOVE that hidden MySQL password from the server. But at that point, the damage had been done.

Fortunately, I'm a 40 year IT pro and had a current backup... Enough said.

[ Parent | Reply to this comment ]

Posted by chris (193.30.xx.xx) on Thu 24 Jul 2008 at 12:20
[ View Weblogs ]

Feel free to raise a bug on the mysql packages :)

[ Parent | Reply to this comment ]

Posted by chris (193.30.xx.xx) on Thu 24 Jul 2008 at 12:25
[ View Weblogs ]

Hmm - missed a line there.

Regards to "Worse yet, we find one of the first comments in a visible-to-anyone public discussion on how to change the DBA's password is an open explanation about how to DEFEAT the DBA's password and take control of the database anyway."

I can't agree. "Security by not talking about it" isn't security to me. This file is on ALL debian systems that use debian's packaged mysql. Hardly unknown and many many machines.

The better the administrators of these systems understand on what is present on their systems and why the better.

If the file should or should not be present is an interesting argument - but - pretending it isn't there isn't going to help.

[ Parent | Reply to this comment ]

Posted by Anonymous (12.47.xx.xx) on Mon 26 Apr 2010 at 13:36
Can you tell me how yo do this. I found the file and the password. I tried logging in to mysql with and received an 'access denied' message. I tried to login using putty with this id an password and againg was denied. I am a windows user, our only linux user quit and now I have to change some mysql stuff. Unfortunately the root id was working but for some reason it no longer works. It either got corrupted or changed by accident. I have tried to start mysqld_safe with the "--skip-grant -tables" option and itjust gives an error message. Please help. Thanks
john
john.keusch@uaw-gm.org

[ Parent | Reply to this comment ]

Posted by amluidgi (41.204.xx.xx) on Fri 13 Jun 2014 at 13:09
I went to the /etc/mysql/ directory and red the debian.cnf file.
Does encrypted Debian password work wherever one uses it in mysql??

Many Thanks

[ Parent | Reply to this comment ]

Posted by mcphail (62.6.xx.xx) on Thu 28 Sep 2006 at 10:32
Nice tip. but I'd suggest that you don't use the password on the command line (where it will be stored in your ~/.bash_history). Passing a bare "-p" flag will get mysql to prompt you for the password.

[ Parent | Reply to this comment ]

Posted by Anonymous (213.164.xx.xx) on Thu 28 Sep 2006 at 15:21
It'll be viewable to all using ps axu too.

[ Parent | Reply to this comment ]

Posted by jeld (163.192.xx.xx) on Thu 28 Sep 2006 at 19:10
Nope. MySQL client is smart enough not to show command line password in the ps list. It will get recorded in the bash history though.

[ Parent | Reply to this comment ]

Posted by Anonymous (62.31.xx.xx) on Fri 29 Sep 2006 at 00:18
It is visible for a short time until the client removes it from argv, and hence it may be swapped to disk.

[ Parent | Reply to this comment ]

Posted by Anonymous (213.202.xx.xx) on Sun 1 Oct 2006 at 10:38
When I enter passwords or sensitive information in the shell, I exit the shell with 'kill -9 0' (or the equivalent 'kill -9 $$') to just make the shell exit immediately, without saving the history.

[ Parent | Reply to this comment ]

Posted by goeb (84.184.xx.xx) on Sun 1 Oct 2006 at 22:21
[ View Weblogs ]
A simple way to prevent a command from being recorded in .bash_history is using
export HISTCONTROL=ignoreboth
in your .bashrc file. With this set all you need to do to is to start the command with a space in front of it to prevent it from being written to the history file.

[ Parent | Reply to this comment ]

Posted by Anonymous (62.254.xx.xx) on Thu 28 Sep 2006 at 19:34
thanks, handy to have this on the site.

i once forgot the password and whilst i dont remember how i reset it the first time, whatever i did reset ALL user passwords, so the next time i used the debian-maintainer user/password and reset it that way :)

sno

[ Parent | Reply to this comment ]

Posted by Anonymous (210.185.xx.xx) on Fri 29 Sep 2006 at 06:00
One thing I noticed in your script Steve, is that you kill -9 the mysqld. Wouldn't it be nicer to send it a standard kill signal, and reduce the risk of corrupting the database?
Cheers,
Jonesy

[ Parent | Reply to this comment ]

Posted by Steve (62.30.xx.xx) on Tue 3 Oct 2006 at 09:07
[ View Steve's Scratchpad | View Weblogs ]

Yes it probably would, still for a simple demonstration script it appears to work fairly well.

Steve

[ Parent | Reply to this comment ]

Posted by kernst (66.133.xx.xx) on Thu 11 Nov 2010 at 18:27
For the love of whatever deity you hold most dear, do not get in the habit of using 'kill -9' for anything (unless you understand what it does and have no other option). It *does* cause data loss. This is one of the classic Unix blunders: see http://partmaps.org/era/unix/award.html#uuk9letter for reference.

Issuing 'mysqladmin shutdown' as root will cleanly shut down the MySQL instance that you have running in the background. Better yet, you'll see the console messages that tell you it's doing this. As written, the instructions don't work with recent (ca. 2010) versions of MySQL as CTRL + C just causes the server to respawn itself.

Hope this helps.

[ Parent | Reply to this comment ]

Posted by Anonymous (142.179.xx.xx) on Wed 18 Oct 2006 at 01:02
Great fixed my Problem !!

[ Parent | Reply to this comment ]

Posted by Anonymous (59.92.xx.xx) on Mon 30 Oct 2006 at 13:52
Very useful info. I had the same problem on windows since somehow root password stopped working, so I needed to reset it. And since there is no mysqld_safe on windows, I figured mysqld can be run by itself like so:

mysqld --skip-grant-tables

The other steps should remain same (changing password etc). I had mysql 5 installed as a windows service, so I did the following steps:

1. stopped service
2. mysqld --skip-grant-tables
3. changed password using procedure given.
4. restarted service

That was all.
Vivek Deveshwar

[ Parent | Reply to this comment ]

Posted by Anonymous (59.96.xx.xx) on Wed 12 Mar 2008 at 10:46
Good article for resetting password.
A small addup for this comment.
Some of the mysql versions may not have mysqld.exe you can make use of mysqld-nt.exe

Rest of the procedure is same.

Regards,
Kishore.

[ Parent | Reply to this comment ]

Posted by Anonymous (83.131.xx.xx) on Wed 8 Nov 2006 at 23:06
exelent

[ Parent | Reply to this comment ]

Posted by Anonymous (200.47.xx.xx) on Tue 17 Jul 2007 at 16:50
This explanation es wonderful... I was searching about this problem in many pages but you have helped me a lot with to much precision...

Muchas gracias,

Ruby Ortiz
From Colombia

[ Parent | Reply to this comment ]

Posted by Anonymous (75.13.xx.xx) on Sat 22 Sep 2007 at 00:51
Tho im running fedora it workd the same thanx bunches

[ Parent | Reply to this comment ]

Posted by Anonymous (62.136.xx.xx) on Sun 18 Nov 2007 at 14:47
Fantastic - this was the most helpful guide I found to doing this - sorted in a jiffy!

[ Parent | Reply to this comment ]

Posted by Anonymous (208.50.xx.xx) on Wed 26 Mar 2008 at 13:31
AHH! The script appears to be missing. Can it be found anywhere else?

Thanks!

[ Parent | Reply to this comment ]

Posted by Anonymous (193.137.xx.xx) on Tue 3 Jun 2008 at 19:20
# dpkg -l mysql-server* | grep ii
ii  mysql-server                        5.0.51a-3                     MySQL database server (meta package depending on the latest version)
ii  mysql-server-5.0                    5.0.51a-3                     MySQL database server binaries


# dpkg-reconfigure mysql-server-5.0
Stopping MySQL database server: mysqld.
[debconf screen appears]

 While not mandatory, it is highly       
 recommended that you set a password     
 for the MySQL administrative "root"     
 user.                                   
                                         
 If that field is left blank, the        
 password will not be changed.           
                                         
 New password for the MySQL "root"       
 user:                                   
 _______________________________________ 
                                         
                 <Ok>                    


Stopping MySQL database server: mysqld.
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

...et voilà, it seems :)

[ Parent | Reply to this comment ]

Posted by amluidgi (41.204.xx.xx) on Fri 13 Jun 2014 at 13:23
When I tried to apply the dpkg-reconfigure procedure, it issued this:

# dpkg-reconfigure mysql-server-5.5
[FAIL] Stopping MySQL database server: mysqld failed!
invoke-rc.d: initscript mysql, action "stop" failed.
I cannot stop it!
Why? Is there any other way to stopping this database?

Thanks

[ Parent | Reply to this comment ]

Posted by websissy (75.161.xx.xx) on Wed 23 Jul 2008 at 23:42
Steve, you did an EXCEPTIONAL job on this procedure. My hat's off to you!

I struggled and fought for hours trying to set the root MYSQL password on my new server and failed repeatedly to make the procedure work. I was very careful to follow the proceudre I was using step-by-step and double- and triple-checked every minute detail; but I still I failed to get the password changed and I've done this BEFORE... It's NOT new to me. The only thing that WAS new to me was doing it on Debian and on MySql5 -- not RH and MySQL 3.23.54

Finally, after at least 2 dozen failures and unable to figure out why, I tossed the procedure I was using in the trash and went looking for a new one. That's when I found this well written tutorial.

Presto! Within 5 minutes I had the new password installed and had change it just to be sure. Hurray!!

Thanks, a lot, Steve. GREAT job.

[ Parent | Reply to this comment ]

Posted by Anonymous (203.199.xx.xx) on Wed 15 Oct 2008 at 07:57
Hi , This is a great post.It worked very well for me.Thanks a lot !

[ Parent | Reply to this comment ]

Posted by Anonymous (75.149.xx.xx) on Tue 2 Dec 2008 at 19:01
Perfect thanks alot :)

[ Parent | Reply to this comment ]

Posted by Anonymous (202.88.xx.xx) on Thu 2 Apr 2009 at 16:51
Simply Super . :)

[ Parent | Reply to this comment ]

Posted by Anonymous (156.56.xx.xx) on Wed 8 Apr 2009 at 21:39
The process listed here should only be used prior to 5.0. Starting with 5.0, you can create an SQL script to be run at start-up. This technique can be used to run an SQL script that will change the root password. This is standard MySQL technique.

However for Debian things get easier. First, become root. Then run:
mysql --defaults-file=/etc/mysql/debian.cnf

Now use the standard command to change the root password. You're logged in with an account with the capacity to change it. This config file is used by Debian's /etc/init.d logic for administrative purposes.

For recent docs from MySQL on fixing the root password, see http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html

[ Parent | Reply to this comment ]

Posted by Anonymous (71.245.xx.xx) on Thu 16 Apr 2009 at 21:17
Thanks! Worked like a charm.

[ Parent | Reply to this comment ]

Posted by Anonymous (125.165.xx.xx) on Wed 4 Nov 2009 at 18:00
Thats really help me !, thanks..

[ Parent | Reply to this comment ]

Posted by Anonymous (66.63.xx.xx) on Mon 1 Mar 2010 at 18:36
Not to add to the flames, but, the default binary build is as-is.
If you don't agree with the policy, join the MySQL Developer community and get it changed.
Or better yet, build MySQL yourself with out the options that allow it to be started in this manner.

[ Parent | Reply to this comment ]

Posted by Anonymous (173.19.xx.xx) on Fri 5 Mar 2010 at 23:13
Thanks, this made my day :)

[ Parent | Reply to this comment ]

Posted by Anonymous (71.29.xx.xx) on Wed 17 Mar 2010 at 16:29
Thanks a lot for this resource, I was having a hard time figuring out from other sites on how to reset the mysql password. Thanks again...

[ Parent | Reply to this comment ]

Posted by tttony (190.204.xx.xx) on Sun 25 Apr 2010 at 00:45
[ View Weblogs ]
wow thanks for the tips... I have learned more reading this post than ebooks/wikis/sites together xDDD

[ Parent | Reply to this comment ]

Posted by Anonymous (195.188.xx.xx) on Thu 5 Aug 2010 at 13:30
This worked for me, i am really grateful you got me out of a BIG hole man!

[ Parent | Reply to this comment ]

Posted by Anonymous (208.92.xx.xx) on Sat 14 Aug 2010 at 02:31
This worked perfectly for me on CentOS 5.x 64 bit. Thanks.

Matthew

[ Parent | Reply to this comment ]

Posted by Anonymous (146.231.xx.xx) on Fri 20 May 2011 at 21:55
This is really helpful. I was stuck for the whole day and managed to reset the password following this tutorial. Thanks

[ Parent | Reply to this comment ]

Posted by Anonymous (122.166.xx.xx) on Sat 8 Oct 2011 at 07:41
Saved the day :)

[ Parent | Reply to this comment ]

Posted by amluidgi (41.204.xx.xx) on Fri 13 Jun 2014 at 13:00
Hello!
But at this : "mysql stop" level,
I am already asked to enter the (forgotten) root password, when I tried to stop mysql!!
Isn't this a kind of looping problem?


[ Parent | Reply to this comment ]

Sign In

Username:

Password:

[Register|Advanced]

 

Flattr

 

Current Poll

What do you use for configuration management?








( 462 votes ~ 5 comments )