mysql 主从故障恢复

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

有碰到这样的主从故障,都是yes和0,但是exec和relay相差太多,从一直卡住在update

有能力的话,深入分析relay-log和master binlog来判断到底是哪条语句,卡住了主从复制


正常的监控图

wKioL1gcAd7yPr73AAB1TpYb25g243.png-wh_50

IO thread read position

SQL thread execute position

两个必须一致

两者对应的是

Exec_Master_Log_Pos: 18036251

Read_Master_Log_Pos: 18037252


主从不同步解决方案

1.简单,慢

重新建立m-s,让它自动同步数据

stop slave;reset slave all;change master to master_host='ip', master_user='replusers',master_password='xx'

2.快速

重新mysqldump导到备库,然后建立m-s

3.pt工具,但是pt我这边支持不了mysql5.7

高端的解决办法

http://imysql.com/2014/08/30/mysql-faq-howto-monitor-slave-lag.shtml#comment-146  

[MySQL FAQ]系列 — MySQL复制中slave延迟监控

1、可以在MASTER上维护一个监控表,它只有一个字段,存储这最新最新时间戳(高版本可以采用event_scheduler来更新,低版本可以用cron结合自动循环脚本来更新),在SLAVE上读取该字段的时间,只要MASTER和SLAVE的系统时间一致,即可快速知道SLAVE和MASTER延迟差了多少。不过,在高并发的系统下,这个时间戳可以细化到毫秒,否则哪怕时间一致,也是有可能会延迟数千个binlog event的。
2、网友(李大玉,QQ:407361231)细心支出上面的计算延迟有误,应该是mysql-bin.000009的最大事件数减去已经被执行完的事件数,即1073742063 – 654409041= 419333022个binlog event,再加上mysql-bin.000010这个binlog已经产生的107374193个binlog event,共526707215 个binlog event。


优化

5.7的优化 

http://www.aikaiyuan.com/10053.html MySQL 5.7 并行复制实现原理与调优

http://www.cnblogs.com/zengkefu/p/5669887.html

slave节点开启并发复制(slave_parallel_workers=3)

MySQL 5.7 引入Enhanced Muti-threaded slaves,当slave配置slave_parallel_workers>0并且global.slave_parallel_type=‘LOGICAL_CLOCK’,可支持一个schema下,slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。但是要实现以上功能,需要在master机器标记binary log中的提交的事务哪些是可以并发执行,虽然MySQL 5.6已经引入了binary log group commit,但是没有将可以并发执行的事务标记出来。

假设当slave_parallel_workers=7时,Coordinator线程(SQL线程)分配这一组事务到worker中排队去执行。这里可以看出增加master库binary log group commit组中事务的数量可以提高slave机器并发处理事务的数量,MySQL5.7引入 binlog_group_commit_sync_delay和 binlog_group_commit_sync_no_delay_count参数即提高binary log组提交并发数量。MySQL等待binlog_group_commit_sync_delay毫秒的时间直到binlog_group_commit_sync_no_delay_count个事务数时,将进行一次组提交

MySQL 5.7 GA版本推出的 Enhanced Multi-threaded Slaves功能,彻底解决了之前版本主备数据复制延迟的问题,开启该功能参数如下:

1.  # slave机器

2.  slave-parallel-type=LOGICAL_CLOCK

3.  #slave-parallel-type=DATABASE #兼容MySQL 5.6基于schema级别的并发复制

4.  slave-parallel-workers=16 #开启多线程复制

5.  master_info_repository=TABLE

6.  relay_log_info_repository=TABLE

7.  relay_log_recovery=ON


精确方式如在master上表中定期插入timestamp,在slave上通过系统时间和timestamp进行对比得到精确slave延迟),简单看了下代码time_diff(Seconds_Behind_Master值)


stop slave;

change master to master_heartbeat_period = 10;

set global slave_net_timeout = 25;

start slave;


主从延迟

主备基本无延迟的时候,主库执行了一个大事务,你会发现备库延迟突然很大,然后又没了,"延迟跳跃"

主库的网络有问题,io thread拉binlog的速度很慢,备库实际有很大的延迟,而sql thread执行的时候永远是hot log,这个时候会出现,备库一直有很大的延迟,突然一下延迟没了(=0),接下去延迟又变得很大

到这里Seconds_Behind_Master的计算就结束了。总结起来就是Seconds_Behind_Master的计算有3部分组成,备库当前时间,sql thread最近一次执行的event的时间,主备系统的时间差。备库出现延迟跳跃,可能是大事务引起,也可能是网络问题

可能主服务器已经有大量操作,但是从服务器的io线程并没有获取到这些操作,而从机的sql线程和io线程之间时间差又为0

Seconds_Behind_Master值是slave io thread当前已经获取的master binlog pos - slave sql thread当前已经执行完的master binlog pos

设计实验来验证,用取一天前的备份集新做了一个slave,change master且start slave后,Seconds_Behind_Master显示为60000多秒(20小时左右),显然是master当前系统时间戳 - slave sql thread当前已经执行完的master binlog pos的结果


1.slave,show global variables like 'GTID_EXECUTED';

2.master上mysqldump all database

mysqldump -uroot -p'xx' --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs --socket=xx > $DumpFile

发送到slave,mysql  -uroot -p'xx' <$DumpFile

3.stopslave;reset slave 

4.change master to master_host='ip', master_user='replusers',master_password='xx', master_auto_position=1;

5.start slave;


数据库内核月报 - 2015 / 10

http://mysql.taobao.org/monthly/2015/10/05/

stop slave; set global slave_parallel_workers=0; start slave;


http://imysql.com/2014/07/31/mysql-faq-exception-replication-with-gtid.shtml?utm_source=tuicool&utm_medium=referral

[MySQL FAQ]系列 — 5.6版本GTID复制异常处理一例


碰到1236

http://www.fromdual.com/replication-troubleshooting-classic-vs-gtid

mysqldump -u root -p --all-databases --flush-privileges --single-transaction --flush-logs --triggers --routines --events -B>all.sql

--all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs --socket=/opt/3306/mysql.sock --set-gtid-purged=OFF >all.sql

head -n 50 all.sql|grep PURGED


reset master;

start slave;

这步启动有可能会报错


如果还不行,就去master上reset master,重新生成uuid


1236

参考 http://suifu.blog.51cto.com/9167728/1845457

使用:slave_exec_mode参数

slave-skip-errors = 1062,1032,1060

end_log_pos 有了它,根据pos值,直接就能找到,找到delete那条数据,反做(变成insert)


SET @@GLOBAL.GTID_PURGED = 'b30dcce8-3395-11e6-902b-0050569d58f6:1-24414141';

begin;commit;

set session gtid_next=automatic;

start slave;


其他的一些资料


参考 http://ylw6006.blog.51cto.com/470441/1788809

注意

1.事物不能跳过的,绝对不能跳过

2.master上不要使用reset master,如果操作,slave不知道从哪里位置进行同步。因为master删除了的gtid不是以前的同步的位置了。

3.slave使用reset slave之前必须使用stop slave 命令将复制进程停止

执行reset slave,其实是把master.info和relay-log.info文件给删除,但里面的同步信息还在,那么可以用这个方法,让其清除的彻彻底底

reset slave all;


以下的问题属于比较基本的主从恢复,深入就要学习分析relay-log和binlog


在使用Mysql的主从复制架构中,有两个比较头疼的问题:

1、主从数据不同步后如何处理

2、主从同步延迟问题如何解决

 

本文将根据实际案例来分析下问题1,至于问题2多数文档介绍的办法是启用多线程复制来解决,言归正传,这里的问题1还可以细分成两种情况。

1、Slave_IO_Running和Slave_SQL_Running在YES情况下,主从数据不同步如何处理?

2、Slave_SQL_Running在NO情况下,主从数据不同步如何处理?


这个办法其实回答了前面的问题1,Slave_IO_Running和Slave_SQL_Running在YES情况下,主从数据不同步如何处理

# yum -y install perl-TermReadKey 

# wget ftp://ftp.netbsd.org/pub/pkgsrc/distfiles/maatkit-7540.tar.gz

# tar -zxvpf maatkit-7540.tar.gz 

# cd maatkit-7540

# perl Makefile.PL 

# make && make install

# mk-table-checksum h=192.168.115.6,u=root,p=123456,P=3306  \

h=192.168.115.7,u=root,p=123456,P=3306 -d test | mk-checksum-filter

# mk-table-checksum h=192.168.115.6,u=root,p=123456,P=3306 \

h=192.168.115.7,u=root,p=123456,P=3306 -d test

如果主从数据不一致则采用mk-table-sync进行数据同步

# mk-table-sync --execute --print --no-check-slave --transaction --databases test  \

h=192.168.115.6,u=root,p=123456 h=192.168.115.7,u=root,p=123456


采用跳过这个事务的办法来处理主从数据库不同步问题


在未启用GTID复制的情况下采用下面的方法跳过事务:

mysql>slave stop; 

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  //跳过一个事务 

mysql>slave start;

启用GTID

在show slave status \G;输出中的最后几条里面,

Retrieved_Gtid_Set项:记录了relay日志从Master获取了binlog日志的位置

Executed_Gtid_Set项:记录本机执行的binlog日志位置(如果是从机,包括Master的binlog日志位置和slave本身的binlog日志位置)

我们要跳过事务的GTID在错误日志中有记录

# tail -f /home/mydata/localhost.localdomain.err

mysql> set session gtid_next='bd9e9912-2bc7-11e6-bade-000c29b8871c:1440';

mysql> begin;commit;

mysql> set session gtid_next=automatic;

mysql> start slave;

mysql> show slave status \G;

验证从库数据是否和主库一致

mysql> select * from test.asm_user;

前面模拟了Slave_SQL_Running在NO情况下,主从数据不同步情况的处理过程,在现实的环境中,往往情况要复杂的多,下面分享一则内存开发库因为断电导致主从数据不一致的故障处理:

1、因为电源故障,导致主从数据库全部宕机,电源恢复后,主库启动正常,从库无法启动,通过分析日志发现可能是电源故障导致从库的固态盘异常,许多的binlog文件权限出现???,这些文件甚至无法正常查看

1、通过fsck -y进行文件系统校验修复坏块,修复完成后从库数据库可以启动,但开启复制进程的时候报中继日志丢失

2、在没有办法的情况下,采用主库dump数据,从库重新source的办法在线重做主从数据同步。整个操作过程中,主库的数据不断的写入。

下面是大致的步骤:

3.1、主库导出全库数据,注意一定要使用--single-transaction参数

# /usr/local/mysql/bin/mysqldump --all-databases --single-transaction --triggers --routines > /tmp/1.sql

3.2、将备份文件拷贝到从库进行source

3.3、开启从库的复制进程

mysql> change master to master_host='192.168.1.15',

master_user='rep1',master_password='123456',MASTER_AUTO_POSITION=1;

mysql> start slave;


0.Big transaction and MySQL replication lag

After running the script, I do really found something astonishing, during that time the master had one transaction per minute, and each was comparatively big, nearly 10k delete operations for one transaction

After the discussion with developer, we decided to stop the job. Besides that, we consider to increase the thread(slave_parallel_worker) of slave since we currently use MySQL 5.7 which is its big selling point.

解决方法:Identifying useful info from MySQL row-based binary logs


参考 http://www.cnblogs.com/xiaoyanger/p/5576652.html


1.Got fatal error 1236 from master when reading data from binary log: 'unknown error reading log event on the master; the first event '' at 4, the last event read from '/opt/3306/mysql-bin.000001' at 308, the last byte read from '/opt/3306/mysql-bin.000001' at 308.'

在source那边,执行:

flush logs;

show master status;

记下File, Position。

在target端,执行:

CHANGE MASTER TO MASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=107;

slave start;

show slave status \G

一切正常。

2.

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica'

3.Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'


4.MySQL master 宕机导致slave数据比master多的case


5.reset master导致主从GTID不一致的处理方法

为什么从库的GTID会比主库的小呢?应该是有同学在从库做了reset master的操作,重置了GTID导致的

由于这是从从库同步到主库的信息,而从库是只读的,只会产生备份的临时信息。因此这些事务都可以跳过。但由于从库的GTID比主库上的小,跳过了1413号事务,后面还会有更多的类似的问题。因此还需要在从库上将事务号推高到跟主库一致的水平,这样后面从库产生的事务号都不会再跟主库上的有冲突。推高事务号可以通过执行空事务达到:

set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:1421;

begin;commit;

set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:1422;

begin;commit;

…………………………………

set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:7383;

begin;commit;


set gtid_next='AUTOMATIC';

检查主库relaylog,发现7388、7389这两个事务主库是有获取到的,但7388看起来跳过没执行,为什么7389这次执行却没报错呢?

检查主库的binlog。

可以发现:7388的确是被跳过了,而7389这个本该是insert的却变成了空事务!

这个问题比较坑,看了下文档,mysql 在row format的情况下,对于temporary tab的处理情况如下:

1、不进行tempory table 复制(这个应该是指创建)

2、从上面的binlog中也可以看到,对于temporary tab 的dml操作,在复制的时候,用空事务代替。


6.大坑:用SQLyog连mysql的部分操作不能同步到从库

解析日志发现relaylog里面有相关的alter table的操作,但是从库就是没有执行。比较奇怪的是另外那个从库很正常。

通过这次的问题,发现用工具连数据库还是会有很多隐患,要从根本上解决这个问题只能严格控制开发权限,只给他们select权限。

dba平时操作数据库的时候尽量在命令行操作


7.GTID复制报错处理:Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query

去查看主库上的binlog日志,查找drop database test相关的日志

要理解GTID代表的是什么,每个事务的提交都代表着一个GTID的生成,正如其全名:全局事务ID(global transaction identifier),所以如果想跳过导致错误的事务不执行的话,需要找到对应事务的gtid号,设置(set gtid_next="....")并提交空事务后重新启用自动模式后,再重启slave就可以,并不是每个导致错误的事务都是binlog中最后一个事务

理解了这个后就能明白,之前的处理还是太复杂,其实直接看show slave satus\G的结果,看这两个参数的值:

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test'

Retrieved_Gtid_Set: 988b8684-3e21-22e6-a801-24505689c77d:1-9

Executed_Gtid_Set: 

能够看到,Executed_Gtid_Set是空的,而且GTID是肯定开启了的,所以,说明日志传过来后压根还没开始执行,所以,第一个事务就已经被卡住了,首先应该跳过的事务号就是1,也不必再去看日志了

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next="988b8684-3e21-22e6-a801-24505689c77d:1";

Query OK, 0 rows affected (0.00 sec)

mysql> begin;commit; 

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> set gtid_next="AUTOMATIC";

Query OK, 0 rows affected (0.00 sec)

mysql> start slave; 

Query OK, 0 rows affected (0.00 sec)

8.Error 'Cannot add or update a child row: a foreign key constraint fails

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

mysql>start slave;

接下来就是主从数据不一致的问题,可以使用pt-table-checksum来检查下不一致的数据,再进行同步,具体步骤如下

在主库执行:


mysql>GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'USER'@'MASTER_HOST' identified  by 'PASSWORD';

注:创建用户,这些权限都是必须的,否则会报错


shell> ./pt-table-checksum --host='master_host' --user='user' --password='password' --port='port' --databases=zabbix --ignore-tables=ignore_table --recursion-method=processlist 

注:(1)因为涉及到的表太多,查看后发现很多表都有外键关联,错综复杂,而且因为是监控表,即使丢失一些也没什么关系,所以查出较大的且没有外键关联的表用ignore-tables选项排除,对其他表进行比对,如果表比较少的话直接指定--TABLES

    (2)recursion-method如果不设的话,会报错:Diffs cannot be detected because no slaves were found.  其参数有四:processlist/hosts/dsn/no,用来决定查找slave的方式是show full processlist还是show slave hosts还是直接给出slave信息,具体用法在另一随笔pt-table-checksum介绍中详述


shell>./pt-table-sync --print --replicate=percona.checksums h=master_host,u=user,p=password,P=port h=slave_host,u=user,p=password,P=port --recursion-method=processlist >pt.log

注:最好使用--print,不要直接使用--execute,否则如果弄出问题,就更麻烦了,打印出直接执行的语句,去从库执行就好了

复制代码

将pt.log传到从库,直接执行,然后再次在主库上进行一致性检查,如果还有不一致的数据,记得登录mysql去把checksums表清空,然后再次进行检查同步,直到没有不一致的数据




本文转自 liqius 51CTO博客,原文链接:http://blog.51cto.com/szgb17/1828390,如需转载请自行联系原作者

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5月前
|
关系型数据库 MySQL 开发工具
MySQL5.7主从配置(Docker)
MySQL5.7主从配置(Docker)
792 0
|
5月前
|
Kubernetes Cloud Native 关系型数据库
提升数据安全与性能,掌握Helm一键部署MySQL 8.0主从技巧
【4月更文挑战第9天】提升数据安全与性能,掌握Helm一键部署MySQL 8.0主从技巧
328 0
|
5月前
|
Ubuntu 关系型数据库 MySQL
使用Ubuntu和Windows电脑实现Mysql主从同步(详细操作步骤)
使用Ubuntu和Windows电脑实现Mysql主从同步(详细操作步骤)
87 2
|
5月前
|
SQL 关系型数据库 MySQL
解决MySQL主从慢同步问题的常见的解决方案:
解决MySQL主从慢同步问题的方法有很多,以下是一些常见的解决方案: 1. 检查网络连接:确保主从服务器之间的网络连接稳定,避免网络延迟或丢包导致数据同步缓慢。 2. 优化数据库配置:调整MySQL的配置参数,如增大binlog文件大小、调整innodb_flush_log_at_trx_commit等参数,以提高主从同步性能。 3. 检查IO线程和SQL线程状态:通过SHOW SLAVE STATUS命令检查IO线程和SQL线程的状态,确保它们正常运行并没有出现错误。 4. 检查主从日志位置:确认主从服务器的binlog文件和位置是否正确,避免由于错误的日志位置导致同步延迟。 5.
963 1
|
5月前
|
SQL 存储 关系型数据库
MySQL的主从复制&主从同步
MySQL的主从复制&主从同步
85 0
|
5月前
|
SQL 关系型数据库 MySQL
MySQL-主从架构的搭建
MySQL-主从架构的搭建
170 0
|
4月前
|
Prometheus 监控 关系型数据库
数据库同步革命:MySQL GTID模式下主从配置的全面解析
数据库同步革命:MySQL GTID模式下主从配置的全面解析
435 0
|
3月前
|
存储 关系型数据库 MySQL
利用 MySQL 克隆插件搭建主从
MySQL 的 Clone 插件是一个强大的功能,首次引入于 MySQL 8.0.17 版本。简单来说,Clone Plugin 是一款物理克隆数据工具,它能够帮助我们快速、高效地克隆或复制数据库,极大地简化了数据库迁移、备份和恢复的过程,让我们在处理大量数据时更加得心应手。本篇文章我们一起来学习下如何使用克隆插件。
70 2
|
3月前
|
运维 关系型数据库 MySQL
【实操记录】MySQL主从配置
本文使用MySQL原生支持的主从同步机制,详细记录了配置步骤及运维操作方法,可供大家直接参考、使用。 本文假设已经部署了两台主机的MySQL软件,且数据库服务正常,详细部署步骤可本站搜索:"mysql二进制安装包部署"
112 0
|
3月前
|
SQL 关系型数据库 MySQL
【MySQL】主从异步复制配置
【MySQL】主从异步复制配置
69 1