SAN-Backed High Availability MySQL Servers

High-Availability Clustering Technologies

High-availability clustering is designed to increase systems availability.  In a previous article, we looked at DRBD-based file storage -- analogous to network disk mirroring.  For this article, we will be using:

• SAN-Backed Storage
• Corosync - Pacemaker

DRBD is useful in situations that lack a SAN.  However a good storage network design is preferable for reliability and performance.  DRBD runs as a daemon on each node, replicating block devices.  There is increased overhead, not only for disk IO, but also for processor iowait states and memory usage.  This overhead is relieved by a SAN, particularly valuable when deploying resource-intensive database servers.

The cluster is composed of elements that differ from a single-server deployment.  

• Resources unique to each node
• Data shared between nodes on the cluster
• Services shared between the nodes on the cluster

Resources Unique to Each Node

Each node has is a server with its own operating system and hardware.  The processor, memory, disk and IO subsystems (including network interfaces) are controlled by the operating system installed on the boot partition.

Data Shared Between Nodes on the Cluster

For a MySQL cluster, there are two types of shared data:  configuration files and databases.  The configuration files are those located in the /etc/mysql/ directory.  When shared between the two nodes, the MySQL server will have an identical configuration regardless of the node that is active.  However, there are circumstances in which the MySQL configuration files may be unique to each server.  The databases are kept in the /var/lib/mysql/ directory and include the log files.  These will be copied to a shared ext4 partition.

MySQL Clustering Caveats

Although the two nodes share the same MySQL databases, UNDER NO CIRCUMSTANCES SHALL THE TWO NODES SIMULTANEOUSLY ACCESS THE DATABASES.  That is, only one node may run the mysqld daemon at any given time.  If two MySQL daemons access the same database, there will eventually be corruption.  The clustering software controls which node accesses the data.

Corosync - Pacemaker Overview

The illustration below depicts a high-availability cluster design.  Each server has four network interfaces:

• eth0 -- the publicly addressable interfaces
• eth1 -- the iSCSI interfaces
• eth2 and eth3 -- the Corosync - Pacemaker control interfaces

The first interface -- eth0 -- is the publicly addressable interface that provides MySQL database and Apache web server (for PHPMyAdmin) access.  Two IP addresses (unique to server) are assigned at boot time and a third is assigned by the Corosync - Pacemaker portion of the clustering software.

The second interface -- eth1 -- is controlled by the open-iscsi daemon that connects to iSCSI SAN storage.

The third and fourth interfaces -- eth2 and eth3 -- are controlled by the Corosync - Pacemaker software.  These interfaces provide communication links defining the status of each defined resource and control how and where shared services (such as shared DRBD block devices, IP addresses, and the MySQL / Apache2 daemons) run.  In failover clustering, only one node may actively be in control at any time.

Installing and Configuring the MYSQL - Apache Failover Cluster

Begin by installing and MySQL and Apache.  Leave MySQL listening on the default loopback interface 127.0.0.1.  Then, start the Linux Cluster Management Console (LCMC) -- a Java application that will install and configure everything required for clustering.  Select the two nodes by name or IP address, install Pacemaker (NOT Heartbeat).

Once both nodes have the required software, configure the cluster.  LCMC will prompt you for two interfaces to use in the cluster (select eth2 and eth3) and the two-node system will be recognized as a Cluster.  Configure global options for the cluster and begin by adding a the shared storage device.  In this case, the SAN partition is formatted with ext4 and recognized as device /dev/sdc1 on each node.  Mount it at /mnt/dev/sdc using the LCMC Filesystem resource.

The shared data must then be moved to the DRBD device.  Stop MySQL on each node.  On the Active node, move the directories /etc/mysql and /var/lib/mysql to /mnt/sdc/etc/mysql and /mnt/sdc/var/lib/mysql, respectively, and create symlinks back to their original locations.  On the Inactive node, delete the mysql directories and replace them with symlinks to the same locations -- even though those locations are not yet visible.

The LCMC console is then used to finalize the shared services.  Add a shared IP Address 10.195.0.110/32 and Primitive LSB Init Script resources (that is, only running on one server at a time) for MySQL and Apache2.

Fail the servers back and forth several times to check that the system performs as expected.  Finally, install a database and modify the /etc/mysql/my.cnf configuration file, removing the loopback 127.0.0.1 address and subtituting the shared 10.195.0.110 address.  For this article, I install two Zabbix Monitoring databases (zabbix_01 and zabbix_02) for use in distributed monitoring.

The video below illustrates the entire installation and configuration process.