Spec-Zone .ru
спецификации, руководства, описания, API

Chapter 15. High Availability and Scalability

Table of Contents

15.1. Oracle VM Template for MySQL Enterprise Edition
15.2. Overview of MySQL with DRBD/Pacemaker/Corosync/Oracle Linux
15.3. Overview of MySQL with Windows Failover Clustering
15.4. Using MySQL within an Amazon EC2 Instance
15.4.1. Setting Up MySQL on an EC2 AMI
15.4.2. EC2 Instance Limitations
15.4.3. Deploying a MySQL Database Using EC2
15.5. Using ZFS Replication
15.5.1. Using ZFS for File System Replication
15.5.2. Configuring MySQL for ZFS Replication
15.5.3. Handling MySQL Recovery with ZFS
15.6. Using MySQL with memcached
15.6.1. Installing memcached
15.6.2. Using memcached
15.6.3. Developing a memcached Application
15.6.4. Getting memcached Statistics
15.6.5. memcached FAQ
15.7. MySQL Proxy
15.7.1. MySQL Proxy Supported Platforms
15.7.2. Installing MySQL Proxy
15.7.3. MySQL Proxy Command Options
15.7.4. MySQL Proxy Scripting
15.7.5. Using MySQL Proxy
15.7.6. MySQL Proxy FAQ

Data is the currency of today's web, mobile, social, enterprise and cloud applications. Ensuring data is always available is a top priority for any organization. Minutes of downtime can result in significant loss of revenue and reputation.

There is no "one size fits all" approach to delivering High Availability (HA). Unique application attributes, business requirements, operational capabilities and legacy infrastructure can all influence HA technology selection. And technology is only one element in delivering HA: people and processes are just as critical as the technology itself.

MySQL is deployed into many applications demanding availability and scalability. Availability refers to the ability to cope with, and if necessary recover from, failures on the host, including failures of MySQL, the operating system, or the hardware and maintenance activity that may otherwise cause downtime. Scalability refers to the ability to spread both the database and the load of your application queries across multiple MySQL servers.

Because each application has different operational and availability requirements, MySQL offers a range of certified and supported solutions, delivering the appropriate levels of High Availability (HA) and scalability to meet service level requirements. Such solutions extend from replication, through virtualization and geographically redundant, multi-data center solutions delivering 99.999% uptime.

Selecting the right high availability solution for an application largely depends on:

The primary solutions supported by MySQL include:

Further options are available using third-party solutions.

Each architecture used to achieve highly available database services is differentiated by the levels of uptime it offers. These architectures can be grouped into three main categories:

As illustrated in the following figure, each of these architectures offers progressively higher levels of uptime, which must be balanced against potentially greater levels of cost and complexity that each can incur. Simply deploying a high availability architecture is not a guarantee of actually delivering HA. In fact, a poorly implemented and maintained shared-nothing cluster could easily deliver lower levels of availability than a simple data replication solution.

Figure 15.1. Tradeoffs: Cost and Complexity versus Availability

As the number of

The following table compares the HA and Scalability capabilities of the various MySQL solutions:

Table 15.1. Feature Comparison of MySQL HA Solutions

Requirement MySQL Replication Windows Server DRBD Oracle VM Template MySQL Cluster
Availability
Platform Support All Supported by MySQL Server Linux Linux Oracle Linux AllSupported by MySQL Cluster
Automated IP Failover No Yes Yes Depends on Connector and Configuration
Automated Database Failover No Yes Yes Yes
Automatic Data Resynchronization No Yes N/A - Shared Storage Yes
Typical Failover Time User / Script Dependent Configuration Dependent, 60 seconds and Above Configuration Dependent, 60 seconds and Above 1 Second and Less
Synchronous Replication No, Asynchronous and Semisynchronous Yes N/A - Shared Storage Yes
Shared Storage No, Distributed No, Distributed Yes No, Distributed
Geographic redundancy support Yes Yes, via MySQL Replication Yes, via MySQL Replication Yes, via MySQL Replication
Update Schema On-Line No No No Yes
Scalability
Number of Nodes One Master, Multiple Slaves One Active (primary), one Passive (secondary) Node One Active (primary), one Passive (secondary) Node 255
Built-in Load Balancing Reads, via MySQL Replication Reads, via MySQL Replication Reads, via MySQL Replication & During Failover Yes, Reads and Writes
Supports Read-Intensive Workloads Yes Yes Yes Yes
Supports Write-Intensive Workloads Yes, via Application-Level Sharding Yes, via Application-Level Sharding to Multiple Active/Passive Pairs Yes, via Application-Level Sharding to Multiple Active/Passive Pairs Yes, via Auto-Sharding
Scale On-Line (add nodes, repartition, etc.) No No No Yes