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.<\/p>\n
The obvious way is through examining database options in\u00a0<\/span>my.cnf<\/em>\u00a0<\/span>or looking at the output of\u00a0<\/span>SHOW GLOBAL VARIABLES<\/em>. 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.<\/p>\n 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.<\/p>\n What information can we find here?<\/p>\n Of course every database may use different file names, but it is usually easy enough to sort them out (e.g.\u00a0<\/span>mysql-error<\/em>instead of\u00a0<\/span>mysql.err<\/em>).<\/p>\n","protected":false},"excerpt":{"rendered":" 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\u00a0my.cnf\u00a0or looking at the output of\u00a0SHOW 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"ngg_post_thumbnail":0,"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[12,5],"tags":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5q9Zn-c2","jetpack-related-posts":[{"id":709,"url":"https:\/\/blog.box.kr\/?p=709","url_meta":{"origin":746,"position":0},"title":"[\ud38c] HOWTO: Turn off MySQL reverse DNS lookup to speed up response times","date":"2015-04-15","format":false,"excerpt":"\ub0b4\uc6a9\uc744 \uc694\uc57d \ud558\uc790\ub9cc.. mysql\uc774 \uc811\uc18d \ud560\ub54c\u00a0reverse DNS lookup\uc744 \ud558\ub294\ub370 \uc774\ub54c \uc2dc\uac04\uc774 \ub9ce\uc774 \uac78\ub9ac\ub2c8 \uc774\ub97c skip\ucc98\ub9ac \ud558\uba74 \uc811\uc18d \uc18d\ub3c4\uac00 \ub9ce\uc774 \ube68\ub77c \uc9c4\ub2e4\ub294 \uc774\uc57c\uae30.. \u00a0 \u00a0 http:\/\/developer.sugarcrm.com\/2012\/01\/10\/howto-turn-off-mysql-reverse-dns-lookup-to-speed-up-response-times\/ Our support team was recently working on a case where pages were taking 5 seconds or longer for each load, even after tackling all of\u2026","rel":"","context":"In "\uae30\uc220\uc790\ub8cc"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":803,"url":"https:\/\/blog.box.kr\/?p=803","url_meta":{"origin":746,"position":1},"title":"[scrap]mysqldump \uc635\uc158 \ubc0f \uc0ac\uc6a9\ubc95","date":"2015-05-18","format":false,"excerpt":"http:\/\/egloos.zum.com\/simonshin\/v\/2247509 \u00a0 1. mysqldump \uc635\uc158 \uc911 binary log \uc758 \ud3ec\uc9c0\uc158 \uc815\ubcf4 \uac19\uc774 dump \ud558\uae30 \/usr\/local\/mysql\/bin\/mysqldump -uroot -p --master-data=2\u00a0 > \/data\/\u2026\/backup.sql \uc774 \uba85\ub839\uc5b4 \uc911\uc5d0\uc11c \"--master-data=2\" \uc635\uc158\uc744 \uc8fc\uac8c \ub418\uba74 \ub364\ud504 \uc2dc\uc810\uc758 binary log\uc758 \ud3ec\uc9c0\uc158 \uc815\ubcf4\uac00 \uac19\uc774 Dump \ub418\uac8c \ub41c\ub2e4. Dump\ub41c \ud30c\uc77c\uc744 \uc5d0\ub514\ud130\ub85c \uc5f4\uc5b4\ubcf4\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \ub0b4\uc6a9\uc774 \ub098\uc628\ub2e4. 2. Table Lock \uac78\uae30 FLUSH\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":766,"url":"https:\/\/blog.box.kr\/?p=766","url_meta":{"origin":746,"position":2},"title":"install bugzilla with nginx on centos7","date":"2015-05-11","format":false,"excerpt":"Set the Hostname Before you begin installing and configuring the components described in this guide, please make sure you\u2019ve followed our instructions for setting your hostname. Issue the following commands to make sure it is set properly: hostname hostname -f The first command should show your short hostname, and the\u2026","rel":"","context":"In "Linux"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":687,"url":"https:\/\/blog.box.kr\/?p=687","url_meta":{"origin":746,"position":3},"title":"[\ud38c]MariaDB \uc124\uc815 \ud30c\uc77c my.cnf","date":"2015-04-13","format":false,"excerpt":"http:\/\/www.programkr.com\/blog\/MUDM5ADMwYT2.html \u00a0 \u00a0 # MariaDB database server configuration file. # # You can copy this file to one of: # - \"\/etc\/mysql\/my.cnf\" to set global options, # - \"~\/.my.cnf\" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help\u2026","rel":"","context":"In "\uae30\uc220\uc790\ub8cc"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":824,"url":"https:\/\/blog.box.kr\/?p=824","url_meta":{"origin":746,"position":4},"title":"[scrap]Step-by-Step Bugzilla Installation Guide for Linux","date":"2015-05-20","format":false,"excerpt":"http:\/\/www.thegeekstuff.com\/2010\/05\/install-bugzilla-on-linux\/ \u00a0 \u00a0 Bugzilla is the best open source bug tracking system. Very simple to use with lot of features. Bugzilla allows you to track the bugs and collaborate with developers and other teams in your organization effectively. This is a detailed step-by-step bugzilla installation guide for Linux. 1. Verify\u2026","rel":"","context":"In "\uae30\uc220"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/static.thegeekstuff.com\/wp-content\/uploads\/2008\/10\/bugzilla-logo-260x300.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":521,"url":"https:\/\/blog.box.kr\/?p=521","url_meta":{"origin":746,"position":5},"title":"MYSql \uc811\uc18d \ud504\ub85c\ud1a0\ucf5c \ubb38\uc81c","date":"2014-12-29","format":false,"excerpt":"Q1. \u00a0Mysql \uc811\uc18d\uc2dc\uc5d0 \"\u00a0MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)\u00a0\" \uc624\ub958 \ubc1c\uc0dd \uc6d0\uc778: \uc0ac\uc6a9\uc790 \uc554\ud638 \ud574\uc26c\uac12\uc744 \uc608\uc804 \uac83\uc744 \uc0ac\uc6a9\ud574\uc11c \uc0dd\uae30\ub294 \ubb38\uc81c \ud574\uacb0 1 : \u00a0\u00a0--skip-secure-auth \u00a0\uc635\uc158 \uc0ac\uc6a9 => \uc811\uc18d \ud560\ub54c Mysql\uc758 \ud074\ub77c\uc774\uc5b8\ud2b8\uc758 \uc554\ud638 Hash\uc218\uc900\uc744 \ub0ae \ucdb0\uc11c \uc811\uc18d ( \uc784\uc2dc\uc801\uc778 \ubc29\ubc95 )\u00a0\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/746"}],"collection":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=746"}],"version-history":[{"count":0,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/746\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=746"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=746"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=746"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}garfield ~ # lsof -nc mysqld | grep -vE '(.so(..*)?$|.frm|.MY?|.ibd|ib_logfile|ibdata|TCP)'\nCOMMAND PID USER FD TYPE DEVICE SIZE\/OFF NODE NAME\nmysqld 30257 mysql cwd DIR 253,1 4096 25346049 \/data\/mysql\nmysqld 30257 mysql rtd DIR 253,2 4096 2 \/\nmysqld 30257 mysql txt REG 253,2 10965992 839485 \/usr\/sbin\/mysqld\nmysqld 30257 mysql 0u CHR 136,8 0t0 11 \/dev\/pts\/8\nmysqld 30257 mysql 1w REG 253,4 10229 270851 \/var\/log\/mysql\/mysql.err\nmysqld 30257 mysql 2w REG 253,4 10229 270851 \/var\/log\/mysql\/mysql.err\nmysqld 30257 mysql 3u REG 253,1 2376 10305537 \/data\/mysql\/mysql-bin.index\nmysqld 30257 mysql 5u REG 253,5 0 81 \/tmp\/ib8iroKe (deleted)\nmysqld 30257 mysql 6u REG 253,5 0 82 \/tmp\/ib8WXRbx (deleted)\nmysqld 30257 mysql 7u REG 253,5 0 83 \/tmp\/ibcmlCEP (deleted)\nmysqld 30257 mysql 8u REG 253,5 0 84 \/tmp\/ibGzgP9q (deleted)\nmysqld 30257 mysql 12u REG 253,5 0 85 \/tmp\/ibDymUYK (deleted)\nmysqld 30257 mysql 13w REG 253,4 45502 270719 \/var\/log\/mysql\/slow.log\nmysqld 30257 mysql 15w REG 253,1 107 77398029 \/data\/mysql\/mysql-bin.000072\nmysqld 30257 mysql 16u unix 0xffff88022f1a2f40 0t0 35379259 \/var\/run\/mysqld\/mysqld.sock\n<\/pre>\n
mysqld 30257 mysql\u00a0<\/span>cwd\u00a0<\/span>DIR 253,1 4096 25346049 \/data\/mysql<\/code>
\nMySQL data files are in\u00a0<\/span>\/data\/mysql<\/em>.\u00a0<\/span>cwd<\/em>\u00a0<\/span>stands for current working directory.<\/p>\nmysqld 30257 mysql\u00a0<\/span>1w\u00a0<\/span>REG 253,4 10229 270851 \/var\/log\/mysql\/mysql.err<\/code>
\nmysqld 30257 mysql\u00a0<\/span>2w\u00a0<\/span>REG 253,4 10229 270851 \/var\/log\/mysql\/mysql.err<\/code>
\nMySQL writes log messages and errors into\u00a0<\/span>\/var\/log\/mysql\/mysql.err<\/em>.\u00a0<\/span>1w<\/em>\u00a0<\/span>and\u00a0<\/span>2w<\/em>\u00a0<\/span>are file descriptors 1 (stdout) and 2 (stderr) and both were redirected from console to the specified file.<\/p>\nmysqld 30257 mysql 13w REG 253,4 45502 270719 \/var\/log\/mysql\/slow.log<\/code>
\nMySQL slow log can be found in\u00a0<\/span>\/var\/log\/mysql<\/em>.<\/p>\nmysqld 30257 mysql 3u REG 253,1 2376 10305537 \/data\/mysql\/mysql-bin.index
\nmysqld 30257 mysql 15w REG 253,1 107 77398029 \/data\/mysql\/mysql-bin.000072<\/code>
\nMySQL binary logs are in\u00a0<\/span>\/data\/mysql<\/em>. If binary logging was enabled there will always be at least two files with the characteristic suffixes.<\/p>\nmysqld 30257 mysql 5u REG 253,5 0 81 \/tmp\/ib8iroKe\u00a0<\/span>(deleted)<\/code>
\nIt uses\u00a0<\/span>\/tmp<\/em>\u00a0<\/span>for temporary file storage (e.g. temporary tables).<\/p>\nmysqld 30257 mysql 16u\u00a0<\/span>unix\u00a0<\/span>0xffff88022f1a2f40 0t0 35379259 \/var\/run\/mysqld\/mysqld.sock<\/code>
\nMySQL socket file for local connections is\u00a0<\/span>\/var\/run\/mysqld\/mysqld.sock<\/em>. It can be easily recognised by file descriptor type column, which in case of this file will be showing\u00a0<\/span>unix<\/em>.<\/p>\n