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=127.0.0.1
port=3309
[server2]
hostname=localhost
ip=127.0.0.1
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 servers…
#!/usr/local/bin/php
<?php
define("MYSQLND_MS_CONFIG", "/home/nixnutz/ftp/mha4mysql-manager-0.55/mysqlnd_ms_config.json");
/*
New master activation phase
$options[0] script name
1 --command=start
2 --ssh_user=(new master's ssh username)
3 --orig_master_host=(dead master's hostname)
4 --orig_master_ip=(dead master's ip address)
5 --orig_master_port=(dead master's port number)
6 --new_master_host=(new master's hostname)
7 --new_master_ip=(new master's ip address)
8 --new_master_port(new master's port number)
9 --new_master_user=(new master's user)
10 --new_master_password(new master's password)
*/
function server_match($server_details, $host, $ip, $port) {
if (!isset($server_details['host'])) {
/* TODO: bark */
return false;
}
if ($port && isset($server_details['port'])) {
/* TCP/IP ? */
if ($server_details['port'] != $port) {
return false;
}
/* compare ip with server details host */
return ($server_details['host'] == $ip);
}
/* Should be socket... */
return ($server_details['host'] == $host);
}
$options = array();
foreach ($argv as $k => $arg) {
$tmp = substr($arg, strpos($arg, "=") + 1);
if ((substr($tmp, 0, 1) == '"' || substr($tmp, 0, 1) == "'") &&
(substr($tmp, 0, 1) == substr($tmp, -1, 1))) {
$tmp = substr($tmp, 1, -1);
}
$options[] = $tmp;
}
file_put_contents(MYSQLND_MS_CONFIG . '.options', var_export($options, true));
if (('start' == $options[1]) && (11 == count($options))) {
/* New master activation phase... */
$json = file_get_contents(MYSQLND_MS_CONFIG);
if (!$json) {
exit(1);
}
file_put_contents(MYSQLND_MS_CONFIG . ".old", $json);
$json = json_decode($json, true);
foreach ($json as $section_name => $section_details) {
/* remove old master and add new one... */
if (isset($section_details['master'])) {
foreach ($section_details['master'] as $name => $details) {
if (server_match($details, $options[3], $options[4], $options[5])) {
printf("Goodbye master...\n");
var_dump($details);
unset($json[$section_name]['master'][$name]);
}
}
if ($port = $options[8]) {
$new_master = array(
'host' => $options[7],
'port' => (int)$options[8],
);
} else {
$new_master = array(
'host' => $options[6],
);
}
/* TODO: user/password */
$json[$section_name]['master']['master_' . $new_master['host']] = $new_master;
}
/* remove slave that has become a master */
if (isset($section_details['slave'])) {
foreach ($section_details['slave'] as $name => $details) {
if (server_match($details, $options[6], $options[7], $options[8])) {
printf("Goodbye former slave, you are a master now...\n");
var_dump($details);
unset($json[$section_name]['slave'][$name]);
}
}
}
/* TODO: check resulting config, for example, number of slaves/masters! */
}
$ok = file_put_contents(MYSQLND_MS_CONFIG . ".new", json_encode($json, JSON_PRETTY_PRINT));
exit((FALSE !== $ok) && ($ok > 0));
}
If all goes well and all the wheels (there are [too] many!) turn as they should, masterha_manager does it job upon master failure and the script updates the client configuration file.
> masterha_manager --conf=/etc/app1.cnf
[...]
From:
localhost (current master)
+--localhost
To:
localhost (new master)
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] * Phase 3.3: New Master Diff Log Generation Phase..
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] * Phase 3.4: Master Log Apply Phase..
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Fri Jan 4 18:39:17 2013 - [info] Starting recovery on localhost(127.0.0.1:3310)..
Fri Jan 4 18:39:17 2013 - [info] This server has all relay logs. Waiting all logs to be applied..
Fri Jan 4 18:39:17 2013 - [info] done.
Fri Jan 4 18:39:17 2013 - [info] All relay logs were successfully applied.
Fri Jan 4 18:39:17 2013 - [info] Getting new master's binlog name and position..
Fri Jan 4 18:39:17 2013 - [info] mysql-bin.000007:231
Fri Jan 4 18:39:17 2013 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='localhost or 127.0.0.1', MASTER_PORT=3310, MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=231, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Fri Jan 4 18:39:17 2013 - [info] Executing master IP activate script:
Fri Jan 4 18:39:17 2013 - [info] /home/nixnutz/ftp/mha4mysql-manager-0.55/mysqlnd_ms_update.php --command=start --ssh_user=root --orig_master_host=localhost --orig_master_ip=127.0.0.1 --orig_master_port=3309 --new_master_host=localhost --new_master_ip=127.0.0.1 --new_master_port=3310 --new_master_user='root' --new_master_password='root'
Goodbye master...
array(2) {
["host"]=>
string(9) "127.0.0.1"
["port"]=>
int(3309)
}
Goodbye former slave, you are a master now...
array(2) {
["host"]=>
string(9) "127.0.0.1"
["port"]=>
int(3310)
}
1Fri Jan 4 18:39:17 2013 - [info] OK.
Fri Jan 4 18:39:17 2013 - [info] ** Finished master recovery successfully.
Fri Jan 4 18:39:17 2013 - [info] * Phase 3: Master Recovery Phase completed.
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] * Phase 4: Slaves Recovery Phase..
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] Generating relay diff files from the latest slave succeeded.
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] All new slave servers recovered successfully.
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] * Phase 5: New master cleanup phase..
Fri Jan 4 18:39:17 2013 - [info]
Fri Jan 4 18:39:17 2013 - [info] Resetting slave info on the new master..
Fri Jan 4 18:39:17 2013 - [info] localhost: Resetting slave info succeeded.
Fri Jan 4 18:39:17 2013 - [info] Master failover to localhost(127.0.0.1:3310) completed successfully.
Fri Jan 4 18:39:17 2013 - [info]
----- Failover Report -----
app1: MySQL Master failover localhost to localhost succeeded
Master localhost is down!
Check MHA Manager logs at linux-dstv for details.
Started automated(non-interactive) failover.
Invalidated master IP address on localhost.
The latest slave localhost(127.0.0.1:3310) has all relay logs for recovery.
Selected localhost as a new master.
localhost: OK: Applying all logs succeeded.
localhost: OK: Activated master IP address.
Generating relay diff files from the latest slave succeeded.
localhost: Resetting slave info succeeded.
Master failover to localhost(127.0.0.1:3310) completed successfully.
No solution, but a hint
As said at the beginning, I’m not showing a solution but only giving a hint of what can be done – on the server side.
Instead of MHA one may also want to evaluate the new mysqlfailover utility designed for MySQL 5.6 and GTIDs. There’s a nice demo. The mysqlfailover utility offers hooks like MHA but does not pass any information on the cluster topology (e.g. new master) to the scripts. However, it may be too early to judge about it given that MySQL 5.6 is not GA yet.
Happy hacking!
