Where is my my.cnf file?

One of the most common questions I get is related to the location of the my.cnf file (or my.ini).

The question comes in few variants:

  1.  I am setting the option xyz in my.cnf but it does not seem to work?
  2.  I can set the option xyz using. /mysql --zyx=abc but when I am placing it in my.cnf it does not take any effect?
  3.  I have my.cnf in /etc and on in /usr/local/mysql/etc/  which one should I use?
  4.  Where the f@&% is my my.cnf file !?!?

In MySQL the answer for the above questions are a little confusing.

The reason for that is that MySQL server use more than one my.cnf file. Because MySQL server uses more than one my.cnf it needs precedence. You should remember the following rules regarding configuration files (a.k.a. options files).  

  1.  The last option found is the “winner”
  2.   Command line option is always the “winner” (i.e. the strongest)
  3.   Windows use both my.ini and my.cnf (in that order).


The user manual specifies the precedence of options file and their location for Windows and Linux MySQL installations here. You can consult your mysql help to figure out the precedence of configuration files and the order of read.
Execute the MySQL server (or any other mysql utility) using the --verbose --help option   


c:\>mysqld-nt.exe --verbose --help
Ver 5.0.87-community-nt-log for Win32 on ia32 (MySQL Community Edition (GPL))
Copyright (C) 2000 MySQL AB, by Monty and others
Starts the MySQL database serverUsage: mysqld-nt.exe 
[OPTIONS]NT and Win32 specific options:
 --install                    Install the default
--install-manual              Install the default service started
Default options are read from the following files in thegiven order:
C:\WINNT\my.ini C:\WINNT\my.cnf C:\my.ini C:\my.cnf 
C:\Program Files\MySQL\MySQL Server 5.0\my.ini 
C:\Program Files\MySQL\MySQL Server 5.0\my.cnf


But in some cases this will not reflect the actual files used! Why?
Because the server maybe started with one of the options --defaults-extra-file or
 --defaults-file or maybe someone added options directly to the mysqld startup script.

  1. In case you would like to add more files you can use the --defaults-extra-file option.  
  2. In case you would like to point only to a specific file you can use the --defaults-file option. 


For example you can see in the image below that when installing MySQL as a service in windows the installation uses the --defaults-file option   

Windows Service <

In my Linux I used “ps aux | grep mysqld” to see how the mysqld is executed from the startup script and got the following output


mysql    21250 0.1 2.9 536588 59840 pts/0    Sl   14:21   0:00
/opt/mysql/bin/mysqld --basedir=/opt/mysql/ --datadir=/opt/mysql//data
--user=mysql --log-error=/opt/mysql//data/error.log --pid-file=/opt/mysql//data/haimr.com.pid
--socket=/tmp/mysql.sock --port=3306


You may see that the port option is served directly at the service script so you should change the script if you want to change to port.


But wait that’s not all; in order to see witch variables are overridden by the files above

MySQL offers the --print-defaults option witch list out all the options in the order they read for example if you had a my.cnf under your user directory (a.k.a~/my.cnf ) in Linux with the following entry


*prompt = \u will print the user at the mysql prompt

And under /etc/my.cnf


When executing mysql –print-defaults you will get the following output

#./mysql --print-defaults ./mysql would have been started with the following arguments: --user=root --port=3306 --socket=/tmp/mysql.sock --no-auto-rehash --user= haim --prompt=\u> ./mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.1.31-log MySQL Community Server (GPL)‏ Type 'help;' or '\h' for help. Type '\c' to clear the buffer. haim>


You can see that haim was used as the user.

Tip: you may use --defaults-file and –print-defaults together.

Watch out for pitfalls

  1.  Windows use both my.ini and my.cnf
  2.  Make sure you --print-defaults with the actual configuration of your running server
    watch out for windows services and startup scripts
  3. Options files are used by mysqld but also by mysql client and other clients