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.
