转载

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 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="" 画一张图:

MYSQL 什么时候同步binlog文件以及它的临时文件在哪里?

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、源码接口展示
建立临时文件:

点击(此处)折叠或打开

  1. File create_temp_file(char *to, const char *dir, const char *prefix,
  2.          int mode, myf MyFlags)
  3. {
  4.   File file= -1;
  5. #ifdef _WIN32
  6.   TCHAR path_buf[MAX_PATH-14];
  7. #endif

  8.   DBUG_ENTER("create_temp_file");
  9.   DBUG_PRINT("enter", ("dir: %s, prefix: %s", dir, prefix));
  10. #if defined(_WIN32)

  11.    /*
  12.      Use GetTempPath to determine path for temporary files.
  13.      This is because the documentation for GetTempFileName
  14.      has the following to say about this parameter:
  15.      "If this parameter is NULL, the function fails."
  16.    */
  17.    if (!dir)
  18.    {
  19.      if(GetTempPath(sizeof(path_buf), path_buf) > 0)
  20.        dir = path_buf;
  21.    }
  22.    /*
  23.      Use GetTempFileName to generate a unique filename, create
  24.      the file and release it's handle
  25.       - uses up to the first three letters from prefix
  26.    */
  27.   if (GetTempFileName(dir, prefix, 0, to) == 0)
  28.     DBUG_RETURN(-1);

  29.   DBUG_PRINT("info", ("name: %s", to));

  30.   /*
  31.     Open the file without the "open only if file doesn't already exist"
  32.     since the file has already been created by GetTempFileName
  33.   */
  34.   if ((file= my_open(to, (mode & ~O_EXCL), MyFlags)) < 0)
  35.   {
  36.     /* Open failed, remove the file created by GetTempFileName */
  37.     int tmp= my_errno();
  38.     (void) my_delete(to, MYF(0));
  39.     set_my_errno(tmp);
  40.   }

  41. #else /* mkstemp() is available on all non-Windows supported platforms. */
  42.   {
  43.     char prefix_buff[30];
  44.     uint pfx_len;
  45.     File org_file;

  46.     pfx_len= (uint) (my_stpcpy(my_stpnmov(prefix_buff,
  47.                  prefix ? prefix : "tmp.",
  48.                  sizeof(prefix_buff)-7),"XXXXXX") -
  49.          prefix_buff);
  50.     if (!dir && ! (dir =getenv("TMPDIR")))
  51.       dir= DEFAULT_TMPDIR;
  52.     if (strlen(dir)+ pfx_len > FN_REFLEN-2)
  53.     {
  54.       errno=ENAMETOOLONG;
  55.       set_my_errno(ENAMETOOLONG);
  56.       DBUG_RETURN(file);
  57.     }
  58.     my_stpcpy(convert_dirname(to,dir,NullS),prefix_buff);
  59.     org_file=mkstemp(to);
  60.     if (mode & O_TEMPORARY)
  61.       (void) my_delete(to, MYF(MY_WME));
  62.     file=my_register_filename(org_file, to, FILE_BY_MKSTEMP,
  63.              EE_CANTCREATEFILE, MyFlags);
  64.     /* If we didn't manage to register the name, remove the temp file */
  65.     if (org_file >= 0 && file < 0)
  66.     {
  67.       int tmp=my_errno();
  68.       close(org_file);
  69.       (void) my_delete(to, MYF(MY_WME));
  70.       set_my_errno(tmp);
  71.     }
  72.   }
  73. #endif
  74.   if (file >= 0)
  75.   {
  76.     mysql_mutex_lock(&THR_LOCK_open);
  77.     my_tmp_file_created++;
  78.     mysql_mutex_unlock(&THR_LOCK_open);
  79.   }
  80.   DBUG_RETURN(file);
  81. }
删除临时文件,保留文件描述符供使用

点击(此处)折叠或打开

  1. int my_delete(const char *name, myf MyFlags)
  2. {
  3.   int err;
  4.   DBUG_ENTER("my_delete");
  5.   DBUG_PRINT("my",("name %s MyFlags %d", name, MyFlags));

  6.   if ((err = unlink(name)) == -1)
  7.   {
  8.     set_my_errno(errno);
  9.     if (MyFlags & (MY_FAE+MY_WME))
  10.     {
  11.       char errbuf[MYSYS_STRERROR_SIZE];
  12.       my_error(EE_DELETE, MYF(0),
  13.                name, errno, my_strerror(errbuf, sizeof(errbuf), errno));
  14.     }
  15.   }
  16.   else if ((MyFlags & MY_SYNC_DIR) &&
  17.            my_sync_dir_by_file(name, MyFlags))
  18.     err= -1;
  19.   DBUG_RETURN(err);
  20. } /* my_delete */



正文到此结束
Loading...