工具名称:mysqlha_skiperror.sh
工具用途:用于MySQL跳过主从错误
工具参数:options:
-P port 指定端口
-N number 指定跳过错误次数 不指定默认为10次(如果不知道可以设置大一些,会记录log)
-C error code Take , as the separator 指定跳过错误的状态码 可以跳过多个以,分隔
工具示例: mysqlha_skiperror.sh -P 4444 -C 1032
工具执行结果:
Could not execute Write_rows event on table test.t; Duplicate entry '23' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000011, end_log_pos 6621
Could not execute Write_rows event on table test.t; Duplicate entry '24' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000011, end_log_pos 6800
Could not execute Delete_rows event on table testone.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000011, end_log_pos 6983
2016-08-08 17:44:25 slave is ok
count_total=3
工具log:会在当前dir下生成skiperror.txt 里面存的是详细的报错内容,看完可以删除。
例子:
#!/bin/bash
#date 2016-08-04
#author liding@xywy.com
currentTime=`date "+%Y-%m-%d %H:%M:%S"`
function helpfunc(){
echo
echo "Please check your input!!!!"
echo "options:"
echo " -P port"
echo " -N number"
echo " -C error code Take , as the separator "
echo " Please enter the port number and the default skip number, not the number of times the default is 10 "
}
count_num=10
code=1062,1032
if [ $# -lt 1 ] ;
then
helpfunc
exit 1
else
while getopts "P:N:C: " Option
do
case $Option in
P) port=$OPTARG;;
N) count_num=$OPTARG;;
C) code=$OPTARG;;
*) helpfunc; exit 1; ;;
esac
done
count_total=0
logdir=/data/logs/skiperror
if [ -d $logdir ];then
echo "$logdir exists"
else
mkdir -p $logdir
fi
#for ((a=0;a++;a<10));
for i in $(seq $count_num)
do
sql_yn=`/etc/dbbin/mysqlha_login.sh -P $port -e "show slave status\G" | grep Slave_SQL_Running | awk '{print $2}'`
if [ "$sql_yn" = "Yes" ];then
echo "$currentTime slave is ok">>$logdir/skiperror.txt
echo "$currentTime slave is ok"
echo "count_total=$count_total">>$logdir/skiperror.txt
echo "count_total=$count_total"
exit 3;
else
for error_code in $code
do
err=`/etc/dbbin/mysqlha_login.sh -P $port -e "show slave status\G" | grep Last_Errno: | awk '{print $2}'`
if [ "$err" -eq "$error_code" ] && [ "$sql_yn" = "No" ] ;then
"$currentTime" >>$logdir/skiperror.txt
/etc/dbbin/mysqlha_login.sh -P $port -e "show slave status\G" | grep Last_SQL_Error | awk -F '_Error:' '{print $2}'>>$logdir/skiperror.txt
/etc/dbbin/mysqlha_login.sh -P $port -e "show slave status\G" | grep Last_SQL_Error | awk -F '_Error:' '{print $2}'
/etc/dbbin/mysqlha_login.sh -P $port -e "set global sql_slave_skip_counter=1;start slave"
#count_total=`expr $count_total + 1`
((count_total=$count_total + 1))
else
continue
fi
done
fi
done
转载于:https://blog.51cto.com/774148515/1845606