Search code examples
mysqlbackupinnodbsnapshotdatabase-restore

Can server snapshots potentially damage MySQL transactions?


Let's say your taking daily snapshots of your server as a whole (public_html, sql files, etc) for later restoration in case of system failure.

Is it possible that you can restore damaged a MySQL InnoDB if you took the snapshot while an uncommitted transaction was taking place? Or will InnoDB do just fine and discard "incomplete" transactions on restoration?


Solution

  • From the databases viewpoint we are dealing with an unclean shutdown (i.e. power went off) and a lost connection so it will discard all transactions that are not committed.

    If you are taking a snapshot of the server that's just like freezing everything in a cryogenic sleep, then after a restore the database would just awake expecting to talk to a non existing application.

    The only issue that i can see is not from a transaction itself but from the fact that the database itself resides inside files. What if you freeze a file half-written to disk. I can see how that might be a problem. On the other hand there's probably some architectural design in place to prevent this as the same is true for a power outage and a database should live through that too.