重庆分公司,新征程启航

为企业提供网站建设、域名注册、服务器等服务

mysql主从错误怎么办 mysql57主从

故障分析 | MySQL 从机故障重启后主从同步报错案例分析

MySQL 从库所在主机故障重启后,sql_thread 线程报错:

创新互联坚持“要么做到,要么别承诺”的工作理念,服务领域包括:成都做网站、网站建设、企业官网、英文网站、手机端网站、网站推广等服务,满足客户于互联网时代的宜丰网站设计、移动媒体设计的需求,帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴!

通过报错信息可知,worker 线程在回放事务 '471c2974-f9bb-11eb-afb1-52540010fb89:88313207' 时,由于要插入的记录主键冲突报错。

主机重启前,主从同步正常,主机重启后,主从同步由于主键冲突报错,对比了冲突主键所在行记

录在主从库是一致的,初步分析事务 '471c2974-f9bb-11eb-afb1-52540010fb89:88313207' 在主机故

障前已经在从库进行了回放,那为何事务会重复回放呢?

在开启gtid模式下,如果指定 master_auto_position=1,start slave 时,从库会把

Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集发送给主库,主库将收到的并集和自己的

gtid_executed 比较,把从库 gtid 集合里缺失的事务全都发送给从库。

主机重启后,事务重复回放,表明 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集中有 GTID 事务

丢失,导致重复获取事务执行引发主键冲突错误。Retrieved_Gtid_Set 和 Executed_Gtid_Set 均为内存变

量,MySQL 重启后,Retrieved_Gtid_Set 初始化为空值,从而推断出 Executed_Gtid_Set 有 GTID 事务丢

失。

Executed_Gtid_Set 来源于 gtid_executed 变量,gtid_executed 变量持久化介质有

mysql.gtid_executed 表和 binlog ,其中 mysql.gtid_executed 表是 MySQL 5.7 后引入的,在 MySQL 5.6 中,从库要使用 GTID ,必须要先设置 log_bin=on,log_slave_updates=on ,因为从库执行过的 GTID 只保留在 binlog 中。

gtid_executed 变量值陈旧,推断出 binlog 未实时持久化,我们看一下参数 sync_binlog :

通过以上分析,此次故障来龙去脉就清楚了:

Worker 线程报 1062 主键冲突错误 -- gtid_executed 信息陈旧 -- binlog 未实时持久化

搭建一主一从测试环境,通过 sysbench 模拟主库并发插入,从库主机暴力关机后,故障复现:

既然错误原因是事务重复执行,那跳过错误就好了,有如下两种方式,根据需要选取其中一种方式执行:

如果最新 binglog 丢失的 GTID 较多,手工执行比较繁琐,需要不断试错。可写一个存储过程批量执行:

待主从同步正常后,再取消参数 slave_skip_errors 设置重启 MySQL 。

mysql 如果出现主从数据不一致情况怎么弄

用 pt-table-checksum 时,会不会影响业务性能?

实验

实验开始前,给大家分享一个小经验:任何性能评估,不要相信别人的评测结果,要在自己的环境上测试,并(大概)知晓原理。

我们先建一对主从:

然后用 mysqlslap跑一个持续的压力:

开另外一个会话,将 master 上的 general log 打开:

然后通过 pt-table-checksum 进行一次比较:

查看 master 的 general log,由于 mysqlslap 的影响,general log 中有很多内容,我们找到与 pt-table-checksum 相关的线程:

将该线程的操作单独列出来:

操作比较多,我们一点一点来说明:

这里工具调小了 innodb 锁等待时间。使得之后的操作,只要在 innodb 上稍微有锁等待,就会马上放弃操作,对业务影响很小。

另外工具调小了 wait_timeout 时间,倒是没有特别的作用。

工具将隔离级别调整为了 RR 级别,事务的维护代价会比 RC 要高,不过后面我们会看到工具使用的每个事务都很小,加上之前提到 innodb 锁等待时间调到很小,对线上业务产生的成本比较小。

RR 级别是数据对比的基本要求。

工具通过一系列操作,了解表的概况。工具是一个数据块一个数据块进行校验,这里获取了第一个数据块的下边界。

接下来工具获取了下一个数据块的下边界,每个 SQL前都会 EXPLAIN 一下,看一下执行成本,非常小心翼翼。

之后工具获取了一个数据块的 checksum,这个数据块不大,如果跟业务流量有冲突,会马上出发 innodb 的锁超时,立刻退让。

以上是 pt-table-checksum 的一些设计,可以看到这几处都是精心维护了业务流量不受影响。

工具还设计了其他的一些机制保障业务流量,比如参数 --max-load 和 --pause-file 等,还有精心设计的数据块划分方法,索引选择方法等。大家根据自己的情况配合使用即可达到很好的效果。

总结

本期我们介绍了简单分析 pt-table-checksum 是否会影响业务流量,坊间会流传工具的各种参数建议或者不建议使用,算命的情况比较多,大家都可以用简单的实验来分析其中机制。

还是那个观点,性能测试不能相信道听途说,得通过实验去分析。

mysql主从复制发生误操作怎么恢复

mysql主从同步常见异常及恢复方法

1. 一般的异常只需要跳过一步即可恢复

slave stop;

SET GLOBAL sql_slave_skip_counter = 1;

slave start;

2.断电导致主从不能同步时,通主库的最后一个bin-log日志进行恢复

在主库服务器上,mysqlbinlog mysql-bin.xxxx binxxxx.txt

tail -n 100000 binxxxx.txt tail-binxxxx.txt

vim tail-binxxxx.txt 打开tail-binxxxx.txt文件找到最后一个postion值

然后在从库上,change host to 相应正确的值

slave stop;

change master to master_host='ip', master_user='username', master_password='password', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx;

slave start;

show slave status\G;

3.主键冲突、表已存在等错误代码如1062,1032,1060等,可以在mysql主配置文件指定

略过此类异常并继续下条sql同步,这样也可以避免很多主从同步的异常中断

[mysqld]

slave-skip-errors = 1062,1032,1060

关于mysql 主从复制的错误

项目上 MySQL 还原 SQL 备份经常会碰到一个错误如下,且通常出现在导入视图、函数、存储过程、事件等对象时,其根本原因就是因为导入时所用账号并不具有SUPER 权限,所以无法创建其他账号的所属对象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常见场景:1. 还原 RDS 时经常出现,因为 RDS 不提供 SUPER 权限;2. 由开发库还原到项目现场,账号权限等有所不同。

处理方式:

1. 在原库中批量修改对象所有者为导入账号或修改 SQL SECURITY 为 Invoker;2. 使用 mysqldump 导出备份,然后将 SQL 文件中的对象所有者替换为导入账号。

二、问题原因我们先来看下为啥会出现这个报错,那就得说下 MySQL 中一个很特别的权限控制机制,像视图、函数、存储过程、触发器等这些数据对象会存在一个 DEFINER 和一个 SQL SECURITY 的属性,如下所示:

--视图定义CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test

--函数定义CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER

--存储过程定义CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER

--触发器定义CREATE DEFINER=`root`@`%` trigger t_test

--事件定义CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER:对象定义者,在创建对象时可以手动指定用户,不指定的话默认为当前连接用户;

SQL SECURITY:指明以谁的权限来执行该对象,有两个选项,一个为 DEFINER,一个为 INVOKER,默认情况下系统指定为 DEFINER;DEFINER:表示按定义者的权限来执行; INVOKER:表示按调用者的权限来执行。

如果导入账号具有 SUPER 权限,即使对象的所有者账号不存在,也可以导入成功,但是在查询对象时,如果对象的 SQL SECURITY 为 DEFINER,则会报账号不存在的报错。ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist

三、改写内容上述这个 DEFINER 问题,个人想到最简单的解决方式就是 mysqldump 导出时直接摘除掉相关属性,但是 mysqldump 本身并不提供对应参数,所以比较蛋疼,无论是原库走脚本变更或是备份后修改 SQL 文件都不是非常方便,尤其是触发器的 DEFINER,只能先 DROP 再 CREATE 才可以变更。只能看下是否可以从 mysqldump 源码中去掉 DEFINER 定义。本次 mysqldump 改写主要有 2 个目的:1. 摘取备份中视图、函数、存储过程、触发器等对象的 DEFINER 定义;2. 尝试加上比较简单的备份进度显示(原生 mysqldump 的 verbose 参数不是非常清晰,想要实现 navicate 备份时的那种行数显示)。

改写好处:1. 可以避免还原时遇到 DEFINER 报错相关问题;2. 根据输出信息知道备份是否正常进行,防止备份中遇到元数据锁无法获取然后一直卡住的情况。


分享名称:mysql主从错误怎么办 mysql57主从
分享路径:http://cqcxhl.com/article/dogepdc.html

其他资讯

在线咨询
服务热线
服务热线:028-86922220
TOP