GTID 跳过脚本

跳过单个error

STOP SLAVE;
SET gtid_next = '3b977b7e-ed28-11e7-a8ff-b4969113b678:138609841';
BEGIN;COMMIT;
SET gtid_next = 'AUTOMATIC';
START SLAVE;
show slave status \G;
select * from performance_schema.replication_applier_status_by_worker;

同步事物工作状态查询

(root@localhost) 12:13:57 [(none)]> select * from performance_schema.replication_applier_status_by_worker;
+--------------+-----------+-----------+---------------+------------------------------------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION                          | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
+--------------+-----------+-----------+---------------+------------------------------------------------+-------------------+--------------------+----------------------+
|              |         1 |   1800788 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144139359 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         2 |   1800789 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144139248 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         3 |   1800790 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144138411 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         4 |   1800791 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144128311 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         5 |   1800792 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144108749 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         6 |   1800793 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144108752 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         7 |   1800794 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144108746 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         8 |   1800795 | ON            | 3b977b7e-ed28-11e7-a8ff-b4969113b678:144108747 |                 0 |                    | 0000-00-00 00:00:00  |
+--------------+-----------+-----------+---------------+------------------------------------------------+-------------------+--------------------+----------------------+
8 rows in set (0.02 sec)

GTID_error 跳过脚本

#!/bin/bash
pass='iforgot'

#sql21=`mysql -uroot -p${pass} -e "show slave status\G;" 2>/dev/null|grep -v Last_Error | grep 'executing transaction'| awk '{print $21}'`

#Errno=`mysql -uroot -p${pass} -e "show slave status\G;" |grep 'Last_SQL_Errno' | awk -F ":" '{print $2}'`


#echo $sql21
#echo $Errno

for((i=1;i<100000;i++))
do
sql21=`mysql -uroot -p${pass} -e "show slave status\G;" 2>/dev/null|grep -v Last_Error | grep 'executing transaction'| awk '{print $21}'`

Errno=`mysql -uroot -p${pass} -e "show slave status\G;" 2>/dev/null|grep 'Last_SQL_Errno' | awk -F ":" '{print $2}'`

SBM=`mysql -uroot -p${pass} -e "show slave status\G;" 2>/dev/null|grep 'Seconds_Behind_Master' | awk -F ":" '{print $2}'`
#echo $sql21
#echo $Errno
#sleep 0.5

if [ $Errno -eq 1061 ] || [$Errno -eq 1062 ] || [$Errno -eq 1217 ] || [$Errno -eq 1050 ] 
   then
	echo ${1} && echo $sql21 &&  echo $Errno
        mysql -uroot -p${pass} -e "STOP SLAVE;SET @@SESSION.GTID_NEXT =${sql21};BEGIN; COMMIT;SET @@SESSION.GTID_NEXT = AUTOMATIC;START SLAVE;"
        # mysql -uroot -p${pass} -e "STOP SLAVE;SET @@SESSION.GTID_NEXT =${sql21};BEGIN; COMMIT;SET @@SESSION.GTID_NEXT = AUTOMATIC;START SLAVE;SHOW SLAVE STATUS \G;"
        echo mysql -uroot -p${pass} -e "STOP SLAVE;SET @@SESSION.GTID_NEXT =${sql21};BEGIN; COMMIT;SET @@SESSION.GTID_NEXT = AUTOMATIC;START SLAVE;SHOW SLAVE STATUS \G;"
        
    else
       sleep 3
       echo  "同步延迟 ${SBM} 秒"
fi
done
posted @ 2018-07-17 14:56  貔貅小米豆  阅读(236)  评论(0编辑  收藏  举报