Recovering a MySQL `root` password: the fourth solution

March 22, 2011

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 file. This file tells the slave all about it's master connection: host, port, user & password are all there for you to read.

tags: , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

13 Comments to "Recovering a MySQL `root` password: the fourth solution"

  1. Aleksandr Kuzminsky wrote:

    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.

    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.

  2. shlomi wrote:

    Cool! (and nasty)

  3. Ronald Bradford wrote:

    The fifth solution you speak of is what Darren's original article describes as point 3.

  4. strcmp wrote:

    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/.

  5. shlomi wrote:

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

    Ineteresting that I read point #3 as copy+paste tables, and I missed the hash editing. Apparently so has Aleksandr...
    Thank you

  6. strcmp wrote:

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

  7. shlomi wrote:

    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!

  8. strcmp wrote:

    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.

  9. Aleksandr Kuzminsky wrote:

    @Ronald: Oh indeed. I'm sorry - couldn't open Darren's article and was confused by "...instead of two".

  10. Kedar wrote:

    @Aleksandr & @strcmp, Both the ways are well thought 🙂

  11. Darren Cassar wrote:

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

  12. Rômulo Collopy wrote:

    If you're trying on wamp/windows, see this:

  13. Reset MySQL root password in LAMPP server on Ubuntu | XL-UAT wrote:

    […] are even more solutions! Please refer to a past blog post of mine. Make sure to check out comment #4 by […]

Leave Your Comment


Powered by Wordpress and MySQL. Theme by