How do I identify the MySQL my.cnf file?

If you are unfamiliar in administrating MySQL, the current MySQL configuration file generally found is named my.cnf (my.ini on windows). Where is that file.

If only that question was easy to answer!

Use of configuration files

MySQL will by default use at least one configuration file from the following defaults. MySQL also uses a cascade approach for configuration files. When you have multiple files in the appropriate paths you can see unexpected behavior when you override certain values in different files.

You can however for example specify –no-defaults to use no configuration file, or add options to your command line execution, so even looking at all configuration files is no guarantee of your operating configuration.

However for most environments, these complexities do not exist.

Default Location

By default and on single instance MySQL servers you are most likely to find this file called my.cnf and found at:

  • /etc/my.cnf
  • /etc/mysql/my.cnf

These are known as the global options files.

Alternative Locations

MySQL has both instance specific and user specific locations. For the inclusion of an instance specific file, the location is:

  • $MYSQL_HOME/my.cnf

where MYSQL_HOME is a defined environment variable. Historical MySQL versions also looked at [datadir]/my.cnf however I am unaware if this is applicable in 5.x versions.

You can also specific options on a per user basis for default inclusion. These are found at:

  • $HOME/.my.cnf

Distro specific locations

Ubuntu for example also provides an ability to add options via an include directory.

Specifying a configuration at runtime

While you may have these default files, you may elect to start mysql with a specific configuration file as specified by –defaults-file. This option will override all global/instance/user locations and use just this configuration file. You can also specify additional configuration that supplements and not overrides the default with –defaults-extra-file.

What files are on my system?

Again, assuming the default names you can perform a brute force check with:

$ sudo find / -name "*my*cnf"

This is actually worthwhile, especially if you find a /root/.my.cnf file which is default MySQL settings for the Operating System ‘root’ user.

MySQL recommendations

MySQL by default provides a number of recommended files however these are generally outdated especially for newer hardware. These files include my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf and my-innodb-heavy-4G.cnf. Don’t assume replacing your configuration with one of these files will make your system perform better.

MySQL made some attempt to correct these and at least some very poor defaults with MySQL 5.4 however I am unsure what’s in MySQL 5.5

MySQL Configuration at runtime

While several commands can help with identifying your configuration files and print defaults etc, it’s also possible to change your configuration at runtime. It’s possible that these changes are not reflected in your configuration files and pose an additional mismatch.

References

Tags: , ,

6 Responses to “How do I identify the MySQL my.cnf file?”

  1. Gerry says:

    Even with this approach there is no guarantees. The files could add ‘include’ that point to other files and Debian distributions have the ‘/etc/mysql/conf.d’ subdir as well.

    The best approach I found to know what the configuration options are in use is: ‘my_print_defaults mysqld’. This will print one line per entry in all configuration files. When there are multiple entries, the last one (or the command line option) prevails.

    For non-standard configurations a ‘ps -ef | grep mysqld’ will show which ‘–defaults-file’ is used for a given instance, and then add the same option to the ‘my_print_defaults’ command.

    My $.02
    G

  2. Gavin Towey says:

    mysqld –verbose –help | grep -C1 my.cnf

    is a pretty reliable way. It will give the possible file locations and order specific to your system.

  3. ronald says:

    Gerry, you are spot on with your suggestions.

  4. ronald says:

    Thanks Gavin, I didn’t realize one option in my example /usr/local/mysql/etc/my.cnf based on your command

    /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /opt/mysql51/my.cnf ~/.my.cnf

  5. Dim says:

    Thanks for the article!

    So what configuration do mysql uses if I didn’t create any my.cnf? because I never created any my.cnf files anywhere after I installed mysql on my linux box.

  6. Charline says:

    Bonjour

    j’ai install√© mysql 5.5 et le fichier my.cnf est sous /etc/my.cnf
    je l’ai mis dans le datadir mais il ne le prends pas en compte pourquoi?

    merci