MySQL: how to find out which configuration files are used?

Question

To reinstall my MySQL database , I deleted /etc/my.cnf

But what are the default settings for MySQL? And how can I see which configuration files are used?

I see mysql --help giving me a list of current settings. But my real problem is that I want to change local-infile and max_allowed_packet .

The help text also states that it is read 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 

Is it possible to create a new configuration file containing only these parameters? And most specifically: How can I see which configuration files are in use?

Here is the result 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

As far as I know, there is no way to see which MySQL configuration file to read when it starts. If it does not exist, it simply uses the default settings with which it was compiled. Note. read , being an operational word, MySQL does not save the configuration file longer than is required for its analysis.

If you are curious to check the file search order, you can simply start mysql through strace and note the access() system calls. This will show you the order that it was compiled to keep track of the search for its configuration and a lot of things that you really did not want to know.

Answer 2

Try using my.cnf or load it with a command line switch:

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

It is important that the parameters are in the appropriate sections.

As I understand it, MySQL reads the files in the sequence that you wrote, and then uses the default values ​​that were set during compilation.

Answer 3

The specified configuration files are read only if they exist. Have you tried reading the /etc/my.cnf file or is one of the others not in your home directory with root privileges? It surprises me that none of them exist.

In any case, you can create one of the files, for example, ~ / .my.cnf, and include the code that culebrón just published.