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

RogerZhuo

背景：上次在应用，因为同事直接关闭了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里面执行，否则，可能导致临时表操作失败

blog地址：http://blog.chinaunix.net/uid-26364035-id-3166987.html

RogerZhuo

回复 2# kerlion
谢谢支持，只是个人理解（没有老大审核），如果有错误的地方希望牛牛批正， 分享事小， 误人子弟事大。

RogerZhuo

回复 4# kerlion

是的，我上面说的也是，stop slave(也就是io/sql两个线程都停止）， 此时的临时表还是在的，但是如果把mysqld停掉后，此时的临时表就不存在了，
当我们restart mysql, 再start slave；这个时候就会出错了。
   

ooooldman

希望多出这样的帖子