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

  • gaopengtttt

    2017-02-15 16:45:05
  • MySQL
  • 原创

原创：转载请注明出处
本文参考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: | | | | | | | | | | | | 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: | | | | | | | | | | 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_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: | | | | | | | | | | | | | |

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

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

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

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

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

  46. #else /* mkstemp() is available on all non-Windows supported platforms. */
  47.   {
  48.     char prefix_buff[30];
  49.     uint pfx_len;
  50.     File org_file;

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

点击(此处)折叠或打开

  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));

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

               


请使用浏览器的分享功能分享到微信等