MySQL 传统复制中常见故障处理和结构优化案例分析

虽然MySQL5.7 的主从复制已经很稳定了,但在备库可读写的情况下,总是会出现部分数据不一致的情况,例如常见的1062、1032和1050错误。下面就介绍下这类报错的常见处理方法和常见主从复制结构的调整。

环境描述

  • 1、mysql 5.7 以上,
  • 2、binlog format 是row格式(5.7默认)
  • 3、传统复制(生产强烈推荐使用gtid)
  • 4、log-bin , log_slave_updates 开启
  • 5、复制结构:101:3306> 103:3306 > 104:3306

常见主从复制报错

1、表重复错误: 1050

从库已经有T2表,再在主库上创建T2. 处理原则:以主库为准,在从库上drop t2。 然后重启slave。

注意: 在db里的操作都会记录到binlog中,如果不想被记录到binlog中,可以先set sql_log_bin=0.drop完成后,再 set sql_log_bin=1即可。

从5.7 开始,有super read only。

处理方法:

从库操作:

set sql_log_bin=0;

drop table t2;

set sql_log_bin=1;

start slave;

show slave status;

2、主键冲突: 1062

处理方法:

从库操作:

set sql_log_bin=0;

delete from t2 where id =2;

set sql_log_bin=1;

start slave;

show slave status;

3、主库上更新后,从库找不到记录 :1032

这时需要解析主库的binlog,把从库的数据补回来。

这里就能看到从库丢失的那条记录。然后在从库补充这条记录即可。

处理方法:

从库操作:

set sql_log_bin=0;

insert into t2 (id) values (2);

set sql_log_bin=1;

start slave;

show slave status;

4、主库上delete后,从库找不到记录: 1032

想看某段pos内执行过的sql: 主库执行:

mysqlbinlog --base64-output=decode-rows -v --start-position=2465 
--stop-position=2748 mysql-bin.000050 > 50.sql

输出如下:

### DELETE FROM `wubx`.`wubx`
### WHERE
###   @1=2###   @2='wubx'
ROLLBACK /* added by mysqlbinlog */ /*!*/; 

注意这里的rollback。如果以后基于binlog和时间点的恢复。这条数据会被rollback掉,造成一条数据的丢失。所以如果想保留这条数据,需要找到commit的位置,或者下个pos的位置。

处理方法:

从库操作:

slave stop;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务

slave start

常见复制结构调整

1、一主一从,添加从库

st=>operation: M 101:3306
e=>end
op=>operation: S1 103:3306
 
st->op->op1

调整为,级联或星型结构

st=>operation: M 101:3306
e=>end
op=>operation: S1 103:3306
op1=>operation: S11 104:3306
 
st->op->op1

2、级联复制调整

从103.3306 dump数据

mysqldump --single-transaction --master-data=2 -uroot -p123456  -A -S 
/tmp/mysql3306.sock

104 导入数据

mysql -S /tmp/mysql3306.sock -uroot -p123456 < /tmp/1203.sql

change 104 到103

change master to master_host='192.168.56.103', master_user='repl', 
master_password='repl4slave', master_port=3306, 
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4138, 
master_connect_retry=10;

101 插入测试数据

insert into t2 values (200);

101 持续insert

for ((i=1;i<=1000000;i++))
do

mysql -S /tmp/mysql3306.sock -uroot -p123456 -e 
"insert into enmo.t2 values($i)"done

关闭103 主机,并检查104 slave 状态

主从的binlog 都会记录主库的server id 和timestamp信息。可以根据这2个信息去定位相应的pos信息。

101:3306

104:3306

这里可以看到101 结束 insert 1419后,并commit的 pos 是387204,所以104 change 的pos 可以选择到387204这里。但是如果104没有把1419 这条记录commit的话,就要选择101 开始 insert 1419 这个事务之间的pos:387020.

104 change 到101

change master to master_host='192.168.56.103', 
master_user='repl', master_password='repl4slave', 
master_port=3306,  MASTER_LOG_FILE='mysql-bin.000093', 
MASTER_LOG_POS=387204, master_connect_retry=10;

作者介绍

张灿 云和恩墨技术顾问

2015年12月加入云和恩墨,擅长oracle、mysql数据库,shell、python脚本。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-12-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户2442861的专栏

关于muduo网络库的注解

http://blog.csdn.net/liuxuejiang158blog/article/details/17056537#comments

491
来自专栏开发与安全

muduo网络库学习之muduo_inspect 库涉及到的类

muduo inspect 库通过HTTP方式为服务器提供监控接口, 现在只实现进程相关信息的监控,通过成员ProcessInspector 实现。 Pro...

1865
来自专栏沃趣科技

ASM 翻译系列第十三弹:ASM 高级知识 - Forcing the issue

原作者:Bane Radulovic 译者: 庄培培 审核: 魏兴华 DBGeeK社群联合出品 Forcing the issue ASM中有部分的...

3705
来自专栏JackeyGao的博客

五个很实用的Django 项目推荐

很多Django的包都能很大的增加我们的开发效率或者增加我们项目的功能, 比如: django-rest-framework 、wagtail, 它们带来了很棒...

793
来自专栏祝威廉

Structured Streaming如何实现Parquet存储目录按时间分区

StreamingPro现在支持以SQL脚本的形式写Structured Streaming流式程序了: mlsql-stream。不过期间遇到个问题,我希望按...

651
来自专栏FreeBuf

Shellcode与加密流量之间的那些事儿

在这篇文章中,我们将简单介绍如何在通过TCP通信的位置无关代码(PIC)中实现数据加密。

732
来自专栏吴伟祥

JPush API Java Library 原

$master_secret = 'a36fef7cbd5b47b74c20278c'; $app_key='1d2bd69d09c3756fa20f9c96...

732
来自专栏有趣的Python

Scrapy分布式爬虫打造搜索引擎 - (三)知乎网问题和答案爬取Python分布式爬虫打造搜索引擎

Python分布式爬虫打造搜索引擎 基于Scrapy、Redis、elasticsearch和django打造一个完整的搜索引擎网站 三、知乎网问题和答案爬取 ...

44210
来自专栏决胜机器学习

Redis专题(七)——Redis高可用(哨兵篇)

Redis专题(七) ——Redis高可用(哨兵篇) (原创内容,转载请注明来源,谢谢) redis2.8开始,提供稳定的哨兵,对redis的主从数据...

3336
来自专栏小曾

.Net高级进阶,教你如何构建企业模型数据拦截层,动态控制字段验证

如图:现在你要在控制器里面判断,账号名称、密码、邮箱不能为空,并且名称和密码不超过16位。

931

扫码关注云+社区