MySQL recovery

All it takes is just one interruption while writing that UPDATE line for a password, leaving off the critical WHERE clause, and suddenly all of your users are locked out.  Or some other minor error that is inadvertently made.  Whatever the cause, you are left with a mysql server that has all of your data, but it cannot serve it because all of the passwords are gone.

Admit it.  You now have an outage that is likely your fault.  Oh, well.  No need to fret over what has happened, but you need to get out of this hole as quickly as possible.

Here is a sample backup script that will create an on-line archive of separate files of all of the databases, in separate files, within a directory tree, for easy parsing and recovery: mysql_backup.pl. It can certainly be modified to use more perl modules, but it works as is, too.

1. Communicate

People are going to quickly realize that either they do not have access to their databases or that, as users, they do not have access to their web applications.  While you may be able to pound out 145 words per minute in your command-line skills and may also secretly hope to recover without having to admit your embarrassing mistake, don’t waste your time.

Focus on getting the problem resolved. This cannot be stressed enough.

Send out a quick email, something that can be done in 30 seconds or less with your 145 words per minute skill, making everyone aware of the problem.

If you don’t, here is a summary of how it will play out:

• You will have random people stopping by to tell you all about the problem typically starting with the phrase “I don’t know if you know about this or not..” or “I just wanted to let you know..” or “Hey, do you know when this will be fixed?” or any number of similar iterations.
• These interruptions will increase in frequency the longer the outage.
• As the number of interruptions increase, your stress level will increase.
• You will attempt to work more feverishly to resolve the problem faster.
• You will become more curt with the people reporting the problem (remember, these are your customers, and they are trying to be helpful in their own way).
• Word will trickle upwards to management and possibly senior management.  A perception, one not based upon reality, will begin to take hold about you and your skills.  Remember, “Perception IS Reality”.

It is important to point out that this type of visibility to your users and your management is a BAD THING.. for you.  While an outage is generally viewed as a bad thing, the perception that is being created by the lack of communication is worse.

2. Stop mysql

Bring down the database server.  /etc/init.d/mysqld stop

3. Start mysql in “safe” mode

It sounds “windows-ish”, but it is true.  Mysql can be started up without reading its grant tables, which gives the administrator the ability to connect in to the database without a password.  No other users can connect at this time.

mysqld_safe --skip-grant-tables &

4. Find your backups

Hopefully, you take nightly backups of your databases.  If not, this task will be harder.

4a. You want to get a copy of the backup of your ‘mysql’ database.  In particular, you want the ‘user’ table.  Place the file in /tmp or some place you can freely work.

4b. If you do not have a backup to restore from, you will need to locate all of the applications that rely on databases hosted by your mysql server and collect all of the user names and passwords.

5. Restore your ‘user’ table

If you have the ‘user’ table from the last backup, you will be simply restoring it over top of the current one.

mysql < /tmp/usertable.sql

After which, you will need to drop any users that were created between the time of the last backup and the time of this restore.  Within mysql execute for each user:

drop user newuser@localhost;
flush privileges;
Otherwise, you will need to manually update each row in the user table accordingly.

6. Restart mysql

Now that your mysql ‘user’ table is restored back to what it was from your previous backup, restart mysql:

/etc/init.d/mysqld restart

Congratulations!  Your users and their web apps now have access again.  You will need to add any users that were added after the time of the most recent backup.