欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql数据库主从同步中断的各种情况

程序员文章站 2022-03-09 20:38:26
主库上修改用户权限原因1:在主库上执行drop user 或者授权操作时,导致的从库上报错,停止恢复主库的binlog解决方法:重启从库同步,跳过出错语句在从库上执行,mysql> stop slave;mysql> set sql_slave_skip_counter=1;mysql> start slave;之后,检查从库是否可以正常同步。注意:在主库上对用户进行操作/授权时,进入mysql提示符后,不要指定具体的数据库,可以避免这类情况发生。sql_slav....
  1. 主库上修改用户权限
    原因1:在主库上执行drop user 或者授权操作时,导致的从库上报错,停止恢复主库的binlog

解决方法:重启从库同步,跳过出错语句在从库上执行,

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> start slave;

之后,检查从库是否可以正常同步。

注意:在主库上对用户进行操作/授权时,进入mysql提示符后,不要指定具体的数据库,可以避免这类情况发生。

sql_slave_skip_counter以event为单位skip,直到skip完第N个event所在的event group才停止。对于事务表,一个event group对应一个事务;对于非事务表,一个event group对应一条SQL语句。一个event group包含多个events。
这里我只针对显式事务模拟insert遇到Duplicate entry(1062错误),知道了问题本质,delete/update中的1032错误类似去分析
set global sql_slave_skip_counter = N
This statement skips the next N events from the master.
(即是跳过N个events,这里最重要的是理解event的含义!在mysql中,对于sql的 binary log 实际上是由一连串的event组成的一个组,即事务组。)

在备库上设置 global sql_slave_skip_counter =N 会跳过当前时间来自于master的之后N个事件,这对于恢复由某条SQL语句引起的从库复制有效. 此语句只在当slave threads是停止时才有效,否则将发生一条错误…每忽略一个事件,N 减一,直到N减为0!

当然,你也可以在对用户权限等进行操作时,强制不记录binlog,即

mysql> set session sql_log_bin=0;

mysql> grant …..;/drop user …;

mysql> set session sql_log_bin=1;

========================

  1. 主从版本不同
    原因2:主从mysql不同版本(例如主库5.0,备库4.0)导致在备库上某些恢复无法进行,因而报错,停止恢复主库binlog

解决方法:

首先到从库上记录下出现问题的语句,具体是insert/update/delete/DDL;

在从库上手动执行该语句,根据版本不同可能会稍微修改一下(注意从库是read-only,需要root用户权限执行);

在从库上跳过出错的语句,继续同步:

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> start slave;

========================

  1. 主库负载压力大
    原因3:主库IO压力大,没有及时向从库发送binlog或者响应从库的连接请求,从库重试一定次数(master-connect-retry)/超时(slave-net-timeout)后,与主库连接断开

解决方法:先停止一下从库同步,停一段时间后(主库IO压力没那么大),再起同步

mysql> stop slave;

some time later…

mysql> start slave;

========================

  1. 数据库bug
    原因4:由于mysql的某些bug导致的中断,这种情况可能的原因不确定

解决方法:重启一下从库的同步

(注意:该方法可以解决大部分主从同步异常,遇到问题时可以首先尝试一下,如果不行再使用其他方法解决)

mysql> stop slave;

mysql> start slave;

========================

  1. 主库上的中断操作
    原因5:主库上中断了某个操作(比如load data),可能会导致主从不一致,从库中断同步

解决方法:

首先需要确认之前的操作在主库上确实已经停止;

然后对比主从上该表的数据是否一致,绝大部分情况主从数据会不一样,通过检查从库上出现的错误,执行

mysql> show slave status\G;

mysql> stop slave;

mysql> set sql_slave_skip_counter=1; 
   //注意,若无错误,无需执行这一步

mysql> start slave;

========================

  1. 主从出现不同错误号
    原因6:主从库出现不同的错误号,出现这种情景的原因不确定

解决方法:可以通过重启从库同步,若不行再看show slave status打印的错误信息,进行跳过

mysql> stop slave;

mysql> start slave;

========================

  1. 主从数据不一致
    原因7:在主库上操作了从库上没有的表或者行数据,导致从库报错,中断同步

解决方法:重启主从同步,跳过出错的语句,但要注意一定要逐个跳过,否则会有问题

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> start slave;

========================

  1. 主从数据库不一致
    原因8:主库上有A,B两个库,但从库上只有B库,且从库上没有设置Replicate_Ignore_DB,当主库操作A库时,会导致从库报错,中断同步(此情况与原因7很类似)

解决方法:确认不同步A库的话,可以在从库上设置Replicate_Ignore_DB或者Replicate_Wild_Ignore_Table,之后重启从库mysql实例:

在从库的配置文件my.cnf中添加:

replicate-wild-ignore-table = A.%

或者

replicate-ignore-DB = A

重启从库mysql实例

========================
MySQL主从复制中replicate-ignore-db replicate-wild-ignore-table的应用

replicate-ignore-db
replicate-wild-ignore-table
官方的解释是:在主从同步的环境中,replicate-ignore-db用来设置不需要同步的库。生产库上不建议设置过滤规则。如果非要设置,那就用Replicate_Wild_Ignore_Table:

在实际生产主从复制环境中,配置Replicate_Wild_Ignore_Table:mysql.% 主库账户写,从库账户读

===================

  1. 主库的binlog损坏
    原因9:主库向从库传输的binlog包损坏,导致从库恢复到某个sql时无法进行,中断同步

解决方法:在从库上,记录同步点,并重新同步

mysql> stop slave;

mysql> change master to master_Log_File=‘mysql-bin.000001’,master_log_pos=488;

mysql> start slave;

注意:master_Log_File取自show slave status中Master_Log_File字段,master_log_pos取自Exec_Master_Log_Pos字段。

========================

  1.   从库SQL执行不通过
    

原因10:主库执行正常的SQL在从库上执行报错,类似Error ‘You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1’ on query. Default database: ‘’. Query: 'UPDATE FC_Word.wordext2 (ignored)

发生错误的SQL,手动在从库上执行则成功。这是mysql一个bug,至今未解决。

Bug相关链接:http://bugs.mysql.com/bug.php?id=31686

解决方法:可以先试着让从库从失败的位置再执行一次,如果仍不能通过,可以DBA手动执行。

1)重指向错误点恢复:

mysql> stop slave;

mysql> change master to master_Log_File='mysql-bin.000001',master_log_pos=488;

mysql> start slave;

2)DBA手动执行步骤:

mysql> stop slave;

mysql> set sql_slave_skip_counter=1;

mysql> update/insert/delete statement

mysql> start slave;

========================

  1.  从库上大事务产生的阻塞
    

原因11:对于某些允许前端业务写操作的从库(不推荐),从主库同步过来的SQL或者存储过程等与从库本身的事务产生阻塞等待资源释放(如表,行锁),超过等待时间后,从库与主库的同步中断

解决方法:若从主库同步的SQL或者存储过程先执行,则没有问题,不会影响主从同步;

若从库上的事务先执行且执行时间较长,会影响从库的同步,可以:

mysql> stop slave;

mysql> start slave;

========================

  1.  从库上存储过程执行报错
    

原因12:对于存储过程和触发器等,在主库上执行正常,从库执行报错,类似:

Last_Error: Error ‘Explicit or implicit commit is not allowed in stored function or trigger.’ on query. Default database: ‘’. Query: ‘update FC_Word.ideainfo1 set touch = touch+1, moduid = 2745112, modtime = ‘2011-07-20 12:08:08’ , ilong = 0 , ideastat3 = ((ideastat3|1)&(~4)) , ideastat4 = (case when ideastat4&8=8 then ideastat4&7 else ideastat4 end) where ideaid = 174568837 and isdel=0’

解决方法:重启一下slave同步就可以了

mysql> stop slave;

mysql> start slave;

========================

本文地址:https://blog.csdn.net/shunnianlv/article/details/108983251