MySQL Use Case: MySQL High Availability (HA)

Table of Contents

Introduction

This use case covers high availability (HA) options for MySQL, which involves ways to cope with and recover from failures, and allow scheduled maintenance activity, without causing any downtime.

Related articles:

Scenario 1: Shared Storage

In this scenario, the user is running a popular WordPress website. The user wants to increase the site's availability as part of a disaster recovery plan. However, they don't foresee the need to take servers down for maintenance very often, and they don't want to have to tackle anything complicated.

The simplest way to increase a MySQL installation's availability is to set up a Shared Storage volume, and use it to store the database files. A Shared Storage volume can be created on the 1&1 Cloud Panel with a few clicks.

The user then creates and stores an image of their server. This allows the user to create a new server from that image. If a server crashes, a new server with the user's custom theme and all of the required WordPress configurations can be brought online within minutes.

Multiple servers can be connected to the same Shared Storage volume. This gives the user the option to scale their WordPress installation in the future, if they choose.

Scenario 2: Master-Node Replication

In this scenario, the user is running a financial services company which stores its customers' data, including their account information, in a MySQL database. This data is critical, and a failure of the database would have a significant business impact.

The best solution for this user is to set up Master-Node MySQL Replication, with node servers located in multiple data centers around the world. In this setup, one server is designated as the master. The master server receives all of the WRITE queries. It executes the WRITE queries, logs them, then ships the log to the node servers to keep the data the same across all of the nodes.

As a basic data redundancy setup, the node servers do not need to be configured to handle direct traffic. However, if the user wants to greatly scale up their operations, the node servers can be used as part of a load-balancing setup in which each of the node servers handles a portion of the overall query load.

Scenario 3: Master-Master Replication

In this scenario, the user is in charge of maintaining a high-volume social image sharing website. All of the images and associated data (including "likes" and comments) are stored in MySQL. This installation needs to have high uptime and a very low failover recovery time, all while maintaining a fast query response time for visitors.

This user decides to implement Master-Master (also called "multi-master") MySQL Replication. In this setup, each master server is constantly being replicated onto a secondary master server. If the main master server fails, the secondary master automatically takes over.

In order to ensure the fastest possible failover recovery time, each pair of master servers should be located in the same data center. However, the user can also get geographic security by having each pair of master servers replicate data to a node server which can be located in a separate data center.