原创:转载请注明出处
本文参考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 T@3: | | | | | | | | | | | <my_register_filename 204="" t@3:="" |="" my_realpath
T@3: | | | | | | | | | | | 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 */