Comments on: Recovering a MySQL `root` password: the fourth solution

By: Reset MySQL root password in LAMPP server on Ubuntu | XL-UAT

Reset MySQL root password in LAMPP server on Ubuntu | XL-UAT — Tue, 10 Mar 2015 15:39:49 +0000

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

By: Rômulo Collopy

Rômulo Collopy — Wed, 06 Feb 2013 17:54:09 +0000

If you’re trying on wamp/windows, see this: http://www.staenzwebsolutions.com/reset-phpmyadmin-mysql-wampserver/569/

By: Darren Cassar

Darren Cassar — Wed, 23 Mar 2011 20:00:33 +0000

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

By: Kedar

Kedar — Wed, 23 Mar 2011 10:34:49 +0000

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

By: Aleksandr Kuzminsky

Aleksandr Kuzminsky — Wed, 23 Mar 2011 10:11:05 +0000

@Ronald: Oh indeed. I’m sorry – couldn’t open Darren’s article and was confused by “…instead of two”.

By: strcmp

strcmp — Wed, 23 Mar 2011 07:12:55 +0000

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.

By: shlomi

shlomi — Wed, 23 Mar 2011 06:59:51 +0000

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

By: strcmp

strcmp — Wed, 23 Mar 2011 06:54:20 +0000

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

By: shlomi

shlomi — Wed, 23 Mar 2011 05:45:39 +0000

@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

By: strcmp

strcmp — Tue, 22 Mar 2011 22:16:02 +0000

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