原創：轉載請註明出處
本文參考5.7官方文件以及對MYSQL進行trace和GDB，使用自制binlog解析工具
infobin 獲取可以透過百度雲盤

幫助：
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 */

作者微信：

MYSQL 什麼時候同步binlog檔案以及它的臨時檔案在哪裡？

相關文章