MySQL: how do I see which config files are used?

Question

In order to reinstall my MySQL database, I deleted /etc/my.cnf

But what are the default settings of MySQL? And how do I see which configuration files are in use?

I can see that mysql --help gives me a list of the current settings. But my real problem here is that I want to modify local-infile and max_allowed_packet.

The help text also states that it reads from the following files: /etc/my.cnf /etc/mysql/my.cnf /opt/local/etc/mysql5/my.cnf ~/.my.cnf. But none of them exist:

> cat /etc/my.cnf /etc/mysql/my.cnf /opt/local/etc/mysql5/my.cnf ~/.my.cnf  
cat: /etc/my.cnf: No such file or directory
cat: /etc/mysql/my.cnf: No such file or directory
cat: /opt/local/etc/mysql5/my.cnf: No such file or directory
cat: /Users/jesper/.my.cnf: No such file or directory

Can I create a new configuration file that only contains these settings? And most specific: How can I see which configuration files that are in use?

Here is the output from mysql --help

> mysql --help
mysql  Ver 14.14 Distrib 5.1.40, for apple-darwin10.0.0 (i386) using readline 6.0
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.

[...]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/local/etc/mysql5/my.cnf ~/.my.cnf 
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults    Print the program argument list and exit
--no-defaults    Don't read default options from any options file
--defaults-file=#   Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
auto-rehash                       TRUE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
database                          (No default value)
default-character-set             latin1
delimiter                         ;
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
ignore-spaces                     FALSE
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              0
prompt                            mysql> 
quick                             FALSE
raw                               FALSE
reconnect                         TRUE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
table                             FALSE
user                              (No default value)
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect_timeout                   0
max_allowed_packet                16777216
net_buffer_length                 16384
select_limit                      1000
max_join_size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE

Answer 1

Try this my.cnf or load it with a command-line parameter:

[mysqld]
max_allowed_packet  = 16M
[client]
local-infile=1

It's important that parameters were in proper sections.

As I understand, MySQL reads the files in the sequence you've written and then uses the defaults that were set at compilation.

Answer 2

The listed configuration files are only read if they exist, obviously. Have you tried reading /etc/my.cnf or one of the others that are not in your home directory as root? It surprises me that none of them are there.

Anyway, you can create one of the files, e.g., ~/.my.cnf, and include the code that culebrón has just posted.

Answer 3

As far as I know, there is no way to see what configuration file MySQL read when it started. If none exist, it simply uses the defaults it was compiled with. Note, read being the operative word, MySQL does not keep the configuration file open any longer than it takes to parse it.

If you are morbidly curious to verify the order of the files it searches for, you could simply run mysql through strace and note the access() system calls. This will show you the order it was compiled to follow when searching for its configuration, and a whole lot of stuff that you really didn't want to know.