Determine which MySQL configuration file is being used

E

16

176

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

Engram answered 24/2, 2009 at 3:12 Comment(0)

V

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.

Valladares answered 24/2, 2009 at 3:20 Comment(4)

And how to do that on a running system without messing up anything? – Olympia 11/5, 2010 at 18:8

@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 – Friesen 24/11, 2012 at 18:8

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? – Sinegold 17/3, 2014 at 15:36

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. – Elate 5/6, 2014 at 4:43

P

342

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

Petree answered 24/5, 2010 at 11:49 Comment(8)

And I assume the last file listed wins? – Disfigurement 19/4, 2012 at 1:47

that's odd.. I checked each and everyone of those and they're all empty (on my mac).. any idea why? – Declarative 20/12, 2013 at 7:17

This doesn't give the loaded configuration. rather possible custom configurations that might be loaded – Sinegold 17/3, 2014 at 15:22

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. – Haupt 26/6, 2016 at 21:39

@ButtleButkus I had a similar issue and it was because there was an extra line after !includedir. Removing that line helped. – Certiorari 3/8, 2016 at 16:18

Why would you need to call mysqld with the full path?! – Jilt 21/2, 2018 at 9:40

This doesn't work on Windows. You need this answer instead: https://mcmap.net/q/28684/-determine-which-mysql-configuration-file-is-being-used – Cubby 3/1, 2020 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 – Goaltender 23/3, 2021 at 16:59

I

83

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 for the client session in configuration file [client]. This can also be useful in identifying just which config file it is loading.

For server configuration session [mysqld] you can use:

mysqld --print-defaults

Inflow answered 24/2, 2009 at 5:57 Comment(5)

+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. – Cockup 13/12, 2013 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 (https://mcmap.net/q/28705/-mysql-39-s-lower_case_table_names-won-39-t-change). – Reverential 3/2, 2015 at 8:23

Not sure it should matter, but you might want to use mysqld instead of mysql – Pasty 5/10, 2015 at 9:20

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. – Pasty 5/10, 2015 at 9:42

Upvoted for mysqld --print-defaults, it gave me exactly what I wanted when I googled this question. – Arterialize 20/6, 2019 at 9:24

V

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.

Valladares answered 24/2, 2009 at 3:20 Comment(4)

And how to do that on a running system without messing up anything? – Olympia 11/5, 2010 at 18:8

@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 – Friesen 24/11, 2012 at 18:8

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? – Sinegold 17/3, 2014 at 15:36

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. – Elate 5/6, 2014 at 4:43

S

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

Stew answered 9/7, 2013 at 17:6 Comment(1)

This works for MySQL 5.7 as well. It's in C:\ProgramData\MySQL\MySQL Server 5.7\my.ini by default. – Cubby 3/1, 2020 at 15:50

R

23

An alternative is to use

mysqladmin variables

Reinke answered 9/6, 2012 at 12:29 Comment(1)

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. – Friesen 24/11, 2012 at 18:2

G

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?

Gangling answered 16/4, 2012 at 0:39 Comment(0)

M

11

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"

Mashie answered 12/1, 2011 at 13:44 Comment(0)

S

6

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

Stinko answered 4/10, 2016 at 12:56 Comment(0)

V

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

Virgiliovirgin answered 20/9, 2015 at 20:52 Comment(1)

This worked for me on my Server 2008 install, thanks. – Nestornestorian 21/3, 2016 at 16:4

E

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

Electrocautery answered 14/1, 2013 at 20:46 Comment(0)

B

2

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.

Bitty answered 26/7, 2016 at 12:28 Comment(0)

C

2

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

sudo dtruss mysqld 2>&1 | grep cnf

Commonalty answered 22/6, 2017 at 9:33 Comment(0)

A

2

I installed mysql use brew install mysql

mysqld --verbose --help | less

And it shows:

Alvardo answered 2/7, 2020 at 13:23 Comment(0)

E

1

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.

Earvin answered 28/8, 2017 at 8:0 Comment(1)

This is basically the equivalent of the top answer, for windows. thank you. – Schulman 21/11, 2019 at 18:51

D

0

Make sure you're getting the correct files, especially if you have multiple versions of MySQL installed, for example, using tools like DBNgin.

This is what I did to find the correct file on Max (should work with Linux too):

Run ps ax|grep mysqld to list the running daemons:

36245   ??  S     18:46.08 /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld --socket=/tmp/mysql_3306.sock

1: Check if --no-defaults, --defaults-file or --defaults-extra-file exists there.

2: Check the default locations:

/Users/Shared/DBngin/mysql/8.0.33/bin/mysqld -v --help|more

3: Also make sure that defaults are not overridden

/Users/Shared/DBngin/mysql/8.0.33/bin/mysqld --print-defaults

Recommended topics

Hot tags