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.