Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.8Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 311.7Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  High Availability and Scalability

Chapter 16 High Availability and Scalability

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 level of availability required.

  • The type of application being deployed.

  • Accepted best practices within your own environment.

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:

  • Data Replication.

  • Clustered & Virtualized Systems.

  • Shared-Nothing, Geographically-Replicated Clusters.

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 16.1 Tradeoffs: Cost and Complexity versus Availability

As the number of “nines” in the uptime percentage increases, so does the cost and complexity, progressing from basic replication, to a clustered and virtualized configuration, to shared-nothing clusters replicated across geographic regions. Different kinds of organizations require different “nines” of availability, from Internet service providers and mainstream businesses at 3 nines, online services at 4 nines, and eCommerce, telecom, and military applications at 5 nines.

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

Table 16.1 Feature Comparison of MySQL HA Solutions

RequirementMySQL ReplicationMySQL Cluster
Availability
Platform SupportAll Supported by MySQL Server (http://www.mysql.com/support/supportedplatforms/database.html)All Supported by MySQL Cluster (http://www.mysql.com/support/supportedplatforms/cluster.html)
Automated IP FailoverNoDepends on Connector and Configuration
Automated Database FailoverNoYes
Automatic Data ResynchronizationNoYes
Typical Failover TimeUser / Script Dependent1 Second and Less
Synchronous ReplicationNo, Asynchronous and SemisynchronousYes
Shared StorageNo, DistributedNo, Distributed
Geographic redundancy supportYesYes, via MySQL Replication
Update Schema On-LineNoYes
Scalability
Number of NodesOne Master, Multiple Slaves255
Built-in Load BalancingReads, via MySQL ReplicationYes, Reads and Writes
Supports Read-Intensive WorkloadsYesYes
Supports Write-Intensive WorkloadsYes, via Application-Level ShardingYes, via Auto-Sharding
Scale On-Line (add nodes, repartition, etc.)NoYes


User Comments
Sign Up Login You must be logged in to post a comment.