How Logs Work On MySQL With InnoDB Tables - What Happens With Commits? (Page 3 of 4 )
At COMMIT time, InnoDB will NOT write all dirty data pages to disk. I emphasize that because it's easy to think that committing changes means writing everything on a persistent medium. Well, the InnoDB folks are smarter than that. They realize that only the log records need writing. The writing of dirty data pages can happen at overflow or checkpoint time, because their contents are redundant. If the log survives a crash, remaking of data pages is possible using the information in the log records.
So InnoDB should only write the log. Or to be exact, InnoDB should write log records, until it has written all log records which apply to the transaction that's committing. Since all log writing is serial, that means InnoDB must write log records for other transactions too, but that's okay.
Here I must get critical, because that's not what InnoDB does, necessarily. If MySQL's my.cnf's innodb_flush_log_at_trx_commit switch is zero, then InnoDB will avoid log writing at commit time. This means that a successful COMMIT won't "guarantee" that all data changes have become persistent, which is what the ANSI/ISO standard demands. The persistence guarantee applies only for checkpoints.
Anyway, you can set innodb_flush_log_at_trx_commit to one. In that case all's well, InnoDB will write the log. Also InnoDB will flush.
I'd better explain what flushing is, eh? Usually it's enough to merely write, but all modern operating systems will cache writes for efficiency reasons. To get the "guarantee" InnoDB must insist to the operating system "I really mean write, I want that disk write head on a platter, don't return till the physical operation is complete." This means that on a Windows system InnoDB calls the Windows-API function FlushFilBuffers, a call meaning "flush the cache." Here InnoDB parts company with Microsoft: SQL Server 2000 would use a "write through" option during the write, rather than flushing after the write.
Recovery We come now to the point that makes all the logging trouble worthwhile: if a crash happens, you can recover your data.
For a crash that didn't melt the disk drive, recovery is automatic. InnoDB reads the last checkpoint log record, sees if the "dirty pages" were written before the crash, and (if they weren't) reads the log records which affected the page and applies them. This is called "rolling forward" and it's easy for two reasons: (1) because the LSN is there so InnoDB just has to compare numbers to get into synch, (2) because I left out a few details.
Fine. Now, what about a crash that did melt the disk drive? Then the recovery scenario depends on your preparation.
Scenario one: the log is gone. Well, you should have prepared by having a log copy on a separate drive. InnoDB has no explicit option but there are operating-system-specific ways.
Scenario two: the database is gone and the log is overwritten. Well, you should have anticipated that with circular logging, log record #5 will overwrite log record #1. Remember? Therefore if you didn't take a backup after the writing of log record #1, you've lost data.
Scenario three: the database is gone and the log is okay. In that case, congratulations. You just have to restore your last backup copy, and roll the whole log forward. There would be complications if you had to back up the log itself several times since the last full backup ("archive logging"), but I've assumed that option is currently inoperative. By the way, I am not discussing the role of MySQL's binlog. It's essential to the process but isn't part of InnoDB, so out of scope.