How to find MySQL binary logs, error logs, temporary files?

Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.

The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.

A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.

garfield ~ # lsof -nc mysqld | grep -vE '(.so(..*)?$|.frm|.MY?|.ibd|ib_logfile|ibdata|TCP)'
COMMAND   PID  USER   FD   TYPE      DEVICE  SIZE/OFF     NODE NAME
mysqld  30257 mysql  cwd    DIR       253,1      4096 25346049 /data/mysql
mysqld  30257 mysql  rtd    DIR       253,2      4096        2 /
mysqld  30257 mysql  txt    REG       253,2  10965992   839485 /usr/sbin/mysqld
mysqld  30257 mysql    0u   CHR       136,8       0t0       11 /dev/pts/8
mysqld  30257 mysql    1w   REG       253,4     10229   270851 /var/log/mysql/mysql.err
mysqld  30257 mysql    2w   REG       253,4     10229   270851 /var/log/mysql/mysql.err
mysqld  30257 mysql    3u   REG       253,1      2376 10305537 /data/mysql/mysql-bin.index
mysqld  30257 mysql    5u   REG       253,5         0       81 /tmp/ib8iroKe (deleted)
mysqld  30257 mysql    6u   REG       253,5         0       82 /tmp/ib8WXRbx (deleted)
mysqld  30257 mysql    7u   REG       253,5         0       83 /tmp/ibcmlCEP (deleted)
mysqld  30257 mysql    8u   REG       253,5         0       84 /tmp/ibGzgP9q (deleted)
mysqld  30257 mysql   12u   REG       253,5         0       85 /tmp/ibDymUYK (deleted)
mysqld  30257 mysql   13w   REG       253,4     45502   270719 /var/log/mysql/slow.log
mysqld  30257 mysql   15w   REG       253,1       107 77398029 /data/mysql/mysql-bin.000072
mysqld  30257 mysql   16u  unix 0xffff88022f1a2f40       0t0 35379259 /var/run/mysqld/mysqld.sock

What information can we find here?

mysqld 30257 mysql cwd DIR 253,1 4096 25346049 /data/mysql
MySQL data files are in /data/mysql. cwd stands for current working directory.

mysqld 30257 mysql 1w REG 253,4 10229 270851 /var/log/mysql/mysql.err
mysqld 30257 mysql 2w REG 253,4 10229 270851 /var/log/mysql/mysql.err
MySQL writes log messages and errors into /var/log/mysql/mysql.err. 1w and 2w are file descriptors 1 (stdout) and 2 (stderr) and both were redirected from console to the specified file.

mysqld 30257 mysql 13w REG 253,4 45502 270719 /var/log/mysql/slow.log
MySQL slow log can be found in /var/log/mysql.

mysqld 30257 mysql 3u REG 253,1 2376 10305537 /data/mysql/mysql-bin.index mysqld 30257 mysql 15w REG 253,1 107 77398029 /data/mysql/mysql-bin.000072
MySQL binary logs are in /data/mysql. If binary logging was enabled there will always be at least two files with the characteristic suffixes.

mysqld 30257 mysql 5u REG 253,5 0 81 /tmp/ib8iroKe (deleted)
It uses /tmp for temporary file storage (e.g. temporary tables).

mysqld 30257 mysql 16u unix 0xffff88022f1a2f40 0t0 35379259 /var/run/mysqld/mysqld.sock
MySQL socket file for local connections is /var/run/mysqld/mysqld.sock. It can be easily recognised by file descriptor type column, which in case of this file will be showing unix.

Of course every database may use different file names, but it is usually easy enough to sort them out (e.g. mysql-error instead of mysql.err).

Blog