Restoring lost data from the Binary Update Log

One quiet Monday morning, I was easing into the week, when some of the staff started reporting ‘strange errors’ in their application (a content management system running on MySQL).

The machine had been having problems with what looked like overheating, so I quickly checked all the tables, and finding some corruption, repaired the tables. I saw that the machine had restarted shortly after the midnight backup, presumably due to overheating (after a backup, the SQL is bzipped, which is quite CPU and drive intensive, which could cause heat problems). Crisis over, I sat back and continued sifting through the hundreds of Monday morning emails.

Lunch came and went, and it looked like being a quiet Monday, when I had another report of ‘strange behaviour’. It was definitely time for moving to that cooler cabinet, but in the meantime I had more corruption to repair. Feeling complacent, I started a REPAIR TABLE. It failed. In horror, I saw that the entire .MYD file (the data file) had disappeared. What was worse, having successfully done this many times recently, I hadn’t backed up before doing the repair. All the data since last night’s backup was gone! After some frantic scrambling around on the filesystem, there was no other conclusion – the data was lost.

Before this horror story scares everyone off MySQL, the real culprit seemed to be memory and motherboard-related, a topic not in my area of expertise. But the problem was now squarely in my domain – all data since last night had been lost.

The Binary Update Log

Fortunately, there was a solution. Some of you may remember a small section in my article entitled More MySQL Logs on the binary update log. The binary update log is a log that records each SQL statement that changes the data stored in the database, which includes all UPDATE and INSERT statements.

The binary update log is activated by placing
log-bin[=binary_update_log_filename]
in the configuration file (my.cnf or my.ini). The filename is optional. If it is not supplied, the binary log will be named after the host (hostname-bin). The first binary log, created when the server is set up, will be given the extension 001. Every time the server is restarted, is flushed (with FLUSH LOGS, mysqladmin flush-logs or mysqladmin refresh), or a single update log becomes too big (determined by the value of max_bin_log_size, which you can set in your config file, the file rolls over, and a new file is created, with an extension incremented by one. So the second binary log file will be called something like hostname-bin.002.

The binary update log can be used to restore lost data (since it records all changes to the data). It is also used for replication, which will not be covered in this article.

Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles