Skip to content

# Where’s my cnf file?

MySQL Configuration Linux December 7, 2010

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
1
Leave a Reply

1 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
1 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
kedar

I wish I could do:
SHOW GLOBAL VARIABLES LIKE ‘defaults-file’;

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.