由于业务写入了一条大事务,导致 MySQL 的 binlog 膨胀。在解析大的 binlog 时,经常会遇到这个问题,导致无法解析,没有其他工具的情况下,很难分析问题。
oot@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)
看一下 my.cnf 设置的 tmpdir,发现并不是使用的这个参数,看来猜测一不对。
[root@mysql mysql]# cat my.cnf | grep tmpdir
tmpdir = /data1/dbatemp
网上搜了一下,大部分是讲临时表满怎么解决的,也就是猜测一的方案,并没有很明确的方法来修改 mybinlog 解析时,所使用的的临时句柄占用空间。
只能看看源码,看一下 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;
}
........
}
好家伙,竟然是获取的机器环境变量,那么这个问题就解决了。
临时修改一下机器的 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
原来这个问题在 MySQL 官方手册[1] 中有所描述,在此做一个补充。
When running mysqlbinlog against a large binary log, be careful that the filesystem has enough space for the resulting files. To configure the directory that mysqlbinlog uses for temporary files, use the TMPDIR environment variable.
mysqlbinlog: https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html
本文关键字:#MySQL# #日志# #源码#
网站声明:如果转载,请联系本站管理员。否则一切后果自行承担。
加入交流群
请使用微信扫一扫!