1

I'd like to know if there are certain configurations of Mysql that should be considered to keep the effect of the power failure as low as possible. Which configuration points should be noted and what values should they have.

migrated from stackoverflow.com Jun 8 '17 at 6:49

This question came from our site for professional and enthusiast programmers.

  • 1
    Use database replication to different datacenters. – Barmar Jun 8 '17 at 0:03
  • It an one user application on home pc. – Hisham K Jun 8 '17 at 0:28
  • not sure if I get your question, incase of power failure there shouldn't be much impact to database interms of data once if it is back? – Mannoj Jun 8 '17 at 7:47
  • For home PC - default configuration have good proof level. In any cases - backups must be! must be regular and exactly after any major changes, and must be stored on external drive. – a_vlad Jun 9 '17 at 2:36
  • Use a USV, prevents overvoltage too and backup frequently (full dump daily, incremental and differential more often) to indepentend storage devices. – BitAccesser Jun 15 '17 at 2:57
1

The setup for maximal resiliency from power failure is to have 3 Galera nodes, one in each of three geographically separate locations.

You seem to be using MariaDB, the newer versions of which include Galera Clustering. There is, of course, some setup for clustering and some coding/admin differences than vanilla MariaDB.

When a node (or even the entire datacenter it is in) goes down, the other nodes are available to take reads and writes. The only issue is pointing your clients to them. There are several Proxy servers (etc) that provide this.

3 is a magic number. It allows for a single-point-of-failure, with the other 2 nodes voting to say "we have a quorum" and will stay up. The one that went down might actually be alive, but hidden behind a network outage. To prevent "split brain", that node realizes that id does not have a quorum, so it refuses to take writes, since they might compromise the consistency of unique keys.

For a single 'home' system:

  • Use InnoDB, not MyISAM. InnoDB will recover graceful from power failures.
  • Plug into a UPS. This will give you 'some' time before the power really fails.
  • Use a laptop, not a desktop -- laptops have a battery that will last 'some' time.
  • Hisham K - "It an one user application on home pc.". Excellent recommendation !! :-))) – a_vlad Jun 9 '17 at 2:33
  • OK, i added 3 suggestions. – Rick James Jun 9 '17 at 2:55
0

This setting will provide the best protection from data loss in case of power failure when DB transactions are being performed. I am assuming you are using innodb engine. This will protect committed transactions.

innodb_flush_log_at_trx_commit=1

On the other hand, you mentioned it is your home PC so probably not being used in production. So what are your concerns about data loss? Must be all test data anyway, no?

  • this is default value, so for home installation - do nothing – a_vlad Jun 9 '17 at 5:15

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.