Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 17, 2004

Restoring lost data from the Binary Update Log

By Ian Gilfillan

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
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.

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM