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