It is found that the mysqlslave server often reports errors due to the update statements generated by some special characters or symbols, and the entire synchronization will also be stuck there. The initial method was to manually go to the wrong machine, run the following three SQL statements to skip the error. Slavestop; setGLOBALSQL_SLAVE_SKIP_COUNTER1; slavestart; used on a server Load balancer instance
It is found that the mysql slave server often reports errors due to the update statements generated by some special characters or symbols, and the entire synchronization will also be stuck there. The initial method is to manually go to the wrong machine, run the following three SQL statements to skip the error. Slave stop; set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; slave start; used by one slave machine
It is found that the mysql slave server often reports errors due to the update statements generated by some special characters or symbols, and the entire synchronization will also be stuck there. The initial method is to manually go to the wrong machine, run the following three SQL statements to skip the error.
slave stop;set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;slave start;
It's okay to use this method on a server Load balancer. it's troublesome to have multiple servers, so you can easily write a simple perl script to skip errors in a unified manner. The Code is as follows:
#!/usr/bin/env perluse strict;use warnings;# get slave statussub 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 skipsub 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;'");}my @hosts = qw/192.168.0.101:root:tt1234192.168.0.102:root: tt1234192.168.0.103:root: tt1234/;foreach (@hosts) { my ($ip, $usr, $pass) = split ':'; print "// ----- $ip\n"; my $count = 1; while ($count < 100000) { my $status = get_status($ip, $usr, $pass); print "i: $count status: $status\n"; last if $status == 0; slaveskip($ip, $usr, $pass); select(undef, undef, undef, 0.1); $count++; } print "\n";}exit;
Original article address: Perl skips MySQL Slave replication errors in batches. Thank you for sharing this with me.