2009/03/12

scripts for mysql skip slave error

when slave has some errors, can try skip it, also like this:
slave stop;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
slave start;

if have more than 2 servers, trouble to update everyone, write small scripts and save,like:

#!/usr/bin/env perl
use strict;
use warnings;

my @hosts = qw/
192.168.1.123:root:tt1234
192.168.1.124:root:tt1234
/;

foreach (@hosts) {
    my ($ip, $usr, $pass) = split ':';
    print "// ----- $ip\n";
    slaveskip($ip, $usr, $pass);
    print "\n";
}

# mysql slave skip
sub slaveskip {
    my ($ip, $usr, $pass) = @_;
    my $info = `mysql -u$usr -p$pass -h$ip -e 'show slave status\\G;'`;
    # Slave_SQL_Running is No
    if (($info =~ /Slave_IO_Running: Yes/) && ($info =~ /Slave_SQL_Running: No/)) {
        my $errno = $1 if $info =~ /Last_Errno: (\d*)/;
        my $error = $1 if $info =~ /Last_Error: (.*)/;
        if ($errno > 0 && length($error) > 0) {
            print "slave error**\n";
            print "errno : $errno\nerror : $error\n";
            system("mysql -u$usr -p$pass -h$ip -e 'slave stop;'");
            system("mysql -u$usr -p$pass -h$ip -e 'set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;'");
            system("mysql -u$usr -p$pass -h$ip -e 'slave start;'");
        }
    } else {
        print "salve ok.\n";
    }
}

exit;

2 replys:

milo said...

嗯,收藏学习一下

Gregg Lain said...

Thank you - saved me the time to write one and I learned some Perl from yours in the process. Added some more output to it, and an internal loop - posted here: http mochabomb.com publicscripts

Post a Comment