0

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.

  • 8
    This is too broad - we'd need information on your backup systems, servers, HA/DR strategies, etc. You can lose data from any database if you lose power and are not sufficiently prepared. – LowlyDBA Dec 15 '14 at 15:52
  • What I mean is that although you can see your inserted data in the tables InnoDB can roll few days back if the database is closed. – ilhan Dec 15 '14 at 17:06
  • yes, you can lose the data – akuzminsky Dec 15 '14 at 19:41
1

First of all, here is a Pictorial Representation of InnoDB (courtesy of Percona CTO Vadim Tkachenko)

InnoDB Architecture

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

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.

Your Comment

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.

SUMMARY

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.