MYSQL 什麼時候同步binlog檔案以及它的臨時檔案在哪裡？
原創：轉載請註明出處
本文參考5.7官方文件以及對MYSQL進行trace和GDB，使用自制binlog解析工具
infobin 獲取可以通過百度雲盤
http://pan.baidu.com/s/1jHIWUN0
幫助：
http://blog.itpub.net/7728585/viewspace-2133534/
使用引擎innodb以及mysql 5.7.13
1、什麼時候生成event以及何時同步到真正的binlog檔案?
我們知道在語句執行期間binlog會記錄到binlog_cache_size，但是超過binlog_cache_size的會
放到臨時檔案，等到commit的時候寫到binlog檔案中，當然是考慮sync_binlog = 1的情況下
關於這段在文件中也有描述：
Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional
tables such as InnoDBtables are cached until a COMMITstatement is received by the server. At that point,
mysqldwrites the entire transaction to the binary log before the COMMIT is executed.
When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_sizeto buffer
statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction.
The temporary file is deleted when the thread ends.
實際上我們可以觀察2個值如下：
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 1 |
來觀察。
其實語句執行期間binlog event會寫入到buffer或者temfile中，commit的時候同步到binlog真正的檔案中
2、臨時檔案在哪裡？
在文件中只是描述了大於binlog_cache_size 會使用臨時檔案，那麼這個臨時檔案放到哪裡呢？
其實他是一個臨時檔案LINUX下使用mkstemp() API建立的，放到了引數tmpdir下面，但是因為是臨時
檔案ls是看不到的，但是lsof能看到，而且它佔用空間，隨著執行緒的結束而釋放類似如下：
lsof|grep delete
名字應該是ML開頭類似如下名字
/root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/tmp/mysqld.1/MLGWFO0T (deleted)
我的tempdir目錄是/root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/tmp/mysqld.1/
官方文件也有描述：
When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer
statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction.
The temporary file is deleted when the thread ends.
3、建立過程
函式 real_open_cached_file是總的介面
T@3: | | | | | | | | | >real_open_cached_file
T@3: | | | | | | | | | | >create_temp_file ---建立臨時檔案
T@3: | | | | | | | | | | | enter: dir: /root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/tmp/mysqld.1, prefix: ML
T@3: | | | | | | | | | | | >convert_dirname
T@3: | | | | | | | | | | | my_register_filename
T@3: | | | | | | | | | | | | >my_raw_malloc
T@3: | | | | | | | | | | | | | my: size: 111 my_flags: 16
T@3: | | | | | | | | | | | | | exit: ptr: 0x7fffd0bc0f70
T@3: | | | | | | | | | | | | <my_raw_malloc 219="" t@3:="" |="" exit:="" fd:="" 65="" <my_register_filename="" 204="" my_realpath="" info:="" executing="" realpath T@3: | | | | | | | | | | my_delete ---刪除臨時檔案保留檔案描述符供使用
T@3: | | | | | | | | | | | my: name /root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/tmp/mysqld.1/MLjBqtsQ MyFlags 16
T@3: | | | | | | | | | | <my_delete 41="" t@3:="" |="" <real_open_cached_file="" 85="" delete過後這個檔案在tmp目錄下就ls看不到了="" 我使用gdb打了斷點，在執行my_delete這個檔案是可見的， [root@testmy mysqld.1]# ls -lrt
total 0
-rw------- 1 root root 0 Feb 15 07:44 MLjBqtsQ
但是my_delete後就看不到了但是lsof看得到
[root@testmy mysqld.1]# lsof|grep MLjBqtsQ
mysqld 3267 root 66u REG 8,3 0 6700113 /root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/tmp/mysqld.1/MLjBqtsQ (deleted)
標記為MLjBqtsQ (deleted) ：ML為固定jBqtsQ為隨機
4、臨時檔案怎麼使用
---在語句執行期間當然是將event不斷的寫入到臨時檔案
---當commit通過總的介面進行臨時檔案的到binlogfile的拷貝
MYSQL_BIN_LOG::write_cache(THD *, binlog_cache_data *, bool)
其中
1、首先將binlog_cache_size中全部的資料重新整理到tempfile中如下：
T@4: | | | | | | | | | | | | >reinit_io_cache
T@4: | | | | | | | | | | | | | enter: cache: 0x7fffd0c79b70 type: 1 seek_offset: 0 clear_cache: 0
T@4: | | | | | | | | | | | | | >my_b_flush_io_cache
T@4: | | | | | | | | | | | | | | enter: cache: 0x7fffd0c79b70
T@4: | | | | | | | | | | | | | | >my_write
T@4: | | | | | | | | | | | | | | | my: fd: 60 Buffer: 0x7fffd0fb6720 Count: 3395 MyFlags: 20
T@4: | | | | | | | | | | | | | | <my_write 115="" t@4:="" |="" <my_b_flush_io_cache="" 1583="" <reinit_io_cache="" 387="" ="" 這裡的fd:60就是我的臨時檔案的檔案描述符="" 2、接下來進行一個event一個event的從temp到binlog進行copy，copy的時候需要一個buffer="" 這個buffer的大小應該是event的大小 T@4: | | | | | | | | | | | | >my_read
T@4: | | | | | | | | | | | | | my: fd: 60 Buffer: 0x7fffd0fb6720 Count: 8192 MyFlags: 16
T@4: | | | | | | | | | | | | Binlog_event_writer::write_event_part
T@4: | | | | | | | | | | | | Binlog_event_writer::write_event_part
T@4: | | | | | | | | | | | | Binlog_event_writer::write_event_part
T@4: | | | | | | | | | | | | | >my_b_flush_io_cache
T@4: | | | | | | | | | | | | | | enter: cache: 0x2dfd5c8
T@4: | | | | | | | | | | | | | | >my_write
T@4: | | | | | | | | | | | | | | | my: fd: 36 Buffer: 0x33b8e50 Count: 8192 MyFlags: 52
T@4: | | | | | | | | | | | | | | <my_write 115="" t@4:="" |="" <my_b_flush_io_cache="" 1583="" <binlog_event_writer::write_event_part="" 1033="" 畫一張圖：=""
5、通過gdb和工具infobin進行驗證(使用mysqlbinlog也可以但是不太好觀察)
這裡將展示一個大的DML語句然後觀察一條一條event的寫入：
mysql> show variables like '%binlog_cache_size%';
+-----------------------+----------------------+
| Variable_name | Value |
+-----------------------+----------------------+
| binlog_cache_size | 4096 |
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 24576 |
+----------+
gdb 斷點打到 MYSQL_BIN_LOG::write_cache上
(gdb) b MYSQL_BIN_LOG::write_cache
Breakpoint 5 at 0x1853f2c: file /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc, line 7682.
mysql> delete from test;
斷點觸發，然後再打一個斷點到
Breakpoint 5, MYSQL_BIN_LOG::write_cache (this=0x2dfd280, thd=0x7fffe8016730, cache_data=0x7fffe812d938, writer=0x7fffec12c810)
at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:7682
7682 DBUG_ENTER("MYSQL_BIN_LOG::write_cache(THD *, binlog_cache_data *, bool)");
(gdb) b my_write
Breakpoint 6 at 0x18e51c0: file /root/mysql5.7.14/percona-server-5.7.14-7/mysys/my_write.c, line 43.
多進行幾次c繼續發現binlog記錄如下：
------>Delete Event:Pos:340(0X154) N_pos:8552(0X2168) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第一個event)
------>Delete Event:Pos:8552(0X2168) N_pos:16764(0X417c) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第二個event)
analyze_binlog:fread ERROR main.c 436
a_binlog analyze_binlog error main.c 596
ERROR:a_binlog fun error
報錯是因為binlog沒有正確結束，這肯定的。
然後再次c
------>Delete Event:Pos:340(0X154) N_pos:8552(0X2168) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第一個event)
------>Delete Event:Pos:8552(0X2168) N_pos:16764(0X417c) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第二個event)
------>Delete Event:Pos:16764(0X417c) N_pos:24976(0X6190) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第三個event)
analyze_binlog:fread ERROR main.c 436
a_binlog analyze_binlog error main.c 596
ERROR:a_binlog fun error
再次c
------>Delete Event:Pos:340(0X154) N_pos:8552(0X2168) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第一個event)
------>Delete Event:Pos:8552(0X2168) N_pos:16764(0X417c) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第二個event)
------>Delete Event:Pos:16764(0X417c) N_pos:24976(0X6190) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第三個event)
------>Delete Event:Pos:24976(0X6190) N_pos:33188(0X81a4) Time:1487120852 Event_size:8212(bytes)
Dml on table: test.test table_id:108 Gno:0 (第四個event)
analyze_binlog:fread ERROR main.c 436
a_binlog analyze_binlog error main.c 596
ERROR:a_binlog fun error
如此我們證明上面的說法，如此反覆可以看到一個event一個event的不斷從tempfile寫入到binlog
6、原始碼介面展示
建立臨時檔案：
點選(此處)摺疊或開啟
-
File create_temp_file(char *to, const char *dir, const char *prefix,
-
int mode, myf MyFlags)
-
{
-
File file= -1;
-
#ifdef _WIN32
-
TCHAR path_buf[MAX_PATH-14];
-
#endif
-
-
DBUG_ENTER("create_temp_file");
-
DBUG_PRINT("enter", ("dir: %s, prefix: %s", dir, prefix));
-
#if defined(_WIN32)
-
-
/*
-
Use GetTempPath to determine path for temporary files.
-
This is because the documentation for GetTempFileName
-
has the following to say about this parameter:
-
"If this parameter is NULL, the function fails."
-
*/
-
if (!dir)
-
{
-
if(GetTempPath(sizeof(path_buf), path_buf) > 0)
-
dir = path_buf;
-
}
-
/*
-
Use GetTempFileName to generate a unique filename, create
-
the file and release it's handle
-
- uses up to the first three letters from prefix
-
*/
-
if (GetTempFileName(dir, prefix, 0, to) == 0)
-
DBUG_RETURN(-1);
-
-
DBUG_PRINT("info", ("name: %s", to));
-
-
/*
-
Open the file without the "open only if file doesn't already exist"
-
since the file has already been created by GetTempFileName
-
*/
-
if ((file= my_open(to, (mode & ~O_EXCL), MyFlags)) < 0)
-
{
-
/* Open failed, remove the file created by GetTempFileName */
-
int tmp= my_errno();
-
(void) my_delete(to, MYF(0));
-
set_my_errno(tmp);
-
}
-
-
#else /* mkstemp() is available on all non-Windows supported platforms. */
-
{
-
char prefix_buff[30];
-
uint pfx_len;
-
File org_file;
-
-
pfx_len= (uint) (my_stpcpy(my_stpnmov(prefix_buff,
-
prefix ? prefix : "tmp.",
-
sizeof(prefix_buff)-7),"XXXXXX") -
-
prefix_buff);
-
if (!dir && ! (dir =getenv("TMPDIR")))
-
dir= DEFAULT_TMPDIR;
-
if (strlen(dir)+ pfx_len > FN_REFLEN-2)
-
{
-
errno=ENAMETOOLONG;
-
set_my_errno(ENAMETOOLONG);
-
DBUG_RETURN(file);
-
}
-
my_stpcpy(convert_dirname(to,dir,NullS),prefix_buff);
-
org_file=mkstemp(to);
-
if (mode & O_TEMPORARY)
-
(void) my_delete(to, MYF(MY_WME));
-
file=my_register_filename(org_file, to, FILE_BY_MKSTEMP,
-
EE_CANTCREATEFILE, MyFlags);
-
/* If we didn't manage to register the name, remove the temp file */
-
if (org_file >= 0 && file < 0)
-
{
-
int tmp=my_errno();
-
close(org_file);
-
(void) my_delete(to, MYF(MY_WME));
-
set_my_errno(tmp);
-
}
-
}
-
#endif
-
if (file >= 0)
-
{
-
mysql_mutex_lock(&THR_LOCK_open);
-
my_tmp_file_created++;
-
mysql_mutex_unlock(&THR_LOCK_open);
-
}
-
DBUG_RETURN(file);
- }
點選(此處)摺疊或開啟
-
int my_delete(const char *name, myf MyFlags)
-
{
-
int err;
-
DBUG_ENTER("my_delete");
-
DBUG_PRINT("my",("name %s MyFlags %d", name, MyFlags));
-
-
if ((err = unlink(name)) == -1)
-
{
-
set_my_errno(errno);
-
if (MyFlags & (MY_FAE+MY_WME))
-
{
-
char errbuf[MYSYS_STRERROR_SIZE];
-
my_error(EE_DELETE, MYF(0),
-
name, errno, my_strerror(errbuf, sizeof(errbuf), errno));
-
}
-
}
-
else if ((MyFlags & MY_SYNC_DIR) &&
-
my_sync_dir_by_file(name, MyFlags))
-
err= -1;
-
DBUG_RETURN(err);
- } /* my_delete */
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2133589/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 檔案-臨時檔案
- MySQL:為什麼lsof會看到這麼多臨時檔案MySql
- 臨時檔案
- MYSQL 5.7中臨時檔案的使用MySql
- 臨時資料檔案
- MySQL什麼時候會使用內部臨時表?MySql
- Oracle資料檔案和臨時檔案的管理Oracle
- 臨時檔案的順序和絕對檔案號
- sersync 實時同步檔案
- Yoink for Mac(臨時檔案拖放助手)Mac
- 怎麼清理temp資料夾的臨時檔案？Win7系統電腦temp臨時檔案的清理方法Win7
- Win10臨時資料夾的路徑在哪 win10系統臨時檔案如何找到Win10
- datafile.sql 檢視資料檔案和臨時檔案SQL
- dataguard新增臨時資料檔案的bug
- Yoink for Mac(臨時檔案儲存助手)Mac
- Mac臨時檔案儲存助手：YoinkMac
- 在Docker中，Docker配置檔案在哪裡以及如何修改？Docker
- javaWeb不生成臨時檔案實現壓縮檔案下載JavaWeb
- php.ini 檔案在哪裡?PHP
- Git操作檔案的時候手賤了，怎麼恢復？Git
- PHP 上傳檔案找不到 tmp_name 臨時檔案的問題PHP
- 移動資料檔案、系統表空間檔案、臨時表空間檔案
- windows系統清理磁碟臨時檔案，及緩衝檔案，及離線檔案和空閒檔案Windows
- 檔案同步是什麼？解析6個最佳的檔案同步應用軟體
- 定時刪除bdump裡的trace檔案
- 定時刪除udump裡的trace檔案
- rsync + lsyncd 檔案實時同步/備份
- /etc/oratab這個檔案時幹什麼用的？
- cad自動儲存的檔案在哪裡 cad檔案一般儲存在哪裡
- 當資料檔案表空間丟失的時候怎麼恢復該資料檔案
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- RMAN備份時候檔案壞塊的處理
- EBS應用臨時檔案或資料的清理
- win10如何刪除系統更新臨時檔案_windows10更新刪除臨時檔案操作方法Win10Windows
- mysql binlog_format和臨時表MySqlORM
- MySQL二進位制檔案（binlog）MySql
- tidb-server 的配置檔案在哪裡?TiDBServer
- 檔案協同工具的優點在哪裡？