MySQL High Availability Solutions
An Overview of MySQL High Availability Solutions
A MySQL
®
Technical White Paper
August 2006
Copyright © 2006, MySQL AB
Table of Contents
1 Executive Summary..............................................................................................................3
2 High Availability Solutions from MySQL ............................................................................3
2.1 MySQL Replication ..........................................................................................................................................3
2.2 MySQL Replication for Disaster Recovery ......................................................................................................7
2.3 MySQL Cluster.................................................................................................................................................8
2.4 MySQL Cluster and Replication.....................................................................................................................12
3 Third-Party High Availability Solutions for MySQL .........................................................13
3.1 Linux Heartbeat & MySQL Replication ..........................................................................................................13
3.2 Linux Heartbeat, Block-Replication & MySQL ...............................................................................................15
3.3 Load Balancing with MySQL Replication.......................................................................................................17
3.4 MySQL with Shared Storage and Clustering Agents.....................................................................................19
3.5 High Availability/Performance Networking – Dolphin SCI Interconnect ........................................................20
3.6 Operating System Clustering Solutions.........................................................................................................21
3.7 High Availability Middleware - Continuent m/Cluster.....................................................................................21
3.8 High Availability Middleware – High-Availability RSF-1.................................................................................21
4 Implementing a MySQL High Availability Solution..........................................................22
4.1 MySQL Professional Services .......................................................................................................................22
4.2 MySQL Certified Partners and Products........................................................................................................23
4.3 MySQL Training.............................................................................................................................................23
4.4 MySQL Network.............................................................................................................................................23
5 Why MySQL?.......................................................................................................................24
5.1 Scalability and Flexibility................................................................................................................................24
5.2 High Performance..........................................................................................................................................24
5.3 High Availability..............................................................................................................................................24
5.4 Robust Transactional Support .......................................................................................................................24
5.5 Web and Data Warehouse Strengths............................................................................................................24
5.6 Strong Data Protection...................................................................................................................................24
5.7 Comprehensive Application Development.....................................................................................................25
5.8 Management Ease.........................................................................................................................................25
5.9 Open Source Freedom and 24 x 7 Support...................................................................................................25
5.10 Lowest Total Cost of Ownership................................................................................................................25
6 Conclusion..........................................................................................................................26
7 About MySQL......................................................................................................................26
8 Resources ...........................................................................................................................26
8.1 White Papers .................................................................................................................................................26
8.2 Case Studies..................................................................................................................................................26
8.3 Press Releases, News and Events................................................................................................................26
8.4 Live Webinars ................................................................................................................................................26
8.5 Webinars on Demand ....................................................................................................................................26
Copyright © 2006, MySQL AB Page 2 of 26
1 Executive Summary
In today’s business world, information and the access to it, forms not only the infrastructure, but often the
entire revenue model for some organizations. Therefore, maintaining the availability and access to this
information has become increasingly more important and common place for businesses big and small.
High availability has traditionally been the domain of mission and business critical systems such as,
applications, databases and storage networks. However, less critical systems are beginning to take
advantage of the many low-cost, high availability solutions currently available on the market.
The demands placed on these information systems include not only ensuring the availability of important
data, but also the efficient sharing of resources and the existing computing infrastructure. A high
availability solution should deliver the greatest amount of data and application availability across the
diverse technology stacks currently found in many modern enterprises. These technology stacks include
various operating systems, applications, hardware components, and can often span multiple geographic
locations. More often then not, it is a database that sits behind many of these critical applications and
information assets. In “A Guide to Database High Availability” of this white paper series, we introduced
general database high availability architectures and concepts. We also explored some considerations to
take into account when selecting a high availability database solution. In this paper, we will examine in
greater detail the high availability solutions provided by MySQL, including MySQL Replication and MySQL
Cluster. We will also include an introduction to MySQL Professional Services and the MySQL network of
certified partners and products.
2 High Availability Solutions from MySQL
MySQL offers a wide array of options when selecting a high availability solution. These include MySQL
Replication, MySQL Cluster, free and open-source solutions, as well as, products from our network of
certified partners. In this section we focus on high availability solutions available directly from MySQL.
2.1 MySQL Replication
MySQL natively supports one-way, asynchronous replication. MySQL Replication works by simply having
one server act as a master, while one or more servers act as slaves. This is in contrast to the
synchronous replication which is a characteristic of MySQL Cluster.
Asynchronous data replication means that data is copied from one machine to another, with a resultant
delay. Often this delay is determined by networking bandwidth, resource availability or a predetermined
time interval set by the administrator. However, with the correct components and tuning, replication itself
can appear to be almost instantaneous to most applications. Synchronous data replication implies that
data is committed to one or more machines at the same time, usually via what is commonly known as a
“two-phase commit”.
In standard MySQL Replication, the master server writes updates to its binary log files and maintains an
index of those files in order to keep track of the log rotation. The binary log files serve as a record of
updates to be sent to slave servers. When a slave connects to its master, it determines the last position it
has read in the logs on its last successful update. The slave then receives any updates which have taken
place since that time. The slave subsequently blocks and waits for the master to notify it of new updates.
Below in Figure 1 is an illustration of a basic master to slave MySQL Replication configuration.
Copyright © 2006, MySQL AB Page 3 of 26
Web/App
Server
Web/App
Server
Writes & ReadsWrites & Reads
MySQL Master
I/O
Thread
SQL
Thread
WritesWrites
relay
binlog
MySQL Slave
mysqld
data
index &
binlogs
mysqld
databinlog
Replication
Figure 1
Replication offers the benefits of reliability, performance, and ease of use:
• In the event the master fails, the application can be designed to switch to the slave.
• Better response time for clients can be achieved by splitting the load for processing client queries
between the master and slave servers. Queries which simply “read” data, such as SELECTs, may
be sent to the slave in order to reduce the query processing load on the master. Statements that
modify data should be sent to the master so that the data on the master and slave do not get out
of synch. This load-balancing strategy is effective if non-updating queries dominate. (This is
normally the case.)
• Another benefit of using replication is that database backups can be performed using a slave
server without impacting the resources on the master. The master continues to process updates
while the backup is being made.
In Figure 2 we illustrate the above points.
Copyright © 2006, MySQL AB Page 4 of 26
Master Server Slave Server
Writes
Index &
Bin Log
Rotation
Writes
Reads
Possible Roles
• Fail over server
• Used for performing backups
• Read load balancing
• Additional slaves allow Scale-Out
BackupsBackups
Writes & Reads
Web/App
Server
Web/App
Server
ReplicationReplication
Figure 2
Although MySQL Replication can solve many high availability design issues in a simple and straight
forward manner, two problems inherent to the configuration do need to be addressed by developers
and/or administrators.
First, the application interacting with the databases must become “replication aware”. By this we mean
that the application must be coded to write only to the master and always read from the slave.
The second issue revolves around creating a strategy for initiating a fail over in the event of a hardware or
software problem on the master. Beyond that, there should also be a process for returning the system to
its original configuration before the failure.
Below in Figure 3 we illustrate how a fail over would likely be configured in a standard master to slave
configuration.
Copyright © 2006, MySQL AB Page 5 of 26
