忽略slave某些从库报错

xiaoxiao2021-02-28  22

某些情况下需要忽略主从同步中某些错误,例如此处:

主库mysql5.6,从库mysql5.7,mysql.user表结构不同,在主库修改用户密码,会导致从库报错,因为5.7没有password字段 1.MySQL5.7中mysql.user表比MySQL5.6少了1个字段(Password)  2.MySQL5.7中mysql.user表比MySQL5.6多了3个字段(password_last_changed,password_lifetime,account_locked)

 

1、  从库上忽略此种错误

还可以在my.cnf中设置如下选项自动跳过这种错误. slave-skip-errors=1062,1677

2、  忽略这一次的错误

 

#stop allslaves

stop slave;

 

# set skip counter

setglobal sql_slave_skip_counter=1;

 

# start slave that shall skip one entry

start slave forchannel 't10';

 

setglobal sql_slave_skip_counter=0;

 

# start all otherslaves

start slave;

 

二:

也可以直接停掉错误的channel,再skip:

stop slave forchannel 't10';

 

setglobal sql_slave_skip_counter=1;

 

start slave forchannel 't10';

 

 

master 上,执行 mysql> SHOW BINLOG EVENTS in'mysql-bin.000010' from 46755013;

 

查询变量

Show variableslike ‘slave_%’;

Set globalsql_slave_skip_counter =1

 

 

根据报错binlog所在的位置,将二进制日志转化成可读的日志:

mysqlbinlog --no-defaults -v -v--base64-output=DECODE-ROWS /mysqldata/mysql3306/binlog/mysql-bin.000208 |grep-A '20' 230407775

转载请注明原文地址: https://www.6miu.com/read-2800237.html

最新回复(0)