[mysql] Determine which MySQL configuration file is being used

Is there a command to determine which configuration file MySQL 5.0 is currently using?

I installed mysql use brew install mysql

mysqld --verbose --help | less

And it shows:

Taken from the fantastic "High Performance MySQL" O'Reilly book:

$ which mysqld
/usr/sbin/mysqld

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

If you are on Windows, you can use Sysinternals procmon. Open it and configure filter setting like this, then click "Add". Now procmon will monitor mysqld.

Now start your mysql server as normal. Procmon will capture mysql's background operations. Search "my." in the procmon result pane and you will find something like the following:

It's clear that mysql searches a list of configuration files in turn. In my case it found C:\mysql-5.7.19-winx64\my.cnf successfully so it's using this one.

For people running windows server with mysql as a service, an easy way to find out what config file you are running is to open up the services control panel, find your mysql service (in my case 'MYSQL56'), right click and click properties. Then from here you can check the "Path to Executable" which should have a defaults-file switch which points to where your config file is.

Some servers have multiple MySQL versions installed and configured. Make sure you are dealing with the correct version running with a Unix command of:

ps -ax | grep mysql

I am on Windows and I have installed the most recent version of MySQL community 5.6

What I did to see what configuration file uses was to go to Administrative Tools > Services > MySQL56 > Right click > Properties and check the path to executable:

"C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

I found this really useful:

1. Find the MySQL process in Services under Control Panel -> Administration Tools
2. Right mouse click and choose Properties
3. Click and select the Path to executable and see if it contains the path to the my.ini/my.cfg

mysqld --help --verbose is dangerous. You can easily overwrite pidfile for running instance! use it with --pid-file=XYZ

Oh, and you can't really use it if you have more than 1 instance running. It will only show you default value.

Really good article about it:

How to find MySQL configuration file?

On CentOS and MariaDB you will find the MariaDB Server configuration settings in: /etc/my.cnf.d/server.cnf

If you run mysql --verbose --help | less it will tell you about line 11 which .cnf files it will look for.

You can also do mysql --print-defaults to show you how the configuration values it will use. This can also be useful in identifying just which config file it is loading.

Just did a quick test on ubuntu:

• installed mysql-server, which created /etc/mysql/my.cnf

• mysqld --verbose --help | grep -A 1 "Default options"

110112 13:35:26 [Note] Plugin 'FEDERATED' is disabled.
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

• created /etc/my.cnf and /usr/etc/my.cnf, each with a different port number

• restarted mysql - it was using the port number set in /usr/etc/my.cnf

Also meanwhile found the --defaults-file option to the mysqld. If you specify a config file there, only that one will be used, regardless of what is returned by /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Using MySQL Workbench it will be shown under "Server Status":

An alternative is to use

mysqladmin variables

Just in case you are running mac this can be also achieved by:

sudo dtruss mysqld 2>&1 | grep cnf