PHP script to check MySQL replication status

If you have MySQL replication running in either a master-slave or master-master type setup then don’t assume everything is running perfectly all the time. Power cuts and other disasters do happen so you need to check periodically to ensure it’s all working. This post has a PHP script to check for any MySQL replication errors and then emails if there are any issues

MySQL Slave Status

Get the current MySQL Slave status by running the command "SHOW SLAVE STATUS" on the slave. This will spit out all sorts of information including whether the following are running ("Yes" or "No"):

• Slave_IO_Running
• Slave_SQL_Running

and then an error number and message for each of the above like so:

• Last_IO_Errno
• Last_IO_Error
• Last_SQL_Errno
• Last_SQL_Error

PHP script to check MySQL slave status

The following script should be run periodically. Modify it to suit your own purposes as you see fit.

Enter values for the username and password to log in as (my script assumes the same login and password is used for all servers, and that all servers can be logged into from the current server) and a list of MySQL servers to check.

$username = '[username]';
$password = '[password]';
$servers = array(
	'[server1]',
	'[server2]',
	'[serverN]',
);

$errors = '';

foreach($servers as $server) {
	$link = mysql_connect($server, $username, $password);
	if($link) {
		$res = mysql_query("SHOW SLAVE STATUS", $link);
		$row = mysql_fetch_assoc($res);
		if($row['Slave_IO_Running'] == 'No') {
			$errors .= "Slave IO not running on $servern";
			$errors .= "Error number: {$row['Last_IO_Errno']}n";
			$errors .= "Error message: {$row['Last_IO_Error']}nn";
		}
		if($row['Slave_SQL_Running'] == 'No') {
			$errors .= "Slave SQL not running on $servern";
			$errors .= "Error number: {$row['Last_SQL_Errno']}n";
			$errors .= "Error message: {$row['Last_SQL_Error']}nn";
		}
		mysql_close($link);
	}
	else {
		$errors .= "Could not connect to $servernn";
	}
}

if($errors) {
	mail('[email address]', 'MySQL slave errors', $errors);
}

Useful links for dealing with errors

I’m not going to help you fix errors myself as I’d simply be copying more or less the information I’ve found on other websites (and I don’t know how accurate or successful all of the solutions are) so will link to some solutions here.

HowtoForge has a post called "how to repair MySQL replication" which shows examples of the SHOW SLAVE STATUS command and covers fixing issues when Slave_SQL_Running is "No". I’ve used the solution presented a couple of times to fix issues, but do be aware if you’ve got something like a primary key violation issue and you skip the error then your databases may not actually be accurately in sync.

I personally got a Slave_IO_Running = "No" error which resulted in the message "Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log".

This article at Brian Klug’s wiki helped me; I did the change master log file solution which worked for me, but again be careful as you may lose data. Note you don’t need to also set the master host, user and password too, just the file and position.

Another article at dev.kafol.net gave me a cross reference for the same solution and also talks about setting sync_binlog to 1 in the MySQL configuration which may be useful; I haven’t tried this yet myself but I imagine on a busy server it may lead to more intensive disk activity.