How to Resolve MySQL Binlog Parsing Issues Due to Large Size

1. Background

A large transaction write caused MySQL's binlog to expand. Parsing large binlogs often leads to failures, making issue analysis difficult without additional tools.

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

‍

Hypotheses

The tmpdir in the configuration file might be the issue, but modifying it requires a MySQL restart.

Is it possible to change the temporary space without restarting MySQL?

3. Hypothesis Verification

3.1 Hypothesis One

Checking the my.cnf setting for tmpdir revealed it wasn't the parameter in use, so hypothesis one was incorrect.

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

‍

3.2 Hypothesis Two

Most online solutions address temporary table space issues, similar to hypothesis one, with no clear method to modify the temporary space used by mysqlbinlog during parsing.

4. Problem Analysis

Reviewing the source code of mysqlbinlog revealed how it obtains 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 */ // Here it is found that it uses the machine environment variable
#if defined(_WIN32)
    if (!pathlist)
      pathlist = getenv("TEMP"); // Windows uses TEMP
    if (!pathlist)
      pathlist = getenv("TMP");  // Linux uses TMP
#endif
    if (!pathlist || !pathlist[0])
      pathlist = DEFAULT_TMPDIR;
  }
........
}

‍

It turned out that it uses the machine environment variable, resolving the issue.

5. Problem Resolution

Temporarily modify the machine's tmpdir variable.

[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

‍

6. Summary

Always check the source code when encountering issues.

Consider using binlog parsing tools like bin2sql.

Check slow query logs for records.

Supplement

This issue is addressed in the MySQL official documentation.

When running mysqlbinlog against a large binary log, ensure the filesystem has enough space for the resulting files. Configure the directory mysqlbinlog uses for temporary files using the TMPDIR environment variable.

‍

‍