Where’s my cnf file?

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

Wait, which my.cnf file does MySQL read? Rather, which my.cnf files?

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

Let’s find out which configuration files apply.

No direct information

It would all be easier if we could just SHOW GLOBAL VARIABLES LIKE ‘configuration_files_that_this_server_has_read_list’. There isn’t such an option.

The MySQL documentation explains about the configuration files search path, and that’s one path you can follow. Also, you can detect another estimated search path by invoking:

root@myhost:~# mysqld --verbose --help | head -n 20
100927 19:53:06 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

mysqld  Ver 5.1.41 for unknown-linux-gnu on x86_64 (MySQL Community Server (GPL))
Copyright (C) 2000-2008 MySQL AB, by Monty and others
Copyright (C) 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Starts the MySQL database server

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-5.1
The following options may be given as the first argument:
...

Easy enough, right? Just walk through that search path and you’ve covered it all. Better yet, see which of these even exist!

root@myhost:~# ls -l /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
ls: cannot access /etc/my.cnf: No such file or directory
ls: cannot access /etc/mysql/my.cnf: No such file or directory
ls: cannot access /usr/local/mysql/etc/my.cnf: No such file or directory
-rw-r--r-- 1 shlomi shlomi 32 2010-03-03 15:21 /home/shlomi/.my.cnf

Seems like we got it. The mysqld process only reads /home/shlomi/.my.cnf. Right?

Wrong!

There are two running instances of MySQL running on my machine. Neither of the primary my.cnf files used by these instances is listed above.

root@myhost:~# ps aux | grep mysqld
shlomi   12092  0.0  0.0   4096   352 pts/1    S    Sep26   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
shlomi   12167  0.0 14.5 765520 587924 pts/1   Sl   Sep26   1:12 /home/shlomi/sandboxes/5.1/5.1.50/bin/mysqld --defaults-file=/home/shlomi/sandboxes/msb_5_1_50/my.sandbox.cnf --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
root     22827  0.0  0.0   4096   668 pts/3    S    16:50   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/eeyore.pid
mysql    22960  0.1  2.2 274584 90188 pts/3    Sl   16:50   0:18 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --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

Can you see the mess above?

The first two lines refer to a MySQL instance running under mysqlsandbox. The mysqld_safe script is passed the defaults-file parameter, and passes it on to the mysqld service.

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. This time the mysqld_safe instance is passed nothing, but invokes mysqld with default-extra-file.

To be fair, I wasn’t expecting the “mysqld –verbose –help” invocation to find the mysqlsandbox configuration files. I did expect it to find the /usr/local/mysql/data/my.cnf file which it eventually used.

That’s nice & ugly. I can see the my.cnf file used by peeking at ps. A bit overkill.

Not quite there yet…

Because there’s still my private configuration file (resides on /home/shlomi/.my.cnf 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.

I can verify whether such files have been used on a file system which is configured to support the atime option:

root@myhost:~# ls -lt --time=atime $(locate *my.cnf)

I usually keep the atime option enabled for my “/” and “/home” partitions, but disable it on data partitions.