{"id":2981,"date":"2010-12-07T12:24:44","date_gmt":"2010-12-07T10:24:44","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2981"},"modified":"2010-12-07T12:24:44","modified_gmt":"2010-12-07T10:24:44","slug":"wheres-my-cnf-file","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/wheres-my-cnf-file","title":{"rendered":"Where’s my cnf file?"},"content":{"rendered":"

So you have a running MySQL server, it’s working well and everyone’s happy. You want to make a minor change to the configuration file, so you edit the file, restart MySQL – but the change doesn’t catch!<\/p>\n

Or maybe you want to check that some global variable has not been dynamically changed without an update to the configuration file. But the configuration file doesn’t make any sense — it looks like nothing is common between the file and the server.<\/p>\n

Wait, which my.cnf<\/strong> file does MySQL read? Rather, which my.cnf<\/strong> files<\/em>?<\/p>\n

Ever happened to you? If you’re well organized, and only keep a single \/etc\/my.cnf<\/strong> file, you know exactly where everything is. But some systems are messier, with lots<\/em> of configuration files hanging around. Which ones apply?<\/p>\n

Let’s find out which configuration files apply.<\/p>\n

No direct information<\/h4>\n

It would all be easier if we could just SHOW GLOBAL VARIABLES LIKE ‘configuration_files_that_this_server_has_read_list’<\/strong>. There isn’t such an option.<\/p>\n

The MySQL documentation explains<\/a> about the configuration files search path, and that’s one path you can follow. Also, you can detect another estimated search path by invoking:<\/p>\n

\n
root@myhost:~# mysqld --verbose --help | head -n 20\r\n<\/strong>100927 19:53:06 [ERROR] Fatal error: Please read \"Security\" section of the manual to find out how to run mysqld as root!\r\n\r\nmysqld\u00a0 Ver 5.1.41 for unknown-linux-gnu on x86_64 (MySQL Community Server (GPL))\r\nCopyright (C) 2000-2008 MySQL AB, by Monty and others\r\nCopyright (C) 2008 Sun Microsystems, Inc.\r\nThis software comes with ABSOLUTELY NO WARRANTY. This is free software,\r\nand you are welcome to modify and redistribute it under the GPL license\r\n\r\nStarts the MySQL database server\r\n\r\nUsage: mysqld [OPTIONS]\r\n\r\nDefault options are read from the following files in the given order:\r\n\/etc\/my.cnf \/etc\/mysql\/my.cnf \/usr\/local\/mysql\/etc\/my.cnf ~\/.my.cnf<\/strong>\r\nThe following groups are read: mysqld server mysqld-5.1\r\nThe following options may be given as the first argument:\r\n...<\/pre>\n<\/blockquote>\n
Easy enough, right? Just walk through that search path and you’ve covered it all. Better yet, see which of these even exist!<\/p>\n
\n
root@myhost:~# ls -l \/etc\/my.cnf \/etc\/mysql\/my.cnf \/usr\/local\/mysql\/etc\/my.cnf ~\/.my.cnf<\/strong>\r\nls: cannot access \/etc\/my.cnf: No such file or directory\r\nls: cannot access \/etc\/mysql\/my.cnf: No such file or directory\r\nls: cannot access \/usr\/local\/mysql\/etc\/my.cnf: No such file or directory\r\n-rw-r--r-- 1 shlomi shlomi 32 2010-03-03 15:21 \/home\/shlomi\/.my.cnf<\/strong><\/pre>\n<\/blockquote>\n
Seems like we got it. The mysqld<\/strong> process only reads \/home\/shlomi\/.my.cnf<\/strong>. Right?<\/p>\n
Wrong!<\/h4>\n
There are two running instances of MySQL running on my machine. Neither of the primary my.cnf<\/strong> files used by these instances is listed above.<\/p>\n
\n
root@myhost:~# ps aux | grep mysqld\r\n<\/strong>shlomi\u00a0\u00a0 12092\u00a0 0.0\u00a0 0.0\u00a0\u00a0 4096\u00a0\u00a0 352 pts\/1\u00a0\u00a0\u00a0 S\u00a0\u00a0\u00a0 Sep26\u00a0\u00a0 0:00 \/bin\/sh \/home\/shlomi\/sandboxes\/5.1\/5.1.50\/bin\/mysqld_safe --defaults-file=\/home\/shlomi\/sandboxes\/msb_5_1_50\/my.sandbox.cnf<\/strong>\r\nshlomi\u00a0\u00a0 12167\u00a0 0.0 14.5 765520 587924 pts\/1\u00a0\u00a0 Sl\u00a0\u00a0 Sep26\u00a0\u00a0 1:12 \/home\/shlomi\/sandboxes\/5.1\/5.1.50\/bin\/mysqld --defaults-file=\/home\/shlomi\/sandboxes\/msb_5_1_50\/my.sandbox.cnf<\/strong> --basedir=\/home\/shlomi\/sandboxes\/5.1\/5.1.50 --datadir=\/home\/shlomi\/sandboxes\/msb_5_1_50\/data --log-error=\/home\/shlomi\/sandboxes\/msb_5_1_50\/data\/msandbox.err --pid-file=\/home\/shlomi\/sandboxes\/msb_5_1_50\/data\/mysql_sandbox5150.pid --socket=\/tmp\/mysql_sandbox5150.sock --port=5150\r\nroot\u00a0\u00a0\u00a0\u00a0 22827\u00a0 0.0\u00a0 0.0\u00a0\u00a0 4096\u00a0\u00a0 668 pts\/3\u00a0\u00a0\u00a0 S\u00a0\u00a0\u00a0 16:50\u00a0\u00a0 0:00 \/bin\/sh \/usr\/local\/mysql\/bin\/mysqld_safe --datadir=\/usr\/local\/mysql\/data --pid-file=\/usr\/local\/mysql\/data\/eeyore.pid\r\nmysql\u00a0\u00a0\u00a0 22960\u00a0 0.1\u00a0 2.2 274584 90188 pts\/3\u00a0\u00a0\u00a0 Sl\u00a0\u00a0 16:50\u00a0\u00a0 0:18 \/usr\/local\/mysql\/bin\/mysqld --defaults-extra-file=\/usr\/local\/mysql\/data\/my.cnf<\/strong> --basedir=\/usr\/local\/mysql --datadir=\/usr\/local\/mysql\/data --user=mysql --log-error=\/usr\/local\/mysql\/data\/eeyore.err --pid-file=\/usr\/local\/mysql\/data\/eeyore.pid --socket=\/tmp\/mysql.sock --port=3306<\/pre>\n<\/blockquote>\n
Can you see the mess above?<\/p>\n
The first two lines refer to a MySQL instance running under mysqlsandbox<\/a>. The mysqld_safe<\/em> script is passed the defaults-file parameter, and passes it on to the mysqld<\/em> service.<\/p>\n
However the next couple of lines refer to a MySQL server installed as a service; installed from a binary tarball, this instance reads configuration from the datadir<\/strong>. This time the mysqld_safe<\/em> instance is passed nothing, but invokes mysqld<\/em> with default-extra-file<\/strong>.<\/p>\n
To be fair, I wasn’t expecting the “mysqld –verbose –help”<\/strong> invocation to find the mysqlsandbox<\/em> configuration files. I did expect it to find the \/usr\/local\/mysql\/data\/my.cnf<\/strong> file which it eventually used.<\/p>\n
That’s nice & ugly. I can see the my.cnf<\/strong> file used by peeking at ps<\/em>. A bit overkill.<\/p>\n
Not quite there yet…<\/h4>\n
Because there’s still my private configuration file (resides on \/home\/shlomi\/.my.cnf<\/strong> on my account). Now I do not expect this file to be read by my standard MySQL server, since it does not run as user “shlomi”. However my command line clients do actually read this file, and so I am affected by its settings.<\/p>\n
I can verify whether such files have been used on a file system which is configured to support the atime<\/strong> option:<\/p>\n
\n
root@myhost:~# ls -lt --time=atime $(locate *my.cnf)\r\n<\/strong><\/pre>\n<\/blockquote>\n
I usually keep the atime<\/strong> option enabled for my “\/”<\/em> and “\/home”<\/em> partitions, but disable it on data partitions.<\/p>\n","protected":false},"excerpt":{"rendered":"
So you have a running MySQL server, it’s working well and everyone’s happy. You want to make a minor change to the configuration file, so you edit the file, restart MySQL – but the change doesn’t catch! Or maybe you want to check that some global variable has not been dynamically changed without an update […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,119],"class_list":["post-2981","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-linux"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-M5","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2981","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2981"}],"version-history":[{"count":18,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2981\/revisions"}],"predecessor-version":[{"id":3105,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2981\/revisions\/3105"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2981"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2981"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}