Attachment 'write_heartbeat.pl'

Download

#!/usr/bin/perl
#
###
#
# write_heartbeat.pl - timestamp replication checks between master & slaves
# Copyright (C) 2008 Anchor Systems - http://www.anchor.com.au/
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA 02111-1307, USA.
#
###

#
# This script has nothing to do with Heartbeat software (www.linux-ha.org).
# It inserts a record into a table on the master MySQL server, allowing the
# slaves to check the replicated records to see how far behind the master it
# is, for an added level of replication checking paranoia.
#
# Code based on that in section 7.5.4.6 of High Performance MySQL (0596003064)
#
# This database and table must be set up before the script is run:
#
# Database: replication
# Table: heartbeat
#
# CREATE TABLE heartbeat
# (
#       unix_time       INTEGER         NOT NULL,
#       db_time         TIMESTAMP       NOT NULL,
#       INDEX           time_idx(unix_time)
# )

use strict;
use warnings;

use DBI;

sub debug (@) {
  print @_ if -t STDOUT;
}

sub cleanup {
        # Disconnect the database connections
        if (defined($::dbh)) { $::dbh->disconnect() or warn "Error disconnecting local database: ", $DBI::errstr; };
}

# Connect the local database
eval {
        our $dsn = "DBI:mysql:replication;mysql_read_default_file=/root/.my.cnf;mysql_connect_timeout=5";
        our $dbh = DBI->connect($dsn, "", "", {'RaiseError' => 1});

};
if ($@) {
        warn $@;
        exit(1);
};

# Make sure we are running on the master
eval {
        my $sth = $::dbh->prepare("show variables");
        my $res = $sth->execute;
        our $mysql_variables;
        while (my $ref = $sth->fetchrow_hashref) {
                $mysql_variables->{$ref->{'Variable_name'}} = $ref->{'Value'};
        }       
        $sth->finish;
};
if ($@) {
        warn $@;
        cleanup();
        exit(2);
};

# And exit if this is running on the backup master
if ($::mysql_variables->{'datadir'} =~ /hamysql/) {
        debug "This server is currently the master\n";
}
else {
        debug "This server is currently a slave (backup master)\n";
        cleanup();
        exit(0);
}

# Update the heartbeat data:
# - Update only if there is just one record
# - Delete all but last record then update it if there are > 1 records
# - Insert a record if there are 0 records

eval {
        debug "SELECT COUNT(*) from heartbeat\n";
        my $sth = $::dbh->prepare("SELECT COUNT(*) from heartbeat");
        $sth->execute;
        
        our $rowcount;
        my @row = $sth->fetchrow_array;
        $rowcount = $row[0];
};
if ($@) {
        warn $@;
        cleanup();
        exit(3);
};

eval {
        if ($::rowcount == 0) {
                debug "INSERT INTO heartbeat (unix_time, db_time) VALUES (unix_timestamp(), NULL)\n";
                $::dbh->do("INSERT INTO heartbeat (unix_time, db_time) VALUES (unix_timestamp(), NULL)");
        }
        elsif ($::rowcount > 1) {
                $::rowcount--;
                debug "DELETE FROM heartbeat ORDER BY unix_time ASC LIMIT $::rowcount\n";
                $::dbh->do("DELETE FROM heartbeat ORDER BY unix_time ASC LIMIT $::rowcount");
        }

        debug "UPDATE heartbeat SET unix_time = UNIX_TIMESTAMP(), db_time = NULL\n";
        $::dbh->do("UPDATE heartbeat SET unix_time = UNIX_TIMESTAMP(), db_time = NULL");
};
if ($@) {
        warn $@;
        cleanup();
        exit(4);
};

cleanup();
exit(0);

Attached Files

To refer to attachments on a page, use attachment:filename, as shown below in the list of files. Do NOT use the URL of the [get] link, since this is subject to change and can break easily.
  • [get | view] (2013-05-31 11:14:57, 8.0 KB) [[attachment:IPaddr2_vlan.sh]]
  • [get | view] (2013-05-31 11:14:58, 361.3 KB) [[attachment:LCA2008-talk.odp]]
  • [get | view] (2013-05-31 11:14:58, 832.2 KB) [[attachment:LCA2008-talk.pdf]]
  • [get | view] (2013-05-31 11:14:57, 3.7 KB) [[attachment:alter_mysql_slave]]
  • [get | view] (2013-05-31 11:14:57, 3.7 KB) [[attachment:alter_mysql_slave.sh]]
  • [get | view] (2013-05-31 11:14:57, 1.5 KB) [[attachment:authkeys]]
  • [get | view] (2013-05-31 11:14:57, 1.5 KB) [[attachment:authkeys.txt]]
  • [get | view] (2013-05-31 11:14:58, 1.1 KB) [[attachment:check-drbd.sh]]
  • [get | view] (2013-05-31 11:14:57, 11.4 KB) [[attachment:check_replication.pl]]
  • [get | view] (2013-05-31 11:14:57, 6.1 KB) [[attachment:cib.xml.template]]
  • [get | view] (2013-05-31 11:14:58, 8.0 KB) [[attachment:db-export-mysqlstandby.pl]]
  • [get | view] (2013-05-31 11:14:57, 3.8 KB) [[attachment:drbd.conf.txt]]
  • [get | view] (2013-05-31 11:14:58, 1.3 KB) [[attachment:drbddisk.sh]]
  • [get | view] (2013-05-31 11:14:57, 1.5 KB) [[attachment:ha.cf.txt]]
  • [get | view] (2013-05-31 11:14:58, 9.0 KB) [[attachment:init_mysql_slave.pl]]
  • [get | view] (2013-05-31 11:14:58, 1.2 KB) [[attachment:logd.cf.txt]]
  • [get | view] (2013-05-31 11:14:58, 2.2 KB) [[attachment:lvs-helper.sh]]
  • [get | view] (2013-05-31 11:14:57, 1.8 KB) [[attachment:my.cnf.txt]]
  • [get | view] (2013-05-31 11:14:57, 2.2 KB) [[attachment:myslave.cnf.txt]]
  • [get | view] (2013-05-31 11:14:57, 5.5 KB) [[attachment:mysqld.sh]]
  • [get | view] (2013-05-31 11:14:58, 2.0 KB) [[attachment:mysqlslave.cnf.txt]]
  • [get | view] (2013-05-31 11:14:57, 5.7 KB) [[attachment:mysqlslaved.sh]]
  • [get | view] (2013-05-31 11:14:57, 3.5 KB) [[attachment:write_heartbeat.pl]]

You are not allowed to attach a file to this page.