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;