mysql 临时不记录binlog mysql 临时表使用

转载

mob64ca141a2a87 2024-05-23 09:15:06

文章标签 mysql 临时不记录binlog 数据库 python mysql 服务器 文章分类 MySQL 数据库

今天（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)
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'
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;
mysql>ALTER TABLE orig_name RENAME new_name;

在数据库复制中使用临时表也有问题，详情参看 Section 16.4.1, “Replication Features and Issues”。

复制和临时表

为了避免出现临时表复制问题，当临时表打开时，不要直接停止 slave 服务。而是用下面的步骤代替：

使用 stop slave sql_thread 语句；
使用 show status 查看 Slave_open_temp_tables 的值；
如果这个值不是 0 ，使用 start slave sql_thread 重启从库 SQL 线程，一会儿后再重复执行这个步骤；
当这个值是 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

=========== 我是分割线 ============

基于mysql主从复制测试对临时表和内存表的支持

临时表测试：

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 ;
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 表也删除了，从服务器连接主服务器状态正常。

注：以上实验不能说明临时表不会在主从之间被复制、内存表会在主从之间复制。因为临时表在主从之间是否会被复制有一些前提条件要约束的！

=========== 我是分割线 ============

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。