Xaprb

This post is for people who are trying to roll back unwanted modifications to their MySQL database.

You cannot use the binary logs to undo unwanted changes to your data. The binary logs are for redoing statements, not undoing them. If you have a backup, you may be able to restore the backup and then replay binary logs to roll forward to the desired state. But you cannot roll backwards with the binary logs.

I say “may be able to” because depending on how you take the backup, even your backups and binary logs may not be enough to fully recover your data. If you don’t know how to do backups right, my advice is to hire someone who knows. It’s not something you should leave to chance.

I wrote this post because of all the people familiar with other databases, who do not know that their chosen backup strategy leads to a situation where it’s impossible to recover their data. Hopefully Google will lead them to it.

Did you notice that I moved this blog from pair Networks to pairLite hosting?

Probably not, unless you check the DNS of xaprb.com regularly!

Don’t you hate it when people say “I’m moving my blog, I hope there won’t be more than a few days of downtime, blah blah…” Why is this ever necessary, I wonder? I wonder the same thing about a lot of hosting providers — recently I had a client in my consulting practice whose (very large, well-known) hosting provider tried to help them with some very simple MySQL work and ended up causing them an obscene amount of downtime, like many many days, and there was no end in sight. As I spoke on the phone with him and asked him about his business, he said “we have X thousand users in our beta.” long pause. “Well, we did anyway.” The poor man hadn’t slept in I don’t know how long. I could only empathize with what it must have felt like to say those words in that mental and physical state. And as I spoke with him I had to tell him, cringing as I said it, that his downtime was completely unnecessary. His host was utterly ignorant of what they were doing.

Does this ever happen to someone you know? It’s such a shame. I wouldn’t be surprised, really, if this client has a hard time recovering fully from this blow.

This is not to demonize hosting providers. They are often great at hosting. But they are not MySQL experts. (Some of them hire Percona to do their MySQL support, and that is good.) If you need expert MySQL help, hire an expert. We can also tell you what to watch out for on your shared hosting — the hosting providers often don’t understand the hardware requirements for a database server, and we constantly see simple and really bad avoidable mistakes such as a 32-bit OS on 64-bit hardware or a misconfigured RAID controller. Don’t rely on your hosting provider for anything database-related, especially backups.

Similarly, if you need expert hosting, call an expert hosting provider, not someone who’s just reselling. I’ve had such good luck with pair Networks (and now pairLite, their budget service) that I write love letters like this blog post constantly. And I recently switched away from Embarq for my DSL provider, to BRIWorks, a local shop with really friendly, smart people who charge me more money than Embarq, and yet I love them for it. (By the way, they’re not just local; they can help you if you don’t live in Charlottesville. If I wasn’t already a pairLite customer, I’d use them.)

My point? With good hosting, and my skills with MySQL and PHP and Apache, I moved with no downtime. OK, it’s not hard — but neither is a non-corrupt MySQL backup that doesn’t kill your entire business. If you know what you’re doing.

If you don’t know what you’re doing, hire someone who does!

Download Maatkit

Maatkit contains essential command-line utilities for MySQL, such as a table checksum tool and query profiler. It provides missing features such as checking slaves for data consistency, with emphasis on quality and scriptability.

This release contains bug fixes and new features.

Changelog for mk-archiver:

2008-01-05: version 1.0.6

   * Made suffixes for time options optional (bug #1858696).

Changelog for mk-deadlock-logger:

2008-01-05: version 1.0.8

   * Made suffixes for time options optional (bug #1858696).

Changelog for mk-heartbeat:

2008-01-05: version 1.0.6

   * Made suffixes for time options optional (bug #1858696).

Changelog for mk-parallel-dump:

2008-01-05: version 1.0.4

   * Second and later chunks had DROP/CREATE TABLE (bug #1863949).
   * Made suffixes for time options optional (bug #1858696).
   * --locktables didn't disable --flushlock.

Changelog for mk-parallel-restore:

2008-01-05: version 1.0.3

   * Made suffixes for time options optional (bug #1858696).
   * --ignoretables was ignored.

Changelog for mk-slave-delay:

2008-01-05: version 1.0.5

   * Made suffixes for time options optional (bug #1858696).
   * The program was ignoring some connection parameters.
   * Made the program use master when the I/O thread waits for relay log space.

Changelog for mk-slave-restart:

2008-01-05: version 1.0.5

   * Made suffixes for time options optional (bug #1858696).
   * Added logic to discard corrupt relay logs.
   * Added --monitor, --sentinel, and --stop.
   * Added --quiet and changed --verbose to 1 by default.
   * Added the ability to monitor many servers with --recurse.

Changelog for mk-table-checksum:

2008-01-05: version 1.1.24

   * Added support for the FNV_64 UDF, which is distributed with Maatkit.
   * --emptyrepltbl didn't Do The Right Thing by default.
   * --explain didn't disable --emptyrepltbl
   * Made suffixes for time options optional (bug #1858696).
   * The --float-precision option was ignored.
   * (mk-checksum-filter) -i, -d options worked only on multiple files.

Changelog for mk-table-sync:

2008-01-05: version 1.0.3

   * Added the --function command-line option.
   * Added support for the FNV_64 hash function (see mk-table-checksum).
   * Made suffixes for time options optional (bug #1858696).
   * InnoDB tables use --transaction unless it's explicitly specified.