Data lost in MySQL DB when roll back happened after system restart [closed]

Question

Few days back due to electricity outage, client's system got restarted, resulted in DB rollback.

But due to this roll back, we lost around 2k existing records from various tables.( which were already present in DB).

It is like DB's state shifted to some previous days state.(around 2 -3 days back).

What could be the reason ? No error seen in the logs:

2015-01-13 11:16:41 1664 [Note] InnoDB: The InnoDB memory heap is disabled

2015-01-13 11:16:41 1664 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

2015-01-13 11:16:41 1664 [Note] InnoDB: Compressed tables use zlib 1.2.3

2015-01-13 11:16:41 1664 [Note] InnoDB: Not using CPU crc32 instructions

2015-01-13 11:16:42 1664 [Note] InnoDB: Initializing buffer pool, size = 101.0M 2015-01-13 11:16:42 1664 [Note] InnoDB: Completed initialization of buffer pool

2015-01-13 11:16:42 1664 [Note] InnoDB: Highest supported file format is Barracuda.

2015-01-13 11:16:42 1664 [Note] InnoDB: The log sequence numbers 145957754 and 145957754 in ibdata files do not match the log sequence number 146939560 in the ib_logfiles!

2015-01-13 11:16:42 1664 [Note] InnoDB: Database was not shutdown normally!

2015-01-13 11:16:42 1664 [Note] InnoDB: Starting crash recovery.

2015-01-13 11:16:42 1664 [Note] InnoDB: Reading tablespace information from the .ibd files...

2015-01-13 11:17:10 1664 [Note] InnoDB: Restoring possible half-written data pages

2015-01-13 11:17:10 1664 [Note] InnoDB: from the doublewrite buffer... 2015-01-13 11:17:28 1664 [Note] InnoDB: 128 rollback segment(s) are active.

2015-01-13 11:17:29 1664 [Note] InnoDB: Waiting for purge to start 2015-01-13 11:17:29 1664 [Note] InnoDB: 5.6.13 started; log sequence number 146939560

2015-01-13 11:17:30 1664 [Note] Server hostname (bind-address): '*'; port: 3306 2015-01-13 11:17:30 1664 [Note] IPv6 is available. 2015-01-13 11:17:30 1664 [Note] - '::' resolves to '::'; 2015-01-13 11:17:30 1664 [Note] Server socket created on IP: '::'. 2015-01-13 11:17:35 1664 [Note] Event Scheduler: Loaded 0 events

2015-01-13 11:17:35 1664 [Note] C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld: ready for connections.

Version: '5.6.13' socket: '' port: 3306 MySQL Community Server (GPL)

2015-01-13 11:18:21 1664 [Note] C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld: Normal shutdown

Environment : Windows 7 + Apache Tomcat 7.0 + Mysql 5.6 server + Apache Active MQ

Answer 1

I doubt any of the SQL engines is rolling back the updates for days. Usually it's a couple of last transactions. It can roll back a couple of days only in one case - you have like 0.5 transactions per day, and you were lucky enough to get the power outage in the exact moment the transaction was happening.

Even if I'm mistaken, besides adding an UPS (the obvious choice) you could also add a slave/standby/replica - whatever this is called in your SQL engine. Even the autostarting power generator can be added.

You can also use snapshots. Like ZFS snapshots (not the windows choice though, but everything you mentioned sounds really alien to Windows, so I don't know what made you using Windows for this, actually) or the snapshots provided by your SAN. If you haven't any, you definitely should get the thing that does them. Or decide - may be your data isn't worth it (it's not sarcasm, there's plenty of data that just isn't that vital).

Answer 2

I would suggest adding a UPS, and then shutting your services down cleanly based on the "no power" signal.