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:
- I am setting the option xyz in my.cnf but it does not seem to work?
- 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?
- I have my.cnf in /etc and on in /usr/local/mysql/etc/ which one should I use?
- 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).
- The last option found is the “winner”
- Command line option is always the “winner” (i.e. the strongest)
- 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.
- In case you would like to add more files you can use the --defaults-extra-file option.
- 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
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
[mysql] user=haim prompt=\u>
*prompt = \u will print the user at the mysql prompt
And under /etc/my.cnf#./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>[mysql] user=root
When executing mysql –print-defaults you will get the following output
You can see that haim was used as the user.
Tip: you may use --defaults-file and –print-defaults together.
Watch out for pitfalls
- Windows use both my.ini and my.cnf
- Make sure you --print-defaults with the actual configuration of your running server
watch out for windows services and startup scripts
- Options files are used by mysqld but also by mysql client and other clients