MySQL 5.7复制的一个小bug-XA事务

线上一个5.7从库复制中断：

成都创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于网站设计制作、网站设计、红山网络推广、小程序制作、红山网络营销、红山企业策划、红山品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；成都创新互联公司为所有大学生创业者提供红山建站搭建服务，24小时服务热线：18980820575，官方网址：www.cdcxhl.com

查询具体报错：

第一感觉很奇怪，为什么会rollback失败呢？于是根据gtid去对应的主库binlog去看了下，并没有任何rollback语句：

看下本地的relay log，找到这个事务的gtid

到这里，这个relay log日志文件结束了。很显然问题也找到了，就是执行

出错了。

首先 我们看到 这个rollback是MySQL自己加上去的，那么为什么要加呢？

mysql为了保证一个事务只在一个binlog里，所以当Binlog或者relay log发生截断时，最后一个事务要么commit，要么rollback，如果rollback，那么下一个binlog或者relay log会把这个事务重做一遍，保证这个事务不会丢。

由于xa事务无法直接rollback，而需要xa rollback ‘XXX’，所以复制就停了。

怎么修复？是不是直接跳过这个rollback就行了？

我们来试一下，跳过这个rollback:

这里GTID_NEXT值不能用show slave status的里executed值，得用具体报错的停止的gtid

但是，show slave status看到，还是有报错：

为什么又报这个事务commit找不到XID呢？

之前说过，在relaylog截断的时候，如果事务没有commit,会自动在最后加rollback，在下一个relay log开始的时候重新做一次这个事务，按理说我们跳过这个rollback，在下个relaylog会被重做，为啥会在commit的时候找不到xid呢？

我们看到我们跳过的gtid原来就是重做这个事务到PREPARE阶段的gtid，原来，rollback是没有gtid的，所以我们实际上就是把这个事务到PREPARE阶段的gtid给跳过了，commit的时候肯定会找不到xid，接着怎么修复？

为什么这是5.7的一个bug呢？5.7之前的版本因为relaylog被截断并不会出现这个bug。

5.7对xa事务的binlog记录方式做了修改，把 xa start,xa end,xa prepare放到一个event里，xa commit又是另外一个event。而在之前的MySQL版本中，整个xa事务从start到commit都是在一个event中，所以其他版本并没有问题。

最后一个问题：5.7为啥要把xa事务拆成两个event？简单的讲是为了数据安全性。

5.5或者5.6假设下面一个场景：

MySQL在某个分布式事务prepare成功后宕机，宕机前操作该事务的连接并没有断开（如果在宕机前断开连接，事务会被MySQL自动回滚），这个时候已经prepare的事务并不会被回滚，所以在MySQL重新启动后，引擎层通过recover机制能恢复该事务。当然该事务的Binlog已经在宕机过程中被丢失，这个时候，如果去提交，则会造成主从数据的不一致， 即提交没有记录Binlog，从上丢失该条数据。

正因为5.7之前版本的xa事务存在这个bug，5.7后做了修复。从XA START到XA PREPARE之间的操作都被记录到了Master的Binlog中，然后通过复制关系传到了Slave上。也就是说5.7开始，MySQL对于XA事务，在prepare的时候就完成了写Binlog的操作，通过新增一种叫 XA_prepare_log_event 的event类型来实现。

其实 MySQL5.7在xa事务上远不止这个bug，后面再来慢慢总结。

MySQL出现错误怎么办？

1、首先检查是否已安装MySQL服务，如果没有安装，则需要安装MySQL服务。

2、然后检查MySQL服务是否已经在系统服务列表中，如果不在则需要手动添加MySQL服务。

3、检查MySQL的配置文件my.ini是否正确，如果不正确则需要修改配置文件。

4、检查Windows服务管理器中MySQL服务的状态是否处于“运行”状态，如果不是，则需要手动启动MySQL服务。

拓展：

如果以上步骤都无法解决问题，可以尝试更新MySQL安装包，或者重新安装MySQL服务。如果仍然无法解决，则可以尝试检查MySQL的数据库文件是否损坏，如果损坏则需要进行修复。

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. 根据输出信息知道备份是否正常进行，防止备份中遇到元数据锁无法获取然后一直卡住的情况。

mysql2016数据库表复制粘贴报错,如何终止导入？

如果从库上表 t 数据与主库不一致，导致复制错误，整个库的数据量很大，重做从库很慢，如何单独恢复这张表的数据？通常认为是不能修复单表数据的，因为涉及到各表状态不一致的问题。下面就列举备份单表恢复到从库会面临的问题以及解决办法：

场景 1

如果复制报错后，没有使用跳过错误、复制过滤等方法修复主从复制。主库数据一直在更新，从库数据停滞在报错状态（假设 GTID 为 aaaa:1-100）。

修复步骤：

在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000）；

恢复到从库；

启动复制。

这里的问题是复制起始位点是 aaaa:101，从库上表 t 的数据状态是领先其他表的。aaaa:101-10000 这些事务中只要有修改表 t 数据的事务，就会导致复制报错 ，比如主键冲突、记录不存在（而 aaaa:101 这个之前复制报错的事务必定是修改表 t 的事务）

解决办法：启动复制时跳过 aaaa:101-10000 这些事务中修改表 t 的事务。

正确的修复步骤：

1. 在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000），恢复到从库；

2. 设置复制过滤，过滤表 t：

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db_name.t');

3. 启动复制，回放到 aaaa:10000 时停止复制（此时从库上所有表的数据都在同一状态，是一致的）;

START SLAVE UNTIL SQL_AFTER_GTIDS = 'aaaa:10000';

4. 删除复制过滤，正常启动复制。

注意事项：这里要用 mysqldump --single-transaction --master-data=2，记录备份快照对应的 GTID

场景 2

如果复制报错后，使用跳过错误、复制过滤等办法修复了主从复制。主、从库数据一直在更新。

修复步骤：

在主库上备份表 t （假设备份快照 GTID为 aaaa:1-10000）；

停止从库复制，GTID为 aaaa:1-20000；

恢复表 t 到从库；

启动复制。

这里的问题是复制起始位点是 aaaa:20001，aaaa:10000-20000 这些事务将不会在从库上回放，如果这里面有修改表 t 数据的事务，从库上将丢失这部分数据。

解决办法：从备份开始到启动复制，锁定表 t，保证 aaaa:10000-20000 中没有修改表 t 的事务。

正确修复步骤：

对表 t 加读锁；

在主库上备份表 t；

停止从库复制，恢复表 t；

启动复制；

解锁表 t。

如果是大表，这里可以用可传输表空间方式备份、恢复表，减少锁表时间。

文章题目：mysql怎么复制出错 mysql复制粘贴
分享地址：http://myzitong.com/article/ddsdeed.html