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.
@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 🙂