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.
Cool! (and nasty)
The fifth solution you speak of is what Darren’s original article describes as point 3.
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/.
@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
@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.
@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!
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.
@Ronald: Oh indeed. I’m sorry – couldn’t open Darren’s article and was confused by “…instead of two”.
@Aleksandr & @strcmp, Both the ways are well thought 🙂
@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.
If you’re trying on wamp/windows, see this: http://www.staenzwebsolutions.com/reset-phpmyadmin-mysql-wampserver/569/
[…] are even more solutions! Please refer to a past blog post of mine. Make sure to check out comment #4 by […]