Mysql table id问题导致主从复制失败该怎么办

共计 1773 个字符，预计需要花费 5 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 Mysql table id 问题导致主从复制失败该怎么办，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

0、导读

主从复制环境中，IO、SQL 线程都很正常，也没设置过滤规则，但数据就是无法复制到 slave 上，什么原因？

1、问题描述

事实上，这个案例发生已经有一阵子了，一直拖到现在我才整理。

发现一个主从环境中，slave 上的 io_thread、sql_thread 状态均正常，relay log 也正常接收来自 master 的 event，但 slave 上却无法正常应用这些 event，个别表数据没有复制过来。而且 slave 上的 binlog 也没有记录这些表上的操作。

2、原因分析

接到现场后，第一反应是是先检查是否设置了 ignore/do 规则，发现并不是这个原因引起的。

我自己手动测试创建了个新的测试表，写了几条数据，发现在 slave 上这个表能被创建，但写入的测试数据仍旧无法复制过来。这说明，slave 上的复制并不是完全失效的，只是有特殊情形下才会失效。

结合上面的问题，想到了可能是因为 binlog format 以及事务隔离级别等原因导致失效的，于是做了下面的尝试。

// 首先修改事务隔离级别为 RR（此前是 RC），尽可能保证主从数据一致性

root@imysql [mydb] set session transaction isolation level repeatable read;

// 测试写入 2 条数据

root@imysql [mydb] insert into z select 5,5;

root@imysql [mydb] insert into z select 6,6;

经过观察，这 2 条数据不可以复制到 slave 上。

// 修改 binlog format 为 statement（此前是 row），再写入 2 条数据

root@imysql [mydb] set session binlog_format=’statement’;

root@imysql [mydb] insert into z select 7,7;

root@imysql [mydb] insert into z select 8,8;

经过观察，这 2 条数据则可以复制到 slave 上。

现在至少表面上看起来，是由于 binlog format+ 事务隔离级别综合因素引起的，所以我们来对比下不同 binlog format 下的 binlog 有什么区别吧。

这些日志中，前两条是 row 模式下的日志，后两条则是 statement 模式下的。我们注意到红框中内容是：table_id: 24874588093，正是由于这个原因导致了 slave 无法正常复制数据。

正常情况下，row 模式下的 binlog event 应该是这样的：

在上面的日志中，我们看到的是：table_id: 108，这种情况下就可以正常复制了。

现在问题很明确了，就是由于 binlog 中 table id 异常导致无法复制。那么，到底什么原因导致 table id 出现异常呢。

3、案例建议

搜索了一些资料，发现也有别人遇到同样的问题。我就不多啰嗦了，大家可以看下方参考文章详细了解下。简言之，发生这中问题的原因，主要是因为 table cache 不够了，导致要频繁打开、关闭 table，导致 table id 急剧增长，因而导致主从数据复制失败。

解决办法有几个：

加大 table_cache_size，或者 table_open_cache 值，以及 table_definition_cache 选项。一般设置不低于总 table 数量的 1.5 倍，更严谨的话，要看 Open_tables 和 Opened_tables 这两个 status 值。Open_tables 表示当前正被打开的 table 数量，而 Opened_tables 表示历史上反复打开 table 的总次数。如果 Opened_tables 值特别高，表明 table cache 很可能不够用所致。

择机重启主库实例，让 table id 的值再次从 0 开始计数。

临时解决方案：把 binlog format 改成 statement，并且把事务隔离级别改成 RR，尽量避免数据不一致的风险。

关于 Mysql table id 问题导致主从复制失败该怎么办就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。