1. 首页
  2. >
  3. 数据库技术
  4. >
  5. MySQL

CENTOS MYSQL复制下slave严重延迟的分析和处理

昨晚在生产环境刷数据,执行了一个全局表的update table_test set XK = 'PT' where (XK = '' or XK is null);当时语句没啥问题,测试了下,执行单库的时间在6s左右,没太在意,结果今天发现所有的从库严重延迟,全都卡在这条事务的binlog回放上了。


分析原因:

尽管我们使用的是基于组的多线程复制,线程配置为4个,但是他对无序innodb表(堆)或者大事务的回放是非常慢的。我们日常工作种应该避免这个问题。很不幸,这个表publicity_licensing完全符合了堆表的概念,无主键、无唯一索引,完全就是一个堆,无论任何操作都是性能非常差的。在复制过程种更是导致了从库hang了。


解决思路:

1、 重做主从,很明显,100多个G的数据重做耗时非常长;

2、 想办法跳过该事务,然后再关闭会话级binlog的情况下,手动执行数据的更新。很明显,第二个方法可行,也是最不耗时的解决办法。


解决步骤:

1、 关闭session级binlog写入:set session sql_log_bin = off; 并确认是否真正关闭;

2、 从机每个库分别手动执行表的更新语句。退出

3、 从机上重新登陆mysql,执行stop salve;

4、 从机上杀掉正在执行的binlog回放线程;

5、 从机上show slave status \G 找到执行的binlog文件及Executed_Gtid_Set项,找出已经执行的ID

CENTOS MYSQL复制下slave严重延迟的分析和处理

CENTOS MYSQL复制下slave严重延迟的分析和处理

6、 在对应的主库上解析binlog(从每个库的数据分析,这个数据更新语句执行到哪里了,用—database参数会使产生的数据文件小很多,便于查找文本)mysqlbinlog /data/mysql/log/mysql-bin.011814 --database=egovmana14 -vv -re14.sql

7、 在e14.sql里面查找第5步找到的ID + 1:410638991 可以看到这个ID正式卡住复制的语句的event id

CENTOS MYSQL复制下slave严重延迟的分析和处理

8、 从机上登陆mysql,执行:set @@session.gtid_next='60ac9a7e-7690-11e8-9a55-fa164e82c979:410638991';目的是手动更新当前会话同步的下一个gtid;

9、 执行空事务以替代这个gtid的事务:begin;commit;

10、 从机上执行:set @@session.gtid_next=AUTOMATIC;(此时我们已经跳过了410638991这个事件),目的是让复制的sql线程自动找位置去开始复制;

11、 从机上执行start slave;

12、 因为是全局表,在继续追复制进度的过程种,会马上又遇到其他库执行的这个update语句(现象为刷show slave status \G,显示的Executed_Gtid_Set编号数字不会刷新增加)。那么重复步骤3-11;直至每个库的update处理完成;

13、 刷show slave status \G,查看Executed_Gtid_Set刷新的情况,确认处理完成。