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