1

Everyone says "don't kill MySQL, don't kill MySQL ...".

But if a database gets corrupted when you kill it, how will it react to power loss?

What's a database software that doesn't get corrupted when killed or on power loss?

6
  • And that is why "real" servers use a UPS and either flip over to generator power OR monitor the UPS and when it indicates battery power only they initial a shutdown (of services or just the whole system). As to a db, perhaps a single-file based one like sqlite hsql etc
    – ivanivan
    Feb 24 '17 at 20:21
  • @ivanivan Why single-file? ext3/4 with data=journal correctly handle multiple files; this is just about the database not interleaving transactions or leaving them half-done I guess. Feb 25 '17 at 1:28
  • 1
    Just about any database that isn't MySQL will survive a crash, a power failure, or other unexpected termination. People say "Don't kill MySQL" because MySQL is unusually fragile in this respect.
    – Mark
    Feb 25 '17 at 2:03
  • 1
    @Mark So PostgreSQL is fine for this? I wonder why anyone uses MySQL then ... Feb 25 '17 at 2:23
  • 1
    @Mark: Postgres is definitely not "considerably" slower then MySQL. That is a myth that might have been true 10 years ago (and even back then for highly concurrent read/write workload Postgres was already faster) Feb 28 '17 at 16:14
5

Write-ahead log (WAL)

Any heavy-duty enterprise-quality database uses a write-ahead log to first capture incoming data. Only after the entire chunk of data (the transaction) is captured is it then written to the actual database. If the computer fails, the pending transaction is rolled back with the actual database left intact. If the failure happened after the entire transaction was recorded successfully in the WAL, then upon restart, the database system can roll-forward to attempt writing the data into the actual database.

WAL has other benefits too beside data-protection. You can restore a back-up of a database and then bring it up-to-day by replaying the WAL files collected since the back-up was taken. And WAL files are the basis for replication where you maintain a second installation of your database in near real-time by forwarding a copy of the WAL files from the primary database to the secondary database.

Keep in mind that WAL is not the only aspect of protecting your data. You must also guard against memory corruption (ECC memory) and storage corruption (RAID, ZFS, etc.). And using an uninterruptible power supply (UPS) is always a good idea.

Postgres

enter image description here

Postgres is one of the leading enterprise-quality databases that makes use of WAL to protect your data. Also open-source and free-of-cost – truly so on both counts, with no catches & no fine-print.

Other examples include Microsoft SQL Server and Oracle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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