不停服务做mysql从库

不念不忘少年蓝@ 2022-09-29 05:19 138阅读 0赞

mysql从库挂掉，记下master节点然后导出数据库，导入到从库服务器，最后修改slave的master信息，有时候直接就好了，如果没有好，比如导出的时候主节点添加了很多数据，导致pos变掉了，当然会变，毕竟没有停止数据库。如果没有好用下面的脚本，执行下，跳过重复的数据就ok了

change master to master_host='111.111.111.111',master_user='slave_site',master_password='password', master_log_file='mysql-bin.000520',master_log_pos=146851479;

mysql\_slave\_skip.pl

#!/usr/bin/env perl
    use strict;
    use warnings;
    
    # get slave status
    sub get_status { 
        my ($ip, $usr, $pass) = @_;
        my $info = `mysql -u$usr -p$pass -h$ip -e 'show slave status\\G;'`;
        if (($info =~ /Slave_IO_Running: Yes/) && ($info =~ /Slave_SQL_Running: No/)) {
            return 1;
        }
        return 0;
    }
    # mysql slave skip
    sub slaveskip { 
        my ($ip, $usr, $pass) = @_;
        print "slave 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;'");
    }
    
    sub stopslave { 
        my ($ip, $usr, $pass) = @_;
        print "stop slave\n";
        system("mysql -u$usr -p$pass -h$ip -e 'slave stop;'");
    }
    
    my @hosts = qw/
    localhost:root:A3NWgX4WQ4m22KvMU2a3Vc2vQ
    /;
    foreach (@hosts) {
        my ($ip, $usr, $pass) = split ':';
        print "// ----- $ip\n";
        my $oktimes=0;
        my $count = 1;
        while ($count < 100000) {
            my $status = get_status($ip, $usr, $pass);
            if($status==0){
                $oktimes++;
                if($oktimes<50){
                    select(undef, undef, undef, 0.1);
                    $count++;
                    next;
                }
                else{
                    print "i: $count status: $status\n";
                    #stopslave($ip, $usr, $pass);
                    last;
                }
            }
            else{
            print "i: $count status: $status\n";
            $oktimes=0;
                slaveskip($ip, $usr, $pass);
                select(undef, undef, undef, 0.1);
                $count++;
            }
        }
        print "\n";
    }
    
    exit;