临时表在MySQL的复制中的处理

背景：上次在应用，因为同事直接关闭了slave的MySQL server导致了临时表问题。

在MySQL5.1手册（6.7. 复制特性和已知问题）中提到，关闭slave的正确流程（个人认为在4步骤只启动slave_SQL线程好一些）

1. 执行STOP SLAVE语句。

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

3. 如果值为0，使用mysqladmin shutdown命令关闭从服务器。

4. 如果值不为0，用START SLAVE重启从服务器线程。

5. 后面再重复该程序看下次的运气是否好一些。

slave_open_temp_tables 的值显示，当前slave创建了多少临时表，注意由于client显示创建的。

提出几个问题：

1，关掉slave的mysqld, 那临时表肯定是不存在了，这样再次start slave，slave_sql 线程执行bin-log时，肯定会出现找不临时表的错误，

这就为什么手册中会提出以上操作流程了，这个问题容易理解。

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

MySQL 5.1的replication，slave的sql线程只有一个，那stop slave后，slave_sql_thread也就停止了，那在Slave上创建的临时表应该随之删除，

但从上面步骤来看，说明Stop slave后, 临时表还是存在的，这是为什么呢？

3，如果Slave不停止，那由slave创建的临时是如何正常删除的？ 它们在slave上的存储形式又是怎么样的？

以下简单分析一下2，3 问题

分析：

1，临时表只对当前会话可见，连接断开时，自动删除！

2，查看临时表，在Master的binlog中的记录形式

2.1 MySQL对临时表的复制，如果在mixed的binlog_format情况下，会以Statement的形式记录到binlog中，当然也可以用Row形式

，因为临时表是基于Session的（也可以说是Connection的），所以在复制中，MySQL会把线程ID添加到临时表操作的事件中

，此时的临时表是属于某个正在运行的Thread。 通过 mysqlbinlog来查看binlog,可以看到事件上绑定了thread_id=297就是这个临时表的

宿主线程，当然你也可以用Show processlist;来查看这个线程

# at 106

#120318 1:42:30 server id 1 end_log_pos 291 Query thread_id=297 exec_time=0 error_code=0

use rep/*!*/;

SET TIMESTAMP=1332006150/*!*/;

SET @@session.pseudo_thread_id=297/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C latin1 *//*!*/;

SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create temporary table cache2( id int unsigned not null, value char(10) not null default '', primary key(id) )engine=myisam

/*!*/;

show processlist;

| 297 | root | localhost | rep | Query | 0 | NULL | show processlist

2.2 从Master的binlog可以看到，有一个SET @@session.pseudo_thread_id=297，这个记录salve的Sql_thread在执行此binlog时，

会创建一个id号为297的"伪线程", 这样在slave上创建的此临时表cache2的宿主线程就此伪线程。

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

mysql> show status like '%slave%';

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

| Variable_name | Value |

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

| Com_show_slave_hosts | 0 |

| Com_show_slave_status | 0 |

| Com_slave_start | 0 |

| Com_slave_stop | 0 |

| Slave_open_temp_tables | 3 |

| Slave_retried_transactions | 0 |

| Slave_running | ON |

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

7 rows in set (0.00 sec)

对于问题2， 为何slave sql thread停掉后，临时表还存在的原因。

3, Slave中的临时是如何删除的呢？

当在Master的创建此临时表的Session断开后，binlogw会记录一个Drop临时表的事件，

这样Slave对应的临时表也就被删除了，可以查看临时的状态变量可得。从下面可以看，在我测试环境中Master上

thread_id=297的这个connection, 一共创建了3个临时表，当退出mysql后，Master的binlog中会记录一个Drop temporary table的事件。

Slave_open_temp_tables

#120318 1:45:53 server id 1 end_log_pos 734 Query thread_id=297 exec_time=0 error_code=0

SET TIMESTAMP=1332006353/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=8,@@session.collation_server=8/*!*/;

DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `cache3`,`cache2`,`cache`

/*!*/;

DELIMITER ;

# End of log file

当slave 的slave_sql_thread执行此事件，也就把刚才创建的临时表删除了。

4, Slave创建的临时表放在哪里呢？

MySQL创建的临时表的文件，其实是放在show variables like 'tmp_dir'这个变量指定的目录下

默认情况是下在/tmp目录下

-rw-rw---- 1 mysql mysql 98304 Mar 23 05:39 #sql2625_18_0.ibd

-rw-rw---- 1 mysql mysql 8586 Mar 23 05:39 #sql2625_18_0.frm

同时也会在slave上的/tmp目录下找到

-rw-rw---- 1 mysql mysql 8586 Mar 24 18:28 #sqld0b_7_2.frm

-rw-rw---- 1 mysql mysql 98304 Mar 24 18:28 #sqld0b_7_2.ibd

也可以根据mysqld打开的文件来查看。

5，关于临时表有两个问题：

5.1 在重新启动Slave 的mysqld服务时，Stop Slave后，一定要检查Slave_open_temp_tables 这个状态值是否已经是0，如果不是，

要重新start slave, 再stop slave，查看，直接是0后，才stop mysql。因为mysql重新启动后，在Slave上的所有临时表都没有了，这样重新进行复制时

后面还有对临时表的操作的binlog事件，因为Slave上的临时表已不存在，此时肯定会出错了。

5.2 在用binlog进行point_in_time恢复数据库时，一定要注意，把所有的binlog放在同一个session里面执行，否则，可能导致临时表操作失败