MYSQL 5.6 : Database Loss After Several Power Outttage

innodbMySQL

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)

Best Answer

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.

Related Solutions

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Best Answer

Related Solutions

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Related Question