Resolving MySQL binlog parsing errors caused by insufficient temporary directory space

Fault phenomenon : A large transaction caused the MySQL binary log (binlog) to expand, and when attempting to parse the binlog with mysqlbinlog , the tool fails with "No space left on device" errors.

Fault reproduction :

[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)
...

Guess :

It might be related to the tmpdir setting in the MySQL configuration, which would require a MySQL restart to change.

Is there a way to modify the temporary directory without restarting MySQL?

Verification of guess 1 :

Checking my.cnf shows that the configured tmpdir is /data1/dbatemp , so the first guess is incorrect.

[root@mysql mysql]# cat my.cnf | grep tmpdir
tmpdir                  = /data1/dbatemp

Verification of guess 2 :

Online searches mainly discuss how to handle a full temporary table, but do not provide a clear method to change the temporary directory used by mysqlbinlog .

Problem analysis :

Inspecting the MySQL source reveals how mysqlbinlog obtains the temporary directory.

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

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"));
    ...
    if (!pathlist || !pathlist[0]) {
        /* Get default temporary directory */
        pathlist = getenv("TMPDIR"); // use environment variable if possible
        #if defined(_WIN32)
        if (!pathlist) pathlist = getenv("TEMP"); // Windows
        if (!pathlist) pathlist = getenv("TMP"); // Linux
        #endif
        if (!pathlist || !pathlist[0])
            pathlist = DEFAULT_TMPDIR;
    }
    ...
}

The code shows that mysqlbinlog ultimately uses the TMPDIR environment variable (or the system default) to locate a temporary directory.

Problem handling :

Temporarily change the machine's TMPDIR variable and re‑run the command.

[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

Summary :

When encountering issues, first consult the manual, then examine the source code if needed.

Consider using dedicated binlog parsing tools such as bin2sql .

Check the slow‑query log for related entries.

Supplement :

The MySQL official documentation also mentions that the TMPDIR environment variable controls the temporary directory used by mysqlbinlog :

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.

Reference: MySQL Documentation – mysqlbinlog