News Archive
PhpRiot Newsletter
Your Email Address:

More information

PECL/mysqlnd_ms and the client failover dilemma - part 1

Note: This article was originally published at Planet PHP on 5 January 2013.
Planet PHP

MySQL Replication has a weak point: the master (primary) is a single point of failure. People ask frequently about client failover. Making a client library failover a client automatically from one server to another is simple. PECL/mysqlnd_ms can do it. Unfortunately, this is only the tip of the iceberg when using a primary copy (master-slave) database cluster. Among others, you may have to reconfigure clients. Below is one hint (no solution) for deploying PECL/mysqlnd_ms configuration files using MHA (Master High Availability Manager and tools for MySQL).

What's the plural of dilemma?

If a server is unavailable a client may attempt to connect to another server given a list of alternatives. A client library can handle it entirely transparently and automatically if connection state is no issue. Otherwise the client can receive an error, handle the exception and start over again with the client library automatically picking a failover server for the next attempt. As long as the client library knows of failover candidates it works.

Possibly, you want the client library to remember failed server. Dilemma 1: PHP is stateless. PECL/mysqlnd_ms can remember failed servers only for the lifespan of PHP. Anything beyond requires deployment of the configuration file.

Dilemma 2: there may be no failover alternatives. A MySQL Replication master is a single point of failure. In the unlikely event of loss of cabine pressure a master the entire database cluster has to be reconfigured. Usually, a slave has to be promoted as a master and all other slaves have to be made aware of the change.

Certainly, nobody wants thousands of clients attempting to reconfigure a cluster concurrently. Thus, over the years, MySQL users have developed tools for monitoring MySQL Replication clusters and handling failure. MHA (Master High Availability Manager and tools for MySQL) is one of them. MHA automates the cluster reconfiguration: it claims to work in heterogenous deployments with MySQL 5.0 or newer and it may take far less than a minute to replace a failed master.

Dilemma 3: client reconfiguration is left as an excercise, sort of.

Client reconfiguration as part of the failover process

Monitoring tools external to a MySQL Replication cluster often propose two ways to inform clients of cluster topology changes (e.g. new master):

  • Update catalog with cluster information (more in a future blog post)
  • IP switch over (not discussed)
  • Client configuration deployment (see below)

IP switch over is transparent from a client perspective. The IP of the failed master is assigned to the newly elected master. No client changes required - great, issue solved! However, what is virtual IPs are no option for you?

The MySQL Replication monitoring tool of your choice may allow calling custom scripts during cluster reconfiguration. You can hook in your IP switch over script, or, for example, deploy PECL/mysqlnd_ms configuration files.

MHA updating PECL/mysqlnd_ms config

The MHA master_ip_failover_script configuration parameter can be used to call a script for client configuration deployment during cluster reconfiguration. Below is an example configuration file for a minimalistic cluster consisting of one master and one slave, just enough to test MHA in action.

[server default] # mysql user and password user=root password=root ssh_user=root # working directory on the manager manager_workdir=/var/log/masterha/app1 # working directory on MySQL servers remote_workdir=/var/log/masterha_remote/app1 master_binlog_dir=/home/nixnutz/ftp/mysql-5.6.9-rc-ms3-master/data master_ip_failover_script=/home/nixnutz/ftp/mha4mysql-manager-0.55/mysqlnd_ms_update.php [server1] hostname=localhost ip= port=3309 [server2] hostname=localhost ip= port=3310

As a PHP guy, I am using a PHP script to handle the PECL/mysqlnd_ms configuration file update. The script is invoked by MHA during master failover. MHA is passing most valuable information on the dead and the new master to the script. Based on this, the script can remove the slave that became a master from the slave list, remove the failed master and add a new master to the master list. The script I show is far from production ready but you get the idea. For example, I am not discussing remote configuration files on remote application serversR

Truncated by Planet PHP, read more at the original (another 8732 bytes)