MYSQL 5.6 : Database Loss After Several Power Outttage

Question

I have lost 10 days of uncommitted data - InnoDB. Apart from, my mysql database directory have two more files: **ib_logfile0_2016-06-16T14-56-52 and ib_logfile1_2016-06-16T14-56-52 on top of ib_logfile0 and ib_logfile1 files **. Which is which? Is it possible that my uncommitted records are recoverable in one of these files? What measures can i take to ensure that i do not have many days of uncommitted transactions?

 **Log details:**

2016-09-28 18:12:07 1696 [Note] Plugin 'FEDERATED' is disabled.
2016-09-28 18:12:07 704 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-09-28 18:12:07 1696 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-09-28 18:12:07 1696 [Note] InnoDB: The InnoDB memory heap is disabled
2016-09-28 18:12:07 1696 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-09-28 18:12:07 1696 [Note] InnoDB: Memory barrier is not used
2016-09-28 18:12:07 1696 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-09-28 18:12:07 1696 [Note] InnoDB: Not using CPU crc32 instructions
2016-09-28 18:12:07 1696 [Note] InnoDB: Initializing buffer pool, size = 3.0G
2016-09-28 18:12:07 1696 [Note] InnoDB: Completed initialization of buffer pool
2016-09-28 18:12:07 1696 [Note] InnoDB: Highest supported file format is Barracuda.
2016-09-28 18:12:07 1696 [Note] InnoDB: The log sequence numbers 17708957 and 17708957 in ibdata files do not match the log sequence number 21593264 in the ib_logfiles!
2016-09-28 18:12:07 1696 [Note] InnoDB: Database was not shutdown normally!
2016-09-28 18:12:07 1696 [Note] InnoDB: Starting crash recovery.
2016-09-28 18:12:07 1696 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-09-28 18:12:08 1696 [Note] InnoDB: Restoring possible half-written data pages 
2016-09-28 18:12:08 1696 [Note] InnoDB: from the doublewrite buffer...
2016-09-28 18:12:09 1696 [Note] InnoDB: 128 rollback segment(s) are active.
2016-09-28 18:12:09 1696 [Note] InnoDB: Waiting for purge to start
2016-09-28 18:12:09 1696 [Note] InnoDB: 5.6.24 started; log sequence number 21593264
2016-09-28 18:12:09 1696 [Note] Server hostname (bind-address): '*'; port: 3306
2016-09-28 18:12:09 1696 [Note] IPv6 is available.
2016-09-28 18:12:09 1696 [Note]   - '::' resolves to '::';
2016-09-28 18:12:09 1696 [Note] Server socket created on IP: '::'.
2016-09-28 18:12:09 1696 [Note] Event Scheduler: Loaded 0 events
2016-09-28 18:12:09 1696 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: ready for connections.
Version: '5.6.24-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
2016-09-29 09:00:01 1672 [Note] Plugin 'FEDERATED' is disabled.
2016-09-29 09:00:01 6e8 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-09-29 09:00:01 1672 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-09-29 09:00:01 1672 [Note] InnoDB: The InnoDB memory heap is disabled
2016-09-29 09:00:01 1672 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-09-29 09:00:01 1672 [Note] InnoDB: Memory barrier is not used
2016-09-29 09:00:01 1672 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-09-29 09:00:01 1672 [Note] InnoDB: Not using CPU crc32 instructions
2016-09-29 09:00:01 1672 [Note] InnoDB: Initializing buffer pool, size = 3.0G
2016-09-29 09:00:01 1672 [Note] InnoDB: Completed initialization of buffer pool
2016-09-29 09:00:01 1672 [Note] InnoDB: Highest supported file format is Barracuda.
2016-09-29 09:00:01 1672 [Note] InnoDB: The log sequence numbers 17708957 and 17708957 in ibdata files do not match the log sequence number 21593274 in the ib_logfiles!
2016-09-29 09:00:01 1672 [Note] InnoDB: Database was not shutdown normally!
2016-09-29 09:00:01 1672 [Note] InnoDB: Starting crash recovery.
2016-09-29 09:00:01 1672 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-09-29 09:00:01 1672 [Note] InnoDB: Restoring possible half-written data pages 
2016-09-29 09:00:01 1672 [Note] InnoDB: from the doublewrite buffer...
2016-09-29 09:00:01 1672 [Note] InnoDB: 128 rollback segment(s) are active.
2016-09-29 09:00:01 1672 [Note] InnoDB: Waiting for purge to start
2016-09-29 09:00:01 1672 [Note] InnoDB: 5.6.24 started; log sequence number 21593274
2016-09-29 09:00:01 1672 [Note] Server hostname (bind-address): '*'; port: 3306
2016-09-29 09:00:01 1672 [Note] IPv6 is available.
2016-09-29 09:00:01 1672 [Note]   - '::' resolves to '::';
2016-09-29 09:00:01 1672 [Note] Server socket created on IP: '::'.
2016-09-29 09:00:01 1672 [Note] Event Scheduler: Loaded 0 events
2016-09-29 09:00:01 1672 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: ready for connections.
Version: '5.6.24-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

Answer 1

Uncommitted data is what the name suggest: uncommitted. This means the transaction that was running failed when MySQL crashed. Therefore the data should not be available because it could cause inconsistent state. Any ACID compliant database should roll back these transactions.

Ib_log files do contain uncommitted changes to the tablespace but chances are these are already checkpointed and overwritten many times.

There is a possibility to get back (some) uncommitted data from the ibdata and the .ibd files. For this you can use something like innodb_ruby to read the pages on disk and the history for each row from undo space. Be aware this will be a tedious work though.

If MySQL was started it will do the cleanup and rollback from the undo space which means pages in .ibd files will get back their original (last committed) state and your undo history will be purged. After this all the uncommitted data is lost for good.

ps.: If you really have transactions running for 10 days fix the application first. Of course this depends on the use case but majority of transactions shouldn't run longer than a couple of seconds.