High Performance MySQL [Electronic resources] نسخه متنی

8.4 High Availability

So far we've
concerned ourselves with the slaves. Using a proper heartbeat setup
and load balancer, you can achieve a high degree of availability and
transparency for MySQL-based applications. In its current state,
MySQL doesn't offer a lot in the way of high
availability support on the master, but that doesn't
mean all hope is lost.

In this section, we'll look at several
high-availability solutions (both commercial and free). Each of the
options considered has pros and cons, which we've
done our best to document.

8.4.1 Dual-Master Replication

We looked at
dual-master replication back in Chapter 7.
While it doesn't help in scaling an application
(both servers must handle the full write load), you can achieve much
improved availability and transparency by putting a load balancer in
the mix. Figure 8-5 illustrates this arrangement.

Figure 8-5. Dual-master replication for high availability

Aside from the downsides mentioned in Chapter 7
(mostly a lack of conflict resolution), there isn't
a lot that can go wrong with this setup. The worst problem is the
potential for data loss, but that's really no
different from master/slave replication. After a query writes a
record to master 1, MySQL records the query in
the binary log, and the other master has a chance to read it. If
master 1 happens to crash between the time that
the record is written and when the binary log is updated, however,
the other master (and any slaves) will never know about the query. As
far as master 2 is concerned, the query never
happened. The solution would be for MySQL to provide synchronous
replication with two-phase commit, but it doesn't.

On the plus side, this solution is relatively easy to set up and
understand. If you already know how to configure replication and have
a working load balancer set up with good health checks, dual-master
replication isn't much extra work. If you need to
perform maintenance on the masters, you can simply take
master 1 offline, do the work, bring it back
online, and repeat the process on the other as soon as the first has
caught up. Of course, it's best to do this
gracefully. Set the health check to fail, and wait until clients are
no longer accessing the master before shutting it down. Otherwise you
risk interrupting in-progress transactions.

If your load balancer is sophisticated enough, you can virtually
eliminate the problem of conflict resolution. Here's
how it works: rather than having both masters active, configure the
load balancer so that master 1 is active, and
master 2 is on standby. Only when
master 1 goes down should the load balancer send
any traffic to master 2. Most load balancers
provide a mechanism for doing this.

However, a wrinkle occurs when master 1 comes
back online. What should the load balancer do? If it begins sending
connections to master 1 again,
you'll have a situation in which writes could be
occurring to both masters at the same time. That's a
recipe for conflict. Remember, MySQL connections can be long-running,
so the load balancer can't assume that clients will
suddenly disconnect from master 2. The load
balancer needs to be configured so that the notion of the
"live master" changes only when the
current live master goes down.

8.4.2 Shared Storage with Standby

By increasing the cost
and complexity of your infrastructure, you can eliminate the problem
of lost updates described previously. Instead of two servers with
their own copies of the data using replication to stay in sync, you
can configure the active and standby masters to use shared
storage.^[3] It's very important to realize that the
standby master shouldn't mount the filesystem or
start MySQL until the first is offline.

^[3] The exact type of shared storage
isn't terribly important. You see greater
performance from directly attached systems than network attached
storage, however, due mainly to the reduced latency.

Figure 8-6 shows one implementation of shared
storage. It's worth pointing out that a load
balancer isn't strictly necessary in this scenario.
All you really need is an agent running on each node to monitor the
other. If the agent running on master 2 finds
that master 1 is unavailable, it takes over
master 1's
IP address and starts up MySQL with an identical configuration (same
data directory, log filenames, etc.). If the configuration is truly
identical, starting up MySQL on master
2 is logically no different from fixing
master 1 and bringing MySQL up there. However,
in reality there is an important difference: time. Master
2 is already booted and ready to go. Starting up MySQL
takes a matter of seconds. The only delay is imposed by consistency
checks on the data. Shared storage means the possibility of share
corruption if you're not using InnoDB or BDB tables.

Figure 8-6. A live master and a warm standby master using shared storage

Writing such an agent is a tricky undertaking. We
don't recommend you try it unless you have a lot of
time available for testing all the possible edge cases
you're likely to encounter with flaky network
equipment. Instead, spend some time looking at existing tools. There
are numerous open source projects that can be adapted to do this for
MySQL. The best candidate is
keepalived
(http://keepalived.sourceforge.net/), a
keep-alive health check facility written to work in conjunction with
LVS. There are also two commercial solutions on the market today,
described in the next section.

8.4.3 Commercial Solutions

As of this writing, there are two
commercial products worthy of consideration for high availability.
Each takes a completely different approach to solving the problem, so
different sites may find one or the other suitable, or neither. Keep
an eye on this market: we expect to see a lot of new development in
this area in the next year or so.

8.4.3.1 Veritas cluster a gent

Veritas
has a well established reputation for providing the technology
necessary to build many sorts of clusters. Their MySQL offering
builds on the shared storage with standby model we just looked at.
The cluster agent runs on both the active and standby nodes,
monitoring the health of the primary master. When the agent detects a
problem on the master, it brings the standby instance online and
takes over the primary master's functionality.

8.4.3.2 EMIC Networks

EMIC Networks provides a full-blown
clustering solution for MySQL. By combining a number of relatively
inexpensive servers running EMIC's version of MySQL,
you can create incredibly robust MySQL clusters without needing to
worry about the single point of failure most other architectures
have.