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

Are you trying to locate where your MySQL keeps some of the files? 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.

testuser ~ # lsof -nc mysqld | grep -vE '(.so(..*)?$|.frm|.MY?|.ibd|ib_logfile|ibdata|TCP)'

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

Leave a Reply

Your email address will not be published. Required fields are marked *