During a power failure, is it possible to lose data if I use InnoDB? I remember reading such things.
closed as too broad by RLF, Mark Storey-Smith, Shawn Melton, ypercubeᵀᴹ, billinkc Dec 15 '14 at 19:44
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
First of all, here is a Pictorial Representation of InnoDB (courtesy of Percona CTO Vadim Tkachenko)
Strictly in terms of InnoDB, there is only lose data on power failure if certain settings as disabled.
There are two parts of the InnoDB Storage that, if disabled, can cause data loss on power failure.
Double Write Buffer
A record of all data and index pages that are supposed to be written back to the physical files of InnoDB table are written in the Double Write Buffer beforehand. The Double Write Buffer is contained with the system tablespace, better know as ibdata1. That way, in the event of a crash, the InnoDB Storage Engine has something to refer to during crash recovery. To ensure that the Double Write Buffer's pages are there for you after a MySQL crash, always leave innodb_doublewrite set to 1 (default) and do not start mysqld with
Transaction Log Flushing
The Log Buffer is used to collect changes to be written to the Transaciton Log Files (ib_logfile0, ib_logfile1). The default flushing behavior (innodb_flush_log_at_trx_commit = 1) is to flush the contents of the InnoDB log buffer to the log file at each transaction commit and the log file flushed to disk. Some change innodb_flush_log_at_trx_commit to 0 or 2 to increase InnoDB write performance. However, changing innodb_flush_log_at_trx_commit to anything other than 1 makes it possible to lose 1 second's worth of transaction upon an OS crash or power outage.
The only way to see data that is not committed would be to start your transactions with
READ UNCOMMITTED isolation and never run
COMMIT. You should always commit your transactions. Days of rollback info sounds like an abuse of InnoDB, your data, and your application.
Although these are the default settings, you should make sure these settings are in place
[mysqld] innodb_flush_log_at_trx_commit = 1 innodb_doublewrite = 1 auto_commit = 1