Point-in-time recovery of MySQL with binlog and blobs: Not possible?

How can I make point in time recovery of a mysql database? Easy! Do regulary well flushed snapshot copies of the mysql file system, and keep the binary transaction log. Then all you have to do is to recover from tape, and then spool from the snapshot to the point in time that you want, using the mysqlbinlog tool. mysqlbinlog takes the binary log and turns it into nice ascii SQL code

Now, what if you have objects in your database that is not clear text? Binary objects like for example images or, hashed or encrypted data. And yes, that is more common than you would like to think.

Some years ago, we found out the hard way that mysqlbinlog can not handle this at all. The binary data is dumped as exactly that, which renders the produced SQL code useless. The fix is of course to add –hex-blob to the tool, as mysqldump has had for ages. I found later that some had already reported this, but the MySQL developers does not seem to respond to this idea.

Just a thought: The replication code in mysql can already spool the binary log from one point to another through a master server. Would it be possible to reuse parts of this code for point in time recovery too?

One Response to “Point-in-time recovery of MySQL with binlog and blobs: Not possible?”

  1. Twitter…

    This post was mentioned on Twitter by Richard Hawkesford….

Leave a Reply