Skip to main content
BlogDatabasesGalera Database Reference Architecture for MySQL and MariaDB

Galera Database Reference Architecture for MySQL and MariaDB

Designing-Database-Reference-Architecture-with-Galera-for-MySQL-MariaDB

It’s a best practice to build a plan before you develop your application. Start by defining the primary business goals, collecting requirements, evaluating a potential technology stack, and understanding integrations. At the end of the process, you should have an outline of your app’s reference architecture, which acts as a blueprint for implementing the components of your infrastructure and defines how data flows between those components.

In this example, we’re reviewing a database design using Galera for MySQL and MariaDB. Databases are mission critical components of many applications. This valuable asset relies on redundancy to keep your workload alive. A single point of failure can negatively impact business operations, uptime agreements, and the overall user experience. 

Using reference architectures as a guide, you can build resilient database and application deployments that scale horizontally (without downtime) as your business grows.

What is Reference Architecture?

Reference architectures are reusable technical diagrams that incorporate common design principles and industry best practices for implementing solutions. A highly-abstracted cloud reference architecture depicts connections between the various compute instances, load balancers, storage, software defined networks and so on — the value of your databases makes it a key area of focus here.

Databases & Reference Architecture

The primary goal is to add redundancy and prevent the database from being a single point of failure. There are different ways to achieve this goal depending on application and workload constraints. For example, an application might perform better by splitting reads and writes in a primary-secondary replication model. A need for scaling write operations would require a strategy with multiple primaries.

Other considerations include the volume of data, ACID compliance, failover/election process (manual or automatic), expected number of client connections, and the chosen database technology. These considerations will shape the database solution and form the larger picture of how these pieces work together in your reference architecture.

When to Use Galera

Galera is a free and open source high availability database solution that is simple to manage and doesn’t depend on a single cloud provider. If your database technology is MySQL, or one of its forks like MariaDB or Percona, we highly recommend Galera as a durable and portable solution.

Galera Cluster Advantages

  • Node Failures Prevents split-brain conditions by invoking a weighted quorum to elect a primary component in the event of node failures.
  • Multi-Primary, Active-Active Cluster – Read and write to any node in the cluster.
  • Data Consistency – Uses certification-based synchronous replication to guarantee ACID compliance.
  • Automatic Node Provisioning – When failed nodes recover, or when new nodes join the cluster, they are automatically brought into sync with the state of the primary component using State Snapshot Transfers (SST) or Incremental State Transfers (IST).

Galera Cluster Limitations

  • No MyISAM support- Only supports the InnoDB engine. Writes to other table types, including system tables, are not replicated.
  • Table Formatting- Tables without primary keys cannot be deleted or properly replicated.
  • Logic Consideration- Application logic might need to be rewritten to avoid the use of explicit table locking.
  • Write Scalability- All nodes must certify they can write before committing, therefore write performance decreases as the cluster grows. We recommend keeping the cluster size to three nodes, enough to have quorum and optimal write performance

Eliminate Single Points of Failure

Let’s use the example of a traditional LEMP stack, where the application software and database have been separated into different compute instances in preparation for future scale. That setup might look like this:

Diagram: An application server and MySQL database server are securely connected using a VLAN.

The separation of concerns is a best practice, and certainly helps support scalability; but the lack of redundancy makes both database and application servers a single point of failure. We can improve on this structure to eliminate those single points of failure and build the overall reference architecture.

Simple Galera Cluster Setup with VLAN & Cloud Firewalls

A Galera cluster increases the database layer to three nodes with synchronous replication between them. We can use a Linode VLAN to isolate replication traffic from public and shared private networks, and a Cloud Firewall to restrict access from outside of the VLAN. The Galera nodes share a floating IP address so that if one fails, another is able to take over serving requests to the unknowing application server.

Diagram: An application server points to a floating IP, which connects to a MySQL Galera database cluster that provides synchronous replication for the production database. All components are contained within a VLAN.

The database layer is now highly available. The next thing to focus on is addressing the application server. That process is straightforward for stateless applications, but additional consideration needs to be taken for apps that are stateful. Depending on how the state is maintained, you might need to refactor code and/or implement file replication with tools like Unison or Gluster. For this example, assume, except for temporary session files, the application state is maintained by the database.

Load Balancing & Application Replication

Load balancing gives your application high availability and horizontally scalability, but a single load balancer instance also creates a single point of failure. Redundancy remains a critical component of this implementation. Linode NodeBalancers provide a highly-available, out-of-the-box solution to facilitate session stickiness, health monitoring, and distribution of client requests to a set of backend application nodes.

Diagram: A load balancer distributes traffic between two application servers, which both connect to the Galera cluster for the production database. Three replications are shown. All components are contained within the same VLAN.

If one application server goes down, the NodeBalancer will begin only directing traffic to the healthy node. As soon as the unhealthy node recovers it will resume balancing connections as before. This makes it easy to add, remove, or update application servers without downtime, and because of the active-active solution provided by Galera, any application node can read/write to any database node.

Linode’s Solutions Engineering team shares frameworks, guides, and tools like this one to develop apps with best practices in mind. Read more about the high availability benefits provided by Galera clusters. If you’re ready to start building this on Linode, check out our guide to setting up MariaDB Clusters with Galeria, Debian, and Ubuntu.


Comments

Leave a Reply

Your email address will not be published.