MySQL HA Architecture for Drupal

This is part 2 of the Drupal Meets MySQL series. In the previous post, we touched briefly on the history of MySQL and Drupal working together. In this post, we’ll touch on different architectures using multiple MySQL backends for enterprise Drupal sites.

There are several reasons one may want to run multiple MySQL servers for a single application, including improving high availability and horizontal scalability. Please note that this post isn’t a deep dive into setting up each of these solutions - rather, it highlights how those approaches work with Drupal and some pros/cons.

When looking at multiple MySQL servers with respect to Drupal, there are a few approaches:

Standard MySQL replication and reconfiguration of application after failure

Highly available MySQL instance for Drupal to use for RW traffic

Splitting read/write traffic to scale out read query traffic

The simplest way to achieve HA would be to use standard MySQL replication (master/slave) to keep a hot backup of the active database. In the event of a master failure, you can simply re-point the application to the slave server. While this is very straightforward, the biggest drawbacks are:

Potentially numerous application servers to update

Failover is one-way

Manual process with chance for user error

This is fine for a base level of redundancy and can qualify as “highly available” (depending on your individual requirements), but will likely incur some level of downtime. The next level of redundancy would be replacing the single server model with multiple servers that can be failed to transparently.

HA MySQL Instance

Given the DB layer architecture of Drupal, achieving HA is best achieved by performing failover via the network (e.g. using a Virtual IP). This can be achieved through different approaches, but it satisfies the standard “database” configuration. Using this approach, you will have a highly available MySQL backend that will be transparent to the application.

If you control your own network stack and gear, it is recommended that you use an IP address and then have that IP address managed external to the Drupal application. By using the IP approach, you would simply need to change the resource in the VIP pool if you need to switch servers and the application will not need to be updated.

If you do not control your own network stack (e.g. if you are running a cloud based solution), tune in for post 3 in this series as we discuss some different approaches that companies use to offer HA in the cloud.

For those users not in the cloud, there are a few approaches you can use:

Master/Master with floating IP

keepalived on each MySQL server

virtual IP

M/M behind Load Balancer

HAProxy (or hardware)

failover only pool for MySQL

M/M fronted by multiple Load Balancers

2 HAProxy with keepalived between (VIP)

failover only pool for MySQL

Percona Replication Manager (PRM)

Pacemaker cluster manager

Pacemaker configuration

MySQL resource configuration

Percona XtraDB Cluster (PXC) fronted by HAProxy

1 (or 2) HAProxy

3 node PXC cluster

can be failover only or round robin

The setup of each of these scenarios is beyond the scope of this post, but essentially, they all achieve the same thing: a single IP in front of multiple MySQL instances to handle failures transparently to the Drupal configuration. Keeping the Drupal configuration standard will result in no application changes being needed while giving the database layer redundancy.

The biggest drawback here is the amount of hardware and in some cases, the operational complexity. While these are standard approaches, each require increased knowledge and/or monitoring. As you work your way through the list, you are also potentially adding more hardware (i.e. cost) to the stack.

Read/Write Splitting

While not truly HA, Drupal does offer support for read/write splitting allowing for potential read scale out. With many Drupal applications being very heavily weighted towards read queries over write queries, adding additional read slaves is a good way to scale horizontally. However, you will need to manually add hooks to the sections in the application that you plan to split. Based on experience, different types of applications lend themselves to high read traffic include:

Apps with large number of BI queries

Apps with infrequently changing static datasets

Apps with large and easily isolated datasets (i.e. no joins)

Apps with large number of concurrent users

Apps with massive amounts of distinct types of user generated content

When used in conjunction with the above high availability techniques for the master, you can achieve both read scaling and HA of your write instance. This is the overall goal when looking at both application resiliency and performance.

Other HA Considerations

Keep database traffic local to same data center as the application

Cross WAN traffic is expensive (both from a cost/latency standpoint)

This mainly applies when HA is applied at the DC level too

You can skip replicating temporary tables (Drupal cache, forms, etc) to cut down on replication bandwidth

Note, the app may need to rebuild these things and incur a slower startup

Plan for failure

This is standard, but worth noting

The database (or hardware) WILL FAIL at some point, be prepared

Automated failover is good, but you need to understand it

Sometimes, errant failover is more costly than investigating first

HA does not necessarily provide disaster recovery

A note of caution: running MySQL in an HA fashion (e.g. with multiple MySQL servers or another implementation) will not necessary provide guarantees against corrupt or lost data. Transactions may erase records on all MySQL servers with replication running. Any MySQL highly available solution should also include database backups and testing of the backup procedures.

One thing is certain -- your database is perhaps the most integral aspect of your web application stack and you cannot afford to cut corners. In the next post in this series we’ll discuss how resiliency in MySQL is handled by various cloud providers.