How to design highly available open-source database environments

These days high availability is a must for any serious deployment. Long gone are days when you could schedule a downtime of your database for several hours to perform a maintenance. Making a database environment highly available is one of the highest priorities nowadays alongside data integrity. For a database, which is often considered the single source of truth, compromised data integrity can have catastrophic consequences. This white paper discusses the requirements for high availability in database setups, and how to design the system from the ground up for continuous data integrity.

What we’ll cover in this white paper

• Introduction – couple of words on “High Availability”
• High Availability basics
  • Measuring High Availability
    • What is High Availability?
    • SLA’s
      • Nines
    • Measuring availability
  • Magic number: “three”
  • Single Points of Failure
• How to design your environment for High Availability?
  • Identify Single Points of Failure
  • Decide what availability level you want to achieve
  • Which failures you can tolerate?
    • Overall setup
    • Hardware failures
    • Network failures
    • Proxy layer failures
    • Database tier failures
      • MySQL crash on slave
      • MySQL crash on master
      • Partial data loss
      • Complete data loss
      • Temporary load spike
      • Increased load due to bad query
    • Availability zone or a datacenter failure
    • What issues cannot be tolerated?
  • Remove SPOF’s and reduce the impact of issues with high severity
    • Identify the culprit of the issues
      • Hardware issues
      • Network issues
      • Proxy layer issues
      • Database tier issues
      • Infrastructure issues
    • How to minimize the impact of the issues?
      • Not enough resources to handle failure of a single node
      • Failover is not fast enough
      • No redundancy in the proxy layer
      • Long backup recovery time
      • No redundancy in terms of the infrastructure
  • Design the environment
    • Database tier design
    • Proxy tier design
      • Deploy ProxySQL with Keepalived for VIP failover
      • Deploy ProxySQL on application hosts
      • Synchronization of the ProxySQL configuration
    • Backup redesign
    • Deployment
  • Test your design
• Examples of the highly available setups
  • Single datacenter, replication
  • Single datacenter, Galera cluster
  • Multiple datacenter, replication