Determine which MySQL configuration file is being used

Question

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

score 331 · Answer 1 · edited Dec 21 '17 at 20:34

331

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

edited Dec 21 '17 at 20:34

Cave Johnson

6,499
5
38
57

answered May 24 '10 at 11:49

Mark B

4,768
2
25
30

3

And I assume the last file listed wins? – mlissner Apr 19 '12 at 01:47
9

that's odd.. I checked each and everyone of those and they're all empty (on my mac).. any idea why? – abbood Dec 20 '13 at 07:17
11

This doesn't give the loaded configuration. rather possible custom configurations that might be loaded – mgPePe Mar 17 '14 at 15:22
5

I don't think this will list files specified by `!includedir`. I'm trying to figure out why the `!includedir` directive in my `/etc/mysql/my.cnf` file is not working. – Buttle Butkus Jun 26 '16 at 21:39
@ButtleButkus I had a similar issue and it was because there was an extra line after !includedir. Removing that line helped. – Ebsan Aug 03 '16 at 16:18
3

Why would you need to call `mysqld` with the full path?! – panepeter Feb 21 '18 at 09:40
This doesn't work on Windows. You need this answer instead: https://stackoverflow.com/a/17554146/491553 – Ryan Shillington Jan 03 '20 at 15:50
I just tried to vote up this answer again 2 years after the first time.. it's good to know that I still have the good habit of not settling for the accepted answer and keep reading a little more xD – rtribaldos Mar 23 '21 at 16:59

score 79 · Answer 2 · answered Feb 24 '09 at 05:57

79

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.

answered Feb 24 '09 at 05:57

staticsan

29,935
4
60
73

1

+1 because this is the mysql recommended way to do this. For windows one simple way is to dump the output to a file if you like to analyze the content. – kta Dec 13 '13 at 11:24
On windows, it told me a list of locations but not the right one, that instead i manage to find with mysql workbench (http://stackoverflow.com/a/17953456/1504300). – reallynice Feb 03 '15 at 08:23
3

Not sure it should matter, but you might want to use `mysqld` instead of `mysql` – nl-x Oct 05 '15 at 09:20
2

And still in Windows I experience that the my.ini is read from the ProgramData directory, and this does not show in the answer suggested here. – nl-x Oct 05 '15 at 09:42
4

Upvoted for `mysqld --print-defaults`, it gave me exactly what I wanted when I googled this question. – Per Lundberg Jun 20 '19 at 09:24

score 69 · Accepted Answer · answered Feb 24 '09 at 03:20

69

If you are on Linux, then start the 'mysqld' with strace, for eg strace ./mysqld.

Among all the other system calls, you will find something like:

stat64("/etc/my.cnf", 0xbfa3d7fc)       = -1 ENOENT (No such file or directory)
stat64("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=4227, ...}) = 0
open("/etc/mysql/my.cnf", O_RDONLY|O_LARGEFILE) = 3

So, as you can see..it lists the .cnf files, that it attempts to use and finally uses.

answered Feb 24 '09 at 03:20

14

And how to do that on a running system without messing up anything? – Milan Babuškov May 11 '10 at 18:08
5

@MilanBabuškov You should run `ps auxf` and find the command executed for MySQL. Typically it look like something such as `mysqld --basedir=/usr/local/mysql ...` (note the ... just means that there may be more flags in the command but I won't list all of it) Once you've found it, copy the whole thing and then shutdown MySQL. If you're on Linux it is `/etc/init.d/mysqld stop` or `mysqladmin -u root -p shutdown`. Then run `strace` with the mysql command you copied. So it would look like so: `strace mysqld --basedir=/usr/local/mysql` – Hengjie Nov 24 '12 at 18:08
I don't have the `open(...` line. Instead I have 4 lines that end with `= -1 Err#2`. How do you I know now which file is loaded? – mgPePe Mar 17 '14 at 15:36
5

fwiiw, on a Mac you can use dtruss instead of strace: `sudo dtruss mysqld 2>&1|grep my.cnf` @mgPePe, that output likely means that none of the files was found so none were opened. – vitaly Jun 05 '14 at 04:43

score 32 · Answer 4 · answered Jul 09 '13 at 17:06

32

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

answered Jul 09 '13 at 17:06

Alexandru Trandafir Catalin

2,588
2
31
39

This works for MySQL 5.7 as well. It's in `C:\ProgramData\MySQL\MySQL Server 5.7\my.ini` by default. – Ryan Shillington Jan 03 '20 at 15:50

score 23 · Answer 5 · edited Oct 20 '12 at 15:22

23

An alternative is to use

mysqladmin variables

edited Oct 20 '12 at 15:22

Ratan Sebastian

1,892
14
23

answered Jun 09 '12 at 12:29

Steve

239
2
2

4

It doesn't actually say where the cnf file is in my variables. I'm currently running MySQL 5.5.20. However, I can say that once upon a time this did work as I have seen it previously in the variables. Perhaps something changed since moving to 5.5. – Hengjie Nov 24 '12 at 18:02

score 15 · Answer 6 · edited Jan 16 '19 at 11:05

15

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?

edited Jan 16 '19 at 11:05

Wodin

3,243
1
26
55

answered Apr 16 '12 at 00:39

Sasha

415
6
5

score 11 · Answer 7 · answered Jan 12 '11 at 13:44

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"

score 6 · Answer 8 · answered Oct 04 '16 at 12:56

6

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

answered Oct 04 '16 at 12:56

fanThomas

127
2
6

score 3 · Answer 9 · edited Nov 10 '15 at 11:36

3

I found this really useful:

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

edited Nov 10 '15 at 11:36

Peter Mortensen

30,738
21
105
131

answered Sep 20 '15 at 20:52

Luke

22,826
31
110
193

1

This worked for me on my Server 2008 install, thanks. – delliottg Mar 21 '16 at 16:04

score 2 · Answer 10 · edited Nov 10 '15 at 11:35

2

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

edited Nov 10 '15 at 11:35

Peter Mortensen

30,738
21
105
131

answered Jan 14 '13 at 20:46

Heather McVay

939
3
15
31

score 2 · Answer 11 · answered Jul 26 '16 at 12:28

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.

score 2 · Answer 12 · answered Jun 22 '17 at 09:33

2

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

sudo dtruss mysqld 2>&1 | grep cnf

answered Jun 22 '17 at 09:33

talsibony

8,448
6
47
46

Kernel · Answer 13 · 2021-03-19T02:19:11.660

2

I installed mysql use brew install mysql

mysqld --verbose --help | less

And it shows:

edited Mar 19 '21 at 02:19

answered Jul 02 '20 at 13:23

Kernel

661
7
10

score 1 · Answer 14 · answered Aug 28 '17 at 08:00

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.

This is basically the equivalent of the top answer, for windows. thank you. — Aaron Wallentine, Nov 21 '19 at 18:51

score -1 · Answer 15 · answered Aug 13 '20 at 14:03

-1

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

answered Aug 13 '20 at 14:03

B.K

847
10
6

I do not even have that path...my.cnf.d And yes, it is MariaDB... – bodomalo Sep 22 '22 at 17:14

Determine which MySQL configuration file is being used

15 Answers15

Linked