臨時(shí)表在MySQL的復(fù)制中的處理

RogerZhuo

背景：上次在應(yīng)用，因?yàn)橥�事直接關(guān)閉了slave的MySQL server導(dǎo)致了臨時(shí)表問題。
在MySQL5.1手冊（6.7. 復(fù)制特性和已知問題）中提到，關(guān)閉slave的正確流程（個(gè)人認(rèn)為在4步驟只啟動slave_SQL線程好一些）
1. 執(zhí)行STOP SLAVE語句。
2. 使用SHOW STATUS檢查slave_open_temp_tables變量的值。
3. 如果值為0，使用mysqladmin shutdown命令關(guān)閉從服務(wù)器。
4. 如果值不為0，用START SLAVE重啟從服務(wù)器線程。
5. 后面再重復(fù)該程序看下次的運(yùn)氣是否好一些。

slave_open_temp_tables 的值顯示，當(dāng)前slave創(chuàng)建了多少臨時(shí)表，注意由于client顯示創(chuàng)建的。

提出幾個(gè)問題：
1，關(guān)掉slave的mysqld, 那臨時(shí)表肯定是不存在了，這樣再次start slave，slave_sql 線程執(zhí)行bin-log時(shí)，肯定會出現(xiàn)找不臨時(shí)表的錯(cuò)誤，
這就為什么手冊中會提出以上操作流程了，這個(gè)問題容易理解。

2，眾所周知，MySQL臨時(shí)表只是當(dāng)前connection有效(沒有全局臨時(shí)表），當(dāng)connection斷開，此臨時(shí)也就會被刪除，也就不存在了。
MySQL 5.1的replication，slave的sql線程只有一個(gè)，那stop slave后，slave_sql_thread也就停止了，那在Slave上創(chuàng)建的臨時(shí)表應(yīng)該隨之刪除，
但從上面步驟來看，說明Stop slave后, 臨時(shí)表還是存在的，這是為什么呢？

3，如果Slave不停止，那由slave創(chuàng)建的臨時(shí)是如何正常刪除的？ 它們在slave上的存儲形式又是怎么樣的？

以下簡單分析一下2，3 問題

分析：
1，臨時(shí)表只對當(dāng)前會話可見，連接斷開時(shí)，自動刪除！

2，查看臨時(shí)表，在Master的binlog中的記錄形式

2.1 MySQL對臨時(shí)表的復(fù)制，如果在mixed的binlog_format情況下，會以Statement的形式記錄到binlog中，當(dāng)然也可以用Row形式
，因?yàn)榕R時(shí)表是基于Session的（也可以說是Connection的），所以在復(fù)制中，MySQL會把線程ID添加到臨時(shí)表操作的事件中
，此時(shí)的臨時(shí)表是屬于某個(gè)正在運(yùn)行的Thread。 通過 mysqlbinlog來查看binlog,可以看到事件上綁定了thread_id=297就是這個(gè)臨時(shí)表的
宿主線程，當(dāng)然你也可以用Show processlist;來查看這個(gè)線程
# 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可以看到，有一個(gè)SET @@session.pseudo_thread_id=297，這個(gè)記錄salve的Sql_thread在執(zhí)行此binlog時(shí)，
會創(chuàng)建一個(gè)id號為297的"偽線程",  這樣在slave上創(chuàng)建的此臨時(shí)表cache2的宿主線程就此偽線程。

2.3 當(dāng)stop slave后，Slave_SQL線程已經(jīng)關(guān)閉，但此時(shí)在Slave的臨時(shí)表是還存在的，可以通過在Slave上查看Status變量Slave_open_temp_tables，其實(shí)是不為0的，也就說由Master復(fù)制來的臨時(shí)表還存在，因?yàn)檫@些臨時(shí)表是所屬于Master上創(chuàng)建臨時(shí)表的Thread的Thread_ID對應(yīng)的pseudo_thread，所以雖然Slave_SQL connection已經(jīng)斷開，但臨時(shí)表是還存在的。
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停掉后，臨時(shí)表還存在的原因。


3, Slave中的臨時(shí)是如何刪除的呢？

當(dāng)在Master的創(chuàng)建此臨時(shí)表的Session斷開后，binlogw會記錄一個(gè)Drop臨時(shí)表的事件，
這樣Slave對應(yīng)的臨時(shí)表也就被刪除了，可以查看臨時(shí)的狀態(tài)變量可得。從下面可以看，在我測試環(huán)境中Master上
thread_id=297的這個(gè)connection, 一共創(chuàng)建了3個(gè)臨時(shí)表，當(dāng)退出mysql后，Master的binlog中會記錄一個(gè)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

當(dāng)slave 的slave_sql_thread執(zhí)行此事件，也就把剛才創(chuàng)建的臨時(shí)表刪除了。


4, Slave創(chuàng)建的臨時(shí)表放在哪里呢？
MySQL創(chuàng)建的臨時(shí)表的文件，其實(shí)是放在show variables like 'tmp_dir'這個(gè)變量指定的目錄下
默認(rèn)情況是下在/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

同時(shí)也會在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

也可以根據(jù)mysqld打開的文件來查看。

5，關(guān)于臨時(shí)表有兩個(gè)問題：
5.1 在重新啟動Slave 的mysqld服務(wù)時(shí)，Stop Slave后，一定要檢查Slave_open_temp_tables 這個(gè)狀態(tài)值是否已經(jīng)是0，如果不是，
要重新start slave, 再stop slave，查看，直接是0后，才stop mysql。因?yàn)閙ysql重新啟動后，在Slave上的所有臨時(shí)表都沒有了，這樣重新進(jìn)行復(fù)制時(shí)
后面還有對臨時(shí)表的操作的binlog事件，因?yàn)镾lave上的臨時(shí)表已不存在，此時(shí)肯定會出錯(cuò)了。

5.2 在用binlog進(jìn)行point_in_time恢復(fù)數(shù)據(jù)庫時(shí)，一定要注意，把所有的binlog放在同一個(gè)session里面執(zhí)行，否則，可能導(dǎo)致臨時(shí)表操作失敗

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

RogerZhuo

回復(fù) 2# kerlion
謝謝支持，只是個(gè)人理解（沒有老大審核），如果有錯(cuò)誤的地方希望牛牛批正， 分享事小， 誤人子弟事大。

RogerZhuo

回復(fù) 4# kerlion

是的，我上面說的也是，stop slave(也就是io/sql兩個(gè)線程都停止）， 此時(shí)的臨時(shí)表還是在的，但是如果把mysqld停掉后，此時(shí)的臨時(shí)表就不存在了，
當(dāng)我們r(jià)estart mysql, 再start slave；這個(gè)時(shí)候就會出錯(cuò)了。
   

ooooldman

希望多出這樣的帖子