MySQL复制对于临时表的处理

在跟网友讨论 inception 这个工具的回滚功能设计时，知道了它会根据 binlog 中的 query event 带的 thread id 来定位此次会话所执行的 SQL 语句有哪些，这是一个事务，当然定位回滚数据还需要 binlog position。而在 MySQL 中对于，如果你的 binlog 模式为 statement 格式，那么所有的 event 都是 query event，都有对应的 thread id。如果 binlog 模式为 row 格式，那么 ddl 操作都是 query event 类型，自然有 thread id，而 dml 操作就是对应的 Write_rows event、Update_rows event、Delete_rows event，但 MySQL 会在每个事务开始时，给 Begin 设置为 query event，自然也有 thread id。据官方说明，thread id 在源码中叫做 slave_proxy_id，主要用于解决创建临时表时避免重复，解决此 Bug #1686 。

（MySQL binlog row format）

按照 MySQL 这个模式，回滚貌似都没有问题（执行 SQL 前获取当前线程 ID，然后匹配 binlog 中的线程 ID），但是在 MariaDB 中对于 binlog 为 row 格式时的 dml event，并没有给 Begin 设置为 query event，而是使用了 Gtid event 这么个东东，而这个 Gtid event 没有 thread id 信息，而 MariaDB 的 query event 基本只有 binlog 为 statement 格式，或 ddl 时才会使用，具体可参考 MariaDB replication protocol。

（MariaDB binlog row format）

所以如果你使用 inception 作为你的审核工具的话，如果你的使用 MariaDB，其实是没办法回滚的，主要原因就是 thread id 问题。现有的解决办法就是注释掉代码中判断 thread id 的逻辑，这样带来的问题就是不能 100% 保证你本次执行的 SQL 产生的回滚语句一定正确（产生的回滚只会多不会少），表的并发越大，错误率越高。

关于回滚这个话题先这样了，这篇文章主要是想说另外一个问题，就是在查 binlog thread id 时，引出的 Bug #1686 ，也就是 MySQL 复制中对于显式临时表创建的处理方式（这里指显示创建的外部临时表，而不是 MySQL 内部临时表），以及会产生什么问题？

复制和临时表

本节的其余部分仅适用于使用基于 STATEMENT 或 Mixed 格式的复制。当 binlog_format 为 STATEMENT 或 MIXED 时，Slave 上复制的临时表的丢失可能是一个问题。

使用临时表时安全关闭 Slave 的方式。当 Slave 已经复制临时表，此时停止 Slave 服务器（不仅仅是 Slave 线程），再启动服务器后，临时表将会丢失。这会带来一个问题，后续关于这个临时表的操作还存在于 binlog 中，启动复制线程后继续执行这些 binlog，肯定会出现找不到临时表的错误。要避免此问题，请勿在临时表打开时关闭 Slave。而是，使用以下过程：

1. 发出 STOP SLAVE SQL_THREAD 语句。

2. 使用 SHOW STATUS 检查 Slave_open_temp_tables 变量的值。

3. 如果该值不为 0，请使用 START SLAVE SQL_THREAD 重新启动 Slave SQL 线程，然后重复此过程。

4. 当值为 0 时，发出 mysqladmin shutdown 命令停止 Slave 服务器。

其中 slave_open_temp_tables 变量的值显示当前 Slave 创建了多少临时表。

众所周知，MySQL 临时表只是当前会话有效（没有全局临时表），当当前会话断开时，此临时也就会被删除，也就不存在了。

MySQL 对临时表的复制，当 binlog_format 为 STATEMENT 或 MIXED 时，会以 STATEMENT 的形式记录到 binlog 中，因为临时表是基于会话的，所以在复制中，MySQL 会把 thread id 添加到临时表操作的事件中，此时的临时表是属于某个正在运行的 Thread。通过 mysqlbinlog 来查看 binlog，可以看到事件上绑定了 thread_id=4 就是这个临时表的。

从 Master 的 binlog 可以看到，有一个SET @@session.pseudo_thread_id=4，这个记录 Slave 的 Sql_thread 在执行此 binlog 时，会创建一个 id 号为 4 的”伪线程”,  这样在 Slave 上创建的此临时表 cache 的宿主线程就此伪线程。

当 stop slave 后，Slave_SQL 线程已经关闭，但此时在 Slave 的临时表是还存在的，可以通过在 Slave 上查看 Status 变量 Slave_open_temp_tables ，其实是不为 0 的，也就说由 Master 复制来的临时表还存在，因为这些临时表是所属于 Master 上创建临时表的 Thread 的 Thread_ID 对应的 pseudo_thread，所以虽然 Slave_SQL connection 已经断开，但临时表是还存在的。

当在 Master 上创建此临时表的会话断开后，binlog 会记录一个 DROP TEMPORARY TABLE IF EXISTS 的事件，当 Slave 的 slave_sql_thread 执行此事件，也就把刚才创建的临时表删除了。

Tips：无论 Master 还是 Slave，创建的临时表的文件，都是放在 show variables like ‘tmp_dir’ 这个变量指定的目录下。默认情况是下在 /tmp 目录下。

如果说 binlog 是 MIXED 格式，那么在用 binlog 进行 point_in_time 恢复数据库时，一定要注意，把所有的 binlog 放在同一个会话里面执行，否则，可能导致临时表操作失败。

临时表和复制选项。默认情况下，复制所有临时表，无论是否存在匹配的--replicate-do-db， --replicate-do-table或--replicate-wild-do-table选项，都会发生这种情况。但是，--replicate-ignore-table和--replicate-wild-ignore-table选项适用于临时表。例外是，为了在会话结束时正确删除临时表，Slave 始终复制 DROP TEMPORARY TABLE IF EXISTS 语句，不管通常适用于指定表的任何排除规则。

使用基于语句或混合格式的复制时，建议的做法是指定一个前缀，以便在命名不需要复制的临时表时使用，然后使用--replicate-wild-ignore-table选项匹配该前缀。例如，您可以给出以 norep 开头的所有这些表名（例如 norepmytable，norepyourtable 等），然后使用--replicate-wild-ignore-table = norep％来防止它们被复制。

<参考>

https://bugs.mysql.com/bug.php?id=1686

http://bbs.chinaunix.net/thread-3706036-1-1.html

https://dev.mysql.com/doc/refman/5.7/en/replication-features-temptables.html

https://dev.mysql.com/doc/refman/5.7/en/replication-rbr-usage.html#replication-rbr-usage-temptables

喜欢 (1)赏

[资助本站您就扫码 谢谢]

分享 (0)