Monitoring MySQL Slave Replication Status with Ruby and Cron

When offering higher levels of uptime on a web site backed by MySQL, a good approach is to set up a MySQL master-slave configuration for failover between servers. This generally works quite well, but once in a while, there is a problem or error that causes the replication to cease. The slave then ceases to process updates and gets out of sync with the master.

The script below is a quick and easy approach to monitoring the status of replication on the slave. If the slave thread or IO ceases, the slave gets more than 120 seconds behind the master, or there is an error, the script will email all the slave status information to an email address you specify to alert you that you need to log in and sort things out. I run the script from cron so that I get notified fairly soon if a problem arises.

RAILS_ENV = 'production'
ALERT_EMAIL_ADDRESS = '[email protected]'

require 'open3'
require 'socket'
require "#{File.dirname(__FILE__)}/../../config/environment.rb"

r = ActiveRecord::Base.connection.execute("show slave status").fetch_hash
unless  r["Slave_IO_Running"] == "Yes" && r["Slave_SQL_Running"] == "Yes" &&
  r["Last_Errno"] == "0" && r["Seconds_Behind_Master"].to_i < 120

    status = "*** STATUS ***\\n" + r.to_a.collect { |i| "#{i[0]}: #{i[1]}\\n" }.join
    subject = "MySQL Slave Replication Down on #{Socket.gethostname}"

    Open3.popen3("mail -s \"#{subject}\" #{ALERT_EMAIL_ADDRESS}") do |stdin, stdout, stderr|
       stdin.write(status)
    end
end

Note: This script relies on being part of a rails app to get a database connection. It would be fairly easy to modify it to include db credentials and open the connection.