今天（2013-10-17）又看了一篇很久以前的博客文章，题目是《 How to eliminate temporary tables in MySQL 》，里面的一些观点和结论摘录如下：
- 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 官网翻译。
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 的值都会增加。
=========== 我是分割线 ============
mysql 复制和临时表 temporary table
当你创建临时表的时候，你可以使用 temporary 关键字。如：
create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)下面几点是临时表的限制：
- 临时表只能用在 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;
复制和临时表
为了避免出现临时表复制问题，当临时表打开时，不要直接停止 slave 服务。而是用下面的步骤代替：
- 使用 stop slave sql_thread 语句；
- 使用 show status 查看 Slave_open_temp_tables 的值；
- 如果这个值不是 0 ，使用 start slave sql_thread 重启从库 SQL 线程，一会儿后再重复执行这个步骤；
- 当这个值是 0 时，使用 mysqladmin shutdown 命令停止 slave 。
如果你不想复制某些临时表，请使用 --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[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=========== 我是分割线 ============
基于mysql主从复制测试对临时表和内存表的支持
临时表测试：
1. 在主服务器上
a. 创建临时表 tmp1
create temporary table tmp1(id int not null);mysql> insert into tmp1(id) values(26);mysql> select * from tmp1;
+----+
| id |
+----+
| 23 |
+----+
1 row in set (0.00 sec)在从服务器上查看：
1） 从服务器连接主服务器的状态是否正常；
show slave status\Gmysql> select * from tmp1;
ERROR 1146 (42S02): Table 'cacti.tmp1' doesn't exist2. 在主服务器上删除 tmp1：
drop table tmp1;内存表测试：
主服务器上操作：
a. 创建内存表 tmp_test2
CREATE TABLE tmp_test2 (
Id int(11) AUTO_INCREMENT,
name varchar(255)
) ENGINE=MEMORY ;insert into tmp_test2(id)
values(10);mysql>select * from tmp_test2;
+------+------+
| id | name |
+------+------+
| 10 | NULL |
+------+------+
1 row in set (0.00 sec)在从服务器上查看状态：
1） 从服务器连接主服务器状态是否正常
show slave status\Gmysql> select * from tmp_test2;
+------+------+
| id | name |
+------+------+
| 10 | NULL |
+------+------+
1 row in set (0.00 sec)drop table tmp_test2；注：以上实验不能说明临时表不会在主从之间被复制、内存表会在主从之间复制。因为临时表在主从之间是否会被复制有一些前提条件要约束的！
=========== 我是分割线 ============
临时表在MySQL的复制中的处理
背景：在应用过程中，同事直接关闭了作为 slave 的 MySQL server 导致了临时表问题。
在 MySQL5.1 手册（6.7. 复制特性和已知问题）中提到，关闭 slave 的正确流程（个人认为在 4 步骤只启动slave_SQL 线程好一些）：
- 执行 STOP SLAVE 语句。
- 使用 SHOW STATUS 检查 slave_open_temp_tables 变量的值。
- 如果值为 0，使用 mysqladmin shutdown 命令关闭从服务器。
- 如果值不为 0，用 START 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上的存储形式又是怎么样的？
分析：
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 processlist2.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)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 file4, 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-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.ibd5，关于临时表有两个问题：
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 里面执行，否则可能导致临时表操作失败。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
