今天（2013-10-17）又看了一篇很久以前的博客文章，题目是《 》，里面的一些观点和结论摘录如下：

• to make statement-based replication reliable is eliminate temporary tables.
• true temporary tables are created with CREATE TEMPORARY TABLE, and internal temporary tables are created internally by MySQL for sorting or processing subqueries.
• replace temporary tables with real tables in the systems by not naming tables randomly.

=========== 我是分割线 ============       今天（2013-10-11）看了另外一边博客文章，感觉之前记录的内容似乎有些地方说的不太对。这里的内容来自 MySQL 官网翻译。

在一些情况下，服务器会在处理 query 的时候创建内部临时表。这种表有两种存在形式：

• 位于内存中，使用 MEMORY 存储引擎（内存临时表）；
• 位于磁盘上，使用 MyISAM 存储引擎（磁盘临时表）。

      服务器可能在最初创建的是内存临时表，之后当其变大到一定程度时再转变为磁盘临时表。对于服务器何时创建内部临时表或者临时表使用哪种存储引擎，用户没有直接控制的能力。 【服务器可能会创建临时表的场景】 1）ORDER BY 子句和 GROUP BY 子句不同， 例如：ORDERY BY price GROUP BY name；

2）在 JOIN 查询中，ORDER BY 或者 GROUP BY 使用了不是第一个表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3）ORDER BY 中使用了 DISTINCT 关键字 ORDERY BY DISTINCT(price)

4）SELECT 语句中指定了 SQL_SMALL_RESULT 关键字 SQL_SMALL_RESULT 的意思就是告诉 MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT 必须和 GROUP BY、DISTINCT 或 DISTINCTROW 一起使用 一般情况下，我们没有必要使用这个选项，让 MySQL 服务器选择即可。

5）由 FROM 语句中的子查询产生的派生表 6）由于子查询或者  semi-join   materialization 所创建的表 【直接使用磁盘临时表的场景】

• 表包含 TEXT 或者 BLOB 列；
• GROUP BY 或者 DISTINCT 子句中包含长度大于 512 字节的列；
• 使用 UNION 或者 UNION ALL 时，SELECT 子句中包含大于 512 字节的列；

【临时表相关配置】

• tmp_table_size: 指定系统创建的内存临时表最大大小；
• max_heap_table_size: 指定用户创建的内存表的最大大小；

注意：最终的系统创建的内存临时表大小是取上述两个配置值的最小值。       服务器可能在最初创建的是内存临时表，之后当其变大到一定程度时再转变为磁盘临时表。这种表与通过 CREATE TABLE 显式 创建的 MEMORY 内存表是不同的：对于后者，系统变量  max_heap_table_size 决定的是 MEMORY 内存表允许的最大大小，而不会出现向磁盘表转变的动作。       当服务器创建了内部临时表后（无论是在内存中还是在磁盘上），状态变量  Created_tmp_tables 都会增加。如果服务器创建了临时表在磁盘上（无论是初始创建在磁盘还是后来转化到磁盘）， 状态变量  Created_tmp_disk_tables 的值都会增加。 =========== 我是分割线 ============ 当你创建临时表的时候，你可以使用 temporary 关键字。如：

create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)

      临时表只在当前连接可见，当这个连接关闭的时候，会自动 drop 。这就意味着你可以在两个不同的连接里使用相同的临时表名，并且相互不会冲突，或者使用已经存在的表，但不是临时表的表名。（当这个临时表存在的时候，存在的表被隐藏了，如果临时表被 drop，存在的表就可见了）。创建临时表你必须有 create temporary table 权限。  下面几点是临时表的限制：

• 临时表只能用在 memory、myisam、merge 或者 innodb 存储引擎中；
• 临时表不支持 mysql cluster（簇）；
• 在同一个 query 语句中，你只能查找一次临时表。例如：下面的就不可用；

  mysql> SELECT * FROM temp_table, temp_table AS t2;ERROR 1137: Can't reopen table: 'temp_table'

• 如果在一个存储函数里，你用不同的别名查找一个临时表多次，或者在这个存储函数里用不同的语句查找，这个错误都会发生；
• show tables 语句不会列举临时表；
• 你不能用 rename 来重命名一个临时表。但是，你可以 alter table 代替：

  mysql>ALTER TABLE orig_name RENAME new_name;

在数据库复制中使用临时表也有问题，详情参看 Section 16.4.1, “Replication Features and Issues”。 复制和临时表 为了避免出现临时表复制问题，当临时表打开时，不要直接停止 slave 服务。而是用下面的步骤代替：

1. 使用 stop slave sql_thread 语句；
2. 使用 show status 查看 Slave_open_temp_tables 的值；
3. 如果这个值不是 0 ，使用 start slave sql_thread 重启从库 SQL 线程，一会儿后再重复执行这个步骤；
4. 当这个值是 0 时，使用 mysqladmin shutdown 命令停止 slave 。

       默认，所有的临时表都是被复制的，无论是否匹配 --replicate-do-db、--replicate-do-table 或者 --replicate-wild-do-table，复制临时表都会发生。但是，--replicate-ignore-table 和 --replicate-wild-ignore-table 两个选项是用来忽略临时表的。       如果你不想复制某些临时表，请使用 --replicate-wild-ignore-table 选项。如 --replicate-wild-ignore-table=foo%.bar% 意思是告诉 slave 线程不要复制匹配以 foo 开头和以 bar 开头的表。 下面是转自网上某人的配置信息： master端配置：

[mysqld]     #Master start     #日志输出地址 主要同步使用     log-bin=/var/log/mysql/updatelog  #同步数据库     binlog-do-db=cnb    #主机id 不能和从机id重复     server-id=1      #Master end

slave端配置：

[mysqld]      #Slave start          #从机id，区别于主机id      server-id=2     #主机ip，供从机连接主机用      master-host=192.168.0.24    #主机端口      master-port=3307     #刚才为从机复制主机数据新建的账号      master-user=slave       #刚才为从机复制主机数据新建的密码      master-password=123456     #重试间隔时间10秒      master-connect-retry=10       #需要同步的数据库      replicate-do-db=cnb     #启用从库日志，这样可以进行链式复制      log-slave-updates      #从库是否只读，0表示可读写，1表示只读      read-only=1        #只复制某个表      #replicate-do-table=tablename                       #只复制某些表（可用匹配符）      #replicate-wild-do-table=tablename%      #只复制某个库      #replicate-do-db=dbname     #不复制某个表      #replicate-ignore-table=tablename     #不复制某些表      #replicate-wild-ignore-table=tablename%      #不复制某个库      #replicate-ignore-db=dbname     #Slave end

=========== 我是分割线 ============ 临时表测试：

1. 在主服务器上 a. 创建临时表 tmp1

create temporary table tmp1(id int not null);

b. 插入数据

mysql> insert into tmp1(id) values(26);

c. 查看数据

mysql> select * from tmp1;             +----+| id |+----+| 23 | +----+1 row in set (0.00 sec)

在从服务器上查看： 1） 从服务器连接主服务器的状态是否正常；

show slave status\G

2）查看是否同步了临时表：

mysql> select * from tmp1;ERROR 1146 (42S02): Table 'cacti.tmp1' doesn't exist

2. 在主服务器上删除 tmp1：

drop table tmp1;

从服务器没有报错！！！ 内存表测试：

主服务器上操作： a. 创建内存表 tmp_test2

CREATE TABLE tmp_test2 (   Id int(11) AUTO_INCREMENT,   name varchar(255) ) ENGINE=MEMORY ;

b. 插入数据

insert into tmp_test2(id)values(10);

c. 查看数据

mysql>select * from tmp_test2;                           +------+------+| id   | name |+------+------+|   10 | NULL | +------+------+1 row in set (0.00 sec)

在从服务器上查看状态： 1） 从服务器连接主服务器状态是否正常

show slave status\G

2）查看数据

mysql> select * from tmp_test2;+------+------+| id   | name |+------+------+|   10 | NULL | +------+------+1 row in set (0.00 sec)

2. 在主服务器上删除 tmp_test2 表：

drop table tmp_test2；

之后会发现，从服务上 tmp_test2 表也删除了，从服务器连接主服务器状态正常。 注：以上实验不能说明临时表不会在主从之间被复制、内存表会在主从之间复制。因为临时表在主从之间是否会被复制有一些前提条件要约束的！ =========== 我是分割线 ============ 背景：在应用过程中，同事直接关闭了作为 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 创建了多少临时表。

提出几个问题：

• 关掉slave的mysqld, 那临时表肯定是不存在了，这样再次start slave，slave_sql 线程执行bin-log时，肯定会出现找不临时表的错误，
• 这就为什么手册中会提出以上操作流程了，这个问题容易理解。
• 众所周知，MySQL临时表只是当前connection有效(没有全局临时表），当connection断开，此临时也就会被删除，也就不存在了。
• MySQL 5.1的replication，slave的sql线程只有一个，那stop slave后，slave_sql_thread也就停止了，那在Slave上创建的临时表应该随之删除，
• 但从上面步骤来看，说明Stop slave后, 临时表还是存在的，这是为什么呢？
• 如果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 断开后，binlog 会记录一个 Drop 临时表的事件， 这样 Slave 对应的临时表也就被删除了，可以查看临时的状态变量可得。从下面可以看，在我测试环境中 Master 上 thread_id=297 的这个 connection，一共创建了 3 个临时表，当退出 mysql 后，Master 的 binlog 中会记录一个 Drop temporary table 的事件。 

#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 里面执行，否则可能导致临时表操作失败。