MySQL复制中的管理技巧

复制中断处理

  • 1677错误
    • 第一种将主库改成statement格式
    • 第二种在从库设置slave_type_conversions
  • 从库出现写入数据,把自增ID占用:1062错误
    • 将从库该条数据删除
    • 能确定数据一致也可以跳过该错误
  • 从库上出现少数据,delete,update操作时,找不到相应的记录,出现1032错误
    • delete可以跳过错误
    • update只能补数据,在master的binlog中解析出哪个位置出错了,将这条数据插入,主要是主键个非空列,开启复制sql_thread,查看status
  • 从库gtid值大于主库gtid值
    • 有可能主库被reset了
    • 新回复的从库将auto.cnf也恢复了,binlog还删除了,也有可能会出现这种场景

复制延迟排查

  • 搞明白当前的数据库在干什么
    • show processlist;
    • show slave status\G定位到sql_thread执行到位置
      • io_tread同步到主库的binlog位置:Master_Log_File;Read_Master_Log_Pos
      • sql_thread重放到对应主库的binlog位置:Relay_Master_Log_File;Exec_Master_Log_Pos
      • Seconds_Behind_Master:这个值是怎么计算的?
        • io_thread.event.timestamp-sql_thread.event.timestamp 单位秒
  • 查看当前的SQL状态
    • 机器负载很高
      • 索引不合理!!!
      • Statement索引不合理,造成同步延迟
        • mysql>pager more
        • mysql>show processlist;
          • 可以直接用select * from information_schema.processlist where 1=1 order by TIME desc limit 10;
          • USER不是sys的,感觉没什么问题的,时间太长快死掉的连接都可以杀掉
        • 在从库上能看到执行的SQL都有问题的!!!
  • 利用perf top 查看MySQL的调度情况
    • perf top -p `pidof mysqld`
    • 利用Google
    • 利用源码定位出问题的地方
  • 延迟现象一
    • Seconds_Behind_Master一直在增大,但Exec_master_log_pos就是卡着不动
    • 解析sql_thread执行的binlog
    • mysqlbinlog -v --base64-output=decode=rows --start-position=xxx mysql-bin.xxxx
    • 去主库上面找
      • Relay_Master_Log_File,Exec_Master_Log_Pos这两个参数定位
    • 在从库上面找
      • Relay_Log_File,Relay_Log_Pos这两个参数
    • 大事务卡的两个原因
      • 真的是大事务等待,一次更新50W行以上,坐等
      • 更新这个表可能没有索引
        • stop slave不了可以mysqladmin -S/mysql3306.sock shutdown
        • 关闭都关闭不了,只能kill -9,从库这么干,主库还是不要这么干
        • 重启mysql,重建索引,之后start slave

如何避免延迟

  • 从库的配置适当高一点
  • 使用MySQL5.7开启并行复制
  • 表结构设计时,一定要有主键,而且主键要短小
  • 使用新型硬件:PCI-E&SSD类设备
  • 程序端适当的Cache,减少数据库的压力

复制结构调整Tips

  • 场景
  • 将下图中的机器加内存,并修改buffer pool,业务暂停时间小于1分钟


  • 凌晨读写压力不大的情况下,可以考虑将读写都放在M上
  • 将s1->stop slave;
  • s1,s11,s12,s13全部shutdown,加内存,修改buffer pool
  • 在s1和M上装keepalived,vip先指到M上,之后停掉M,查看vip是否指到s1上
  • 如果是gtid复制,M启动后直接change master指到s1就可以
    • 在将s11,s13,M都指向s12就ok了
  • 如果是传统复制,要经过一下几步
    • 在vip指过去之前在s1上执行show master status;
    • sleep 1;
    • show master status;结果没变化可以将结果保存到一个文件中
    • show master status>/tmp/1.log
    • M启动后change master to指s1就可以
    • 将s11,s13,M指向s12有两种办法
    • 第一种
      • 可以手工制造个错误,使s11,s12,s13,M都停在同一个位置
      • 在s12上执行show master status>/tmp/1.log
      • 在将s11,s13,M都指向s12就ok了
      • 之后再s12上跳过错误或者将错误修复
    • 第二种
      • 将s11,s12,s13,M全部停掉sql_thread
      • 在s1上执行show master status;
      • s11,s12,s13,M执行start slave sql_thread until master_log_file='xxx',master_log_pos=xxx;
      • 使s11,s12,s13,M机器都停在s1执行的show master status节点上
      • 在s12上执行show master status>/tmp/1.log
      • 在将s11,s13,M都指向s12就ok了
  • 将级联复制改成正常的一级复制


  • gtid可以直接change过去
  • 传统复制
    • 在s1上执行stop slave;
    • 在s1上查看show slave status;
    • 在s11上的change master语句,host,port指定到M上
    • master_log_file='Relay_Master_Log_File' #在s1上执行show slave status查看出来的
    • master_log_pos=Exec_Master_Log_Pos #在s1上执行show slave status查看出来的
    • s2,s21同上
    • 将所有的从库开启复制start slave
  • 假设s1机器电源坏了,不能登录,将s11机器挂到M下或者挂到s2下,怎么搞定?
    • gtid可以直接change master过去
    • 传统复制
      • 一种是将s21机器复制一份数据将s11机器还原,挂到s2下
      • 另一种是不需要重新备份
        • 解析s21机器的mysql-binlog,找到最后一个M库的server_id对应的事务,在这个事务上面找到它对应的timestamp,开启中继日志情况下server_id和timestamp具有传递性
        • 解析出timestamp对应的时间,去M机器上面找到对应的mysql-binlog,大概找到这个时间是写入的哪个binlog,可以用ls查看
        • 解析这个binlog,在这个binlog中查找上述的timestamp,找到对应的事务,要和从库上的那个事务语句一样才可以,查看一下从库的这个事物执行完没有(commit还是没commit),如果没执行完就要记录这个事物begin之前的log_pos,执行完了就记录下一个事务开始的log_pos
        • 将上述的binlog和log_pos写入到从库的change master to上面,开启复制,就可以将机器挂到M下面了
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,829评论 1 331
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,603评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,846评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,600评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,780评论 3 272
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,695评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,136评论 2 293
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,862评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,453评论 0 229
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,942评论 2 233
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,347评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,790评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,293评论 3 221
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,839评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,448评论 0 181
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,564评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,623评论 2 249

推荐阅读更多精彩内容