Have just read Darren Cassar's Recovering a MySQL `root` password – Three solutions. There's a fourth solution: using an init-file, which leads to just one restart of the database instead of two. It also avoids the security issue involved with using skip-grant-tables.
I've written all about it before on Dangers of skip-grant-tables.
Darren's 1st advice (look for password ini files, scripts, etc.) is a very good one. One password that can always be looked up in files is the replication's password.
Replication's password is easily forgotten: you only set it once and never use it again; never script it nor manually login with. When setting up new slaves, though, you suddenly need it.
Apparently not many realize that the replication password is written in plaintext in the master.info file. This file tells the slave all about it's master connection: host, port, user & password are all there for you to read.
There is also the fifth way that doesn't require restart at all.
Open user.MYD in vim, replace the password string with 42 spaces(if passwords are new), then killall -1 mysqld.
Voilà!
Just two notes:
1. user.MYD can be fragmented. You have to either find all 42 charachters from the password or defragment the table with myisamchk
2. Table `user` will be corrupted after this password recovery - you need to repair it. REPAIR TABLE will remove the root record from `user`. Add it right after the repair.
Link | March 22nd, 2011 at 2:30 pm
Cool! (and nasty)
Link | March 22nd, 2011 at 3:43 pm
The fifth solution you speak of is what Darren's original article describes as point 3.
Link | March 22nd, 2011 at 10:15 pm
You can copy data/mysql/user.* to another database where you have the UPDATE privilege, set the password to PASSWORD('word') or change the privileges and move the files back to data/mysql/.
Link | March 23rd, 2011 at 12:16 am
@strcmp: if you copy the user.* to another database, this overrides any UPDATE privilege you may have there.
But, yes, with mysqlsandbox, for example, and skip-grant-tables, this is easy enough.
@Ronald,
Ineteresting that I read point #3 as copy+paste tables, and I missed the hash editing. Apparently so has Aleksandr...
Thank you
Link | March 23rd, 2011 at 7:45 am
@shlomi: It took a while to figure out why you think that. user.* is not a magic object but a normal MyISAM table, it is only special at its original place. Of course you shouldn't put it into another mysql schema/database/directory, that would mean overwriting another user list anyway, which is not our goal. But you _can_ put it into another schema on the same database, or into another database, just not into the mysql schema, I have recovered a root account this way some years ago, it definitely works.
Link | March 23rd, 2011 at 8:54 am
@strcmp
Ah! You meant database == schema. Indeed I was under the impression you meant copying it to another database (database == MySQL instance).
I often find the word "database" is overloaded: database can stand for schema, server instance, server type, dataset,...
Hey, this is a really nice solution. It deserves more than a comment on my blog. Thank you!
Link | March 23rd, 2011 at 8:59 am
I had meant both "schema" and "MySQL instance (into another schema)" at once.
Yes, "database" is confusing, I should have used another word.
In my case editing the password field as in the first tips would not have helped anyway, because all of root's grant columns were set to 'N', the password was ok.
Link | March 23rd, 2011 at 9:12 am
@Ronald: Oh indeed. I'm sorry - couldn't open Darren's article and was confused by "...instead of two".
Link | March 23rd, 2011 at 12:11 pm
@Aleksandr & @strcmp, Both the ways are well thought
Link | March 23rd, 2011 at 12:34 pm
@Aleksandr: Sorry I've been having some issues with my hosting provider, hopefully will move soon.
@Shlomi: The copying and pasting of tables is purely backing stuff up in case a rollback is needed. Thanks for a fourth solution btw.
Link | March 23rd, 2011 at 10:00 pm
If you're trying on wamp/windows, see this: http://www.staenzwebsolutions.com/reset-phpmyadmin-mysql-wampserver/569/
Link | February 6th, 2013 at 7:54 pm