Skip to content

# Recovering a MySQL `root` password: the fourth solution

MySQL Configuration Replication Security 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 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
13
Leave a Reply

13 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
7 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Aleksandr Kuzminsky

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.

Author
shlomi

Cool! (and nasty)

Guest
Ronald Bradford

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

Guest
strcmp

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

Author
shlomi

@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

Guest
strcmp

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

Author
shlomi

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

Guest
strcmp

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.

Guest
Aleksandr Kuzminsky

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

Guest
Kedar

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

Guest
Darren Cassar

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

Guest
Rômulo Collopy

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

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

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

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.