由于业务写入了一条大事务,导致 MySQL 的 binlog 膨胀。在解析大的 binlog 时,经常会遇到这个问题,导致无法解析,没有其他工具的情况下,很难分析问题。
[root@xuzong mysql]# ls -lh mysql-bin.003300 -rw-r----- 1 my3696 mysql 6.7G Oct 30 16:24 mysql-bin.003300 [root@xuzong mysql]# /usr/local/mysql-5.7.35/bin/mysqlbinlog -vv mysql-bin.003300 > 1.sql mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.334z3P' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device) mysqlbinlog: Error writing file '/tmp/tmp.0Uirch' (Errcode: 28 - No space left on device)
[root@mysql mysql]# cat my.cnf | grep tmpdir tmpdir = /data1/dbatemp猜测二
问题分析
只能看看源码,看一下 mysqlbinlog 到底是怎么获取 tmpdir 的。mysqbinlog.cc int main(int argc, char** argv) { ........ MY_TMPDIR tmpdir; tmpdir.list= 0; if (!dirname_for_local_load) { if (init_tmpdir(&tmpdir, 0)) exit(1); dirname_for_local_load= my_strdup(PSI_NOT_INSTRUMENTED, my_tmpdir(&tmpdir), MY_WME); } ........ } mf_tempdir.cc my_bool init_tmpdir(MY_TMPDIR *tmpdir, const char *pathlist) { char *end, *copy; char buff[FN_REFLEN]; DBUG_ENTER("init_tmpdir"); DBUG_PRINT("enter", ("pathlist: %s", pathlist ? pathlist : "NULL")); Prealloced_array<char*, 10, true> full_list(key_memory_MY_TMPDIR_full_list); memset(tmpdir, 0, sizeof(*tmpdir)); if (!pathlist || !pathlist[0]) { /* Get default temporary directory */ pathlist=getenv("TMPDIR"); /* Use this if possible */ //这里能看到是获取的机器环境变量 #if defined(_WIN32) if (!pathlist) pathlist=getenv("TEMP"); //windows是temp if (!pathlist) pathlist=getenv("TMP"); //linux是tmp #endif if (!pathlist || !pathlist[0]) pathlist= DEFAULT_TMPDIR; } ........ }
好家伙,竟然是获取的机器环境变量,那么这个问题就解决了。
[root@mysql mysql]# export TMPDIR="/data1" [root@mysql mysql]# echo ${TMPDIR:-/tmp} [root@xuzong mysql]# /usr/local/mysql-5.7.35/bin/mysqlbinlog -vv mysql-bin.003300 > 1.sql总结