MySQL复制异常大扫盲：快速溯源与排查错误全解

2023-11-11 来源：哗拓教育

（3）原因分析过程查看error logError log中显示的详细错误信息如下：错误信息显示无法找到对应的binlog文件。查看主库binlog日志查看主库的binlog日志文件列表，可能会发现主库的binlog变成重新开始记录：或者需要复制的binlog已经被删除：总结如果binlog重新开始记录，通常是由于主库执行了reset master命令，导致所有的binlog被删除。如果binlog任然在继续记录，只是从库需要的binlog被删除，通常是由于主库手动执行了purge binary logs命令，或者日志的保留时间超过了expire_logs_days设置的时间。3、从库没有执行主库复制的语句由于GTID的特性，SQL线程不会去执行相同的GTID对应的事务，即如果SQL线程发现从relay log中读取到的事务对应的GTID已经存在于从库的GTID_EXECUTED中，那么SQL线程便不会存在。（1）错误原因复制过程中，用于主库执行的事务对应的GTID已经存在于从库的GTID_EXECUTED中，那么从库便不会执行这些事务，从而导致主库和从库的数据不一致。通常有如下情况：主机执行了reset master（从库当前读取主机的第一个binlog，并不会因为reset master而导致找不到文件）重做主从，从库没有清除从库的binlog（2）错误信息在从库忽略主机执行的事务的过程中，从库复制不会报出任何错误，所以这种复制的异常容易被忽略，没有办法及时的发现。由于主库和从库的数据库不一致，后续的DML和DDL操作可能会发生执行失败的错误。（3）原因分析过程这里我们以插入语句找不到对应的表为例。查看error logError log中记录错误信息：查看show slave statusshow slave status显示的信息全部正常，无从库执行事务的binlog产生。这里不排除从库关闭binlog执行drop table操作的可能。查看表分别在主机和从库执行命令show create table mydb.mytbl4，发现从库上并未不存在mydb.mytbl4。（4）解析binlog日志解析主机binlog日志，查看建表的事务日志：解析从库的binlog日志，查找是否存在建表的事务日志：这时我们发现对于相同的GTID，从库和主机执行的语句是不相同的。（5）总结通过上述分析，我们推断是从库并没有执行建表语句，从而导致主库数据不一致。（6）说明这种情况在mysql-5.7版本会在复制时有更严格的校验，如果主机发送GTID要少于从库的GTID，那么会报告出如下的错误：但是即使在5.7版本，如果启动复制的时（错误后重新启动），主库执行的GTID超过了从库，仍然会报出同样的错误。4、主库执行了不进行复制的语句（1）错误原因主库上执行的操作并不会写入binlog。这里不考虑主库主动关闭binlog的情况。（2）错误信息由于主库和从库的数据不一致，从而导致主库执行的操作复制到从库后，发生从库执行失败的情况。如：创建FEDERATED引擎的表失败（3）原因分析过程这里以使用CONNECTION创建FEDERATED引擎的表为例。查看error logError log中记录错误信息：查看主库和从库的server表主库中server表中存在名字为s的记录：从库中不存在名字为s的记录：查看CREATE SERVER文档说明文档中记录，create server语句并不会记录到binlog中。所以导致了主库和从库的数据不一致。复制无法正常进行。总结对于不记入binlog的操作，需要主库和从库同时执行，以防发生主库和从库不一致的情况。5、从库重复执行relay log的语句（非GTID，非多线程复制）当变量relay_log_info_repository设置为FILE时，从库的SQL线程每次执行完一个事务后，会把对应的文件和位置信息更新到文件relay_log.info中。用于在从库重启时，SQL能够从正确的位置继续进行复制。（1）错误原因如果物理机发生宕机或者从库发生意外中断，那么可能发生SQL线程已经执行过了某一个relay log中的事务，但是这个事务对应文件和位置信息并没有及时更新到relay_log.info中的情况。在从库发生重启之后，会将执行过的事务重新再次执行。（2）错误信息重复执行的事务包括任何记录到relay log中的事务，可能出现的错误信息包括：创建库或者表失败：插入语句主键冲突：删除语句找不到对应的语句：由于各种类型的事务均可能执行，这里不再一一列举。（3）原因分析过程这里以插入语句主键冲突为例。查看error logError log中记录以下报错信息：可以看到是SQL线程在启动后执行的第一个事务就发生主键冲突的错误。查看show slave statusshow slave status显示的信息全部正常，无从库执行事务的binlog产生。查看表mydb.k2表中已经存在了这条记录。查看从库的relay log和binlog查看从库的relay log，从复制的起始位置./relaylog002.000002：616查看查看从库的binlog：总结通过分析上述binlog内容，relay log中并没有记录相同的insert语句，而从库的binlog显示已经执行过该语句，当从库重启后，试图再次执行相同的insert语句，从而导致插入语句的主键冲突。说明如果复制使用GTID，那么GTID的特性会使从库不执行相同的语句。如果在5.7版本复制使用多线程复制，那么mts_recovery会修复这个问题。只有在非多线程复制、非GTID复制的情况下才可能出现这个错误。五、总结如果复制发生了错误，通过收集上述的复制相关信息和错误相关信息，分析这些信息中与正常复制异常的地方，便可为排查复制错误提供更多的可以用来排除异常的信息。当然复制的错误是多种多样的，并不是所有的错误都可以排查到具体的产生原因。很多复制错误是较难或者无法进行排查的，比如主库或者从库的binlog日志文件已经丢失、比如关闭binlog后执行某些操作导致复制不一致，再比如某些内核BUG导致MySQL的复制逻辑本身发生了异常等。

标签：报错 tps 停止接收 master 定位 https drop 日志

小编还为您整理了以下内容，可能对您也有帮助：

数据库复制报mysqld_debug无法读取源文件

当在MySQL数据库进行复制时，如果出现"mysqld_debug无法读取源文件"的报错信息，这可能意味着 MySQL 无法读取或访问相关的源文件。以下是一些常见原因和解决方法：

1. 检查文件路径和权限：确定源文件的路径是否正确，并确保 MySQL 进程具有足够的权限来读取该文件。检查文件的所有者和权限设置，确保 MySQL 进程或运行 MySQL 的用户具有适当的读取权限。

2. 文件是否存在：确认源文件是否存在于指定的路径中。如果文件不存在，请检查是否已更改了文件名或目录结构，并相应地更新 MySQL 配置。

3. SELinux 或防火墙设置：如果操作系统启用了 SELinux 或有防火墙设置，它们可能会 MySQL 对文件的访问权限。请检查 SELinux 或防火墙规则，并确保允许 MySQL 访问目标文件。

4. 检查日志和错误信息：查看 MySQL 错误日志以获取更多详细的报错信息。错误日志通常位于 MySQL 配置中指定的日志文件路径中。通过查看日志，您可以获得更多关于无法读取源文件的具体原因。

5. 重新安装或更新 MySQL：如果问题仍然存在，考虑重新安装 MySQL 或更新到最新的稳定版本。在此过程中，请确保按照官方文档的建议进行正确的安装和配置。

如果上述解决方法仍无法解决问题，请考虑向 MySQL 官方支持团队或社区寻求帮助。他们将能够针对您特定的环境和情况，提供更具体的建议和指导。

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. 删除复制过滤，正常启动复制。
注意事项：这里要用 mysqlmp --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。
如果是大表，这里可以用可传输表空间方式备份、恢复表，减少锁表时间。

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

分享一下Mysql常见的几个错误问题及解决方法

1.问题： mysql DNS反解：skip-name-resolve

错误日志有类似警告：

1.120119 16:26:04 [Warning] IP address '192.168.1.10' could not be resolved: Name or service not known

2.120119 16:26:04 [Warning] IP address '192.168.1.14' could not be resolved: Name or service not known

3.120119 16:26:04 [Warning] IP address '192.168.1.17' could not be resolved: Name or service not known

通过show processlist发现大量类似如下的连接：

skip-name-resolve 参数的作用：不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。

修改配置文件添加并需要重启：

[mysqld]

skip-name-resolve

2.问题错误日志：Error: Can't create a new thread (errno 12)

数据库服务器问题，数据库操作无法创建新线程。一般是有以下3个方面的原因：

1）、MySQL 线程开得太多。

2）、服务器系统内存溢出。

3）、环境软件损坏或系统损坏。

【问题解决】

1.1）进入 phpmyadmin 的 mysql 数据库中的 user 表，对数据库的用户进行编辑，修改 max_connections 的值。适当的改小一点。

2.2）联系服务器管理员检查服务器的内存和系统是否正常，如果服务器内存紧张，请检查一下哪些进程消耗了服务器的内存，同时考虑是否增加服务器的内存来提高整个系统的负载能力。

3.3）mysql版本更改为稳定版本

4.4）优化网站程序的sql等等

3. 操作报错：ERROR 1010 (HY000): Error dropping database

1.mysql> drop database xjtrace;

2.ERROR 1010 (HY000): Error dropping database (can't rmdir './xjtrace/

在做数据库删除时出现这种提示，其原因是在database下面含有自己放进去的文件，譬如*.txt文件或*.sql文件等，只要进去把这个文件删了在执行。

1.mysql>drop database xjtrace;

2.Query OK, 0 rows affected (0.00 sec)

果断删除即可！！

4.导出数据很快，导入到新库时却很慢：

MySQL导出的SQL语句在导入时有可能会非常非常慢，经历过导入仅400万条记录，竟用了近2个小时。在导出时合理使用几个参数，可以大大加快导入的速度。

-e 使用包括几个VALUES列表的多行INSERT语法;

--max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;

--net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行

注意：max_allowed_packet和net_buffer_length不能比目标数据库的配置数值大，否则可能出错。

首先确定目标库的参数值

mysql> show variables like 'max_allowed_packet';

+--------------------+---------+

| Variable_name | Value |

+--------------------+---------+

| max_allowed_packet | 1048576 |

+--------------------+---------+

1 row in set (0.00 sec)

mysql> show variables like 'net_buffer_length';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| net_buffer_length | 16384 |

+-------------------+-------+

1 row in set (0.00 sec)

根据参数值书写mysqlmp命令，如：

mysql>mysqlmp -uroot -p 数据库名 -e --max_allowed_packet=1048576 -net_buffer_length=16384 > SQL文件

例如：

1.mysql> mysqlmp -uroot -p xjtrace -e --max_allowed_packet=1048576 --net_buffer_length=16384 >xjtrace_data_`date +%F`.sql

之前2小时才能导入的sql现在几十秒就可以完成了。您可能感兴趣的文章:MySQL数据库导出与导入及常见错误解决MySQL的主从复制步骤详解及常见错误解决方法mysql常见的错误提示问题处理小结MySQL 常见错误分析与解决方法mysql常见错误集锦MySQL常见错误有哪些_MySQL常见错误的快速解决方法MySQL读取Binlog日志常见的3种错误Mysql主从数据库(Master/Slave)同步配置与常见错误MySQL中常见的八种SQL错误用法示例故障分析 | 记一次 MTS 并行复制导致的死锁排查

前段时间在客户现场发现一个奇怪的锁问题，特地拿来和大家分享一下。

MySQL 版本是 8.0.18 ，在从库的线程等待连接中观测到的现象是这样的：

从上面的连接可以观测到：这里存在着几个锁等待，有等全局读锁的，有等提交锁的，首先应该理清对应的锁等待顺序，看看究竟是“谁在等我，而我又在等谁”。

通过上面 threads 表的加锁线程，可以分析到有三条可以抓源头的线程：

我们从最容易分析的全局读锁入手，以线程47295994为切入点进行追根溯源，看看线程47295994持有的锁是什么？这就需要借助 perfoemance_schema 库中的 metadata_locks ，即我们通常分析 DDL 阻塞的元数据锁表进行分析，

线程47295994中的 SQL 是执行了一条 insert 语句，没有观察到该线程持有锁，只是在等全局读锁，我们接着分析全局读锁。

通过元数据锁表进一步确认线程，接下来就看看谁持有了全局读锁：

我们挑几条记录拉回 threads 表中看看能否找到对应的执行 SQL ：

这里通过多次查询可以得到确认，全局读锁是 set global read_only=on 这条语句下发的，从 threads 表中，我们还能找到下发这条语句的用户 proxy_monitor 和连接IP 10.108.76.139/140 ，便可以进一步确认下发全局读锁的应用或者客户端。

通过了解，原来客户使用的 shell 脚本对从库进行监控并配置只读，只要从库不是只读状态，脚本就不停地下发命令去进行配置，由于前面产生的锁阻塞，上千根 set global read_only 连接在 MySQL 中产生等待。

在 threads 表中，我们还观察到一个现象，是所有下发全局读锁的线程都在等commit锁，那commit锁是被谁持有的呢？我们接着去元数据锁表中去检索：

看来全局读锁在等线程47295995、47295996手中的commit锁释放，而47295994 在等全局读锁释放，这里还差一个知识点，那就是线程47295994和47295995、47295996之间的等待关系是什么呢？

不知道大家对 “Waiting for preceding transaction to commit” 这个等待状态有没有了解，它的意思是指该线程中的事务要等待同一个事务提交组中靠前的事务优先完成提交，即同组事务顺序提交，在 MySQL 中有一个参数对从库的顺序提交进行配置（slave_preserve_commit_order）。

在客户现场并没有对 “线程号小的连接中事务优先提交”这个问题进行验证，我们稍后在本地环境中进行测试复现一下。

由于从库进行set global read_only = ON; 时持有MDL::global read lock，并进一步想要获取MDL::global commit lock，而且在LOCK_TYPE中显示的是MDL_SHARED锁。slave_preserve_commit_order=1时，relay log中事务的提交组中的顺序会严格按照在relay log中出现的顺序提交，所以事务的执行和set global read_only语句获得两个锁都不是原子的，并行复制模式下按以下的顺序就会出现死锁：

从库执行以下语句均有可能发生上面的死锁情况：

set global read_only ; grant all(FLUSH PRIVILEGES) ; flush table with read lock ;

使用 sysbench 模拟业务压力，从库开一个窗口进行语句输入，多次执行 set global read_only=ON ;才会卡住，观测输出：

kill 掉 set global read_only=ON ;这条语句之后，复制恢复正常，我们看看在连接hang住时的情况。

死锁1-客户现场

查看对应 performance_schema.threads 表中的信息根据事务的提交顺序，确定对应线程提交顺序，说明客户现场线程号小的执行的事务提交顺序在线程号大的执行事务之前。根据事务的提交顺序，确定对应线程提交顺序，说明客户现场线程号小的执行的事务提交顺序在线程号大的执行事务之前。

查看 performance_schema.metadata_locks 表中锁持有关系

加锁模型和上面分析中的的死锁闭环相同。

根据事务的提交顺序，确定对应线程提交顺序，说明客户现场线程号小的执行的事务提交顺序在线程号大的执行事务之前。

死锁2

查看对应 performance_schema.threads 表中的信息

查看 performance_schema.metadata_locks 表中锁持有关

根据锁关系画出相应死锁闭环

除了必须的锁外，事务会额外请求 MDL_key::GLOBAL 锁的 MDL_INTENTION_EXCLUSIVE 锁；在事务提交前，会先请求 MDL_key::COMMIT 锁的 MDL_INTENTION_EXCLUSIVE 锁，这个锁是S锁，对于范围锁来说，IX锁和S锁是不兼容的。

根据事务的提交顺序，确定对应线程提交顺序

参考：

https://sq.163yun.com/blog/article/211685211777150976

http://dbaplus.cn/news-11-1874-1.html

http://mysql.taobao.org/monthly/2018/02/01/

bug工单： https://bugs.mysql.com/bug.php?id=95863

显示全文

全部栏目

MySQL复制异常大扫盲：快速溯源与排查错误全解