Location of my.cnf file on macOS

Question

I'm trying to follow along this tutorial to enable remote access to MySQL. The problem is, where should my.cnf file be located? I'm using Mac OS X Lion.

Sommelier

I think this belongs to serverfault.com. But still, welcome to SO! – Cyanite

Answer 1

This thread on the MySQL forum says:

By default, the OS X installation does not use a my.cnf, and MySQL just uses the default values. To set up your own my.cnf, you could just create a file straight in /etc.

OS X provides example configuration files at /usr/local/mysql/support-files/.

And if you can't find them there, MySQLWorkbench can create them for you by:

1. Opening a connection.
2. In the left column select "Administration" tab and then the "Options File" under "INSTANCE" in the menu.
3. MySQL Workbench will search for my.cnf and if it can't find it, it'll create it for you.

Gossoon

Thanks!! So just in /usr/etc? Or should I make some kind of mysql directory there? :) edit Found the answer to that on the link, thanks! – Sommelier

At least the current MySQL package for Mac OS X (mysql-5.6.17-osx10.7-x86_64 at the time of this writing) does in fact create and use a my.cnf. It is located at /usr/local/mysql-5.6.17-osx10.7-x86_64/my.cnf – Empiricism

you may want to ensure that mysql is actually loading in whichever my.cnf file you're editing via mysql --verbose --help | grep my.cnf – Misdeem

create /Applications/MAMP/Library/my.cnf for OSX El Capitan – Camiecamila

On Mac OS Sierra, it wasn't set up already. I had to copy /usr/local/mysql/support-files/my-default.cnf to my.cnf in the same dir. Note that the mysql is symlinked to the package, in my case mysql-5.7.17-macos10.12-x86_64. – Percolate

Just did a clean install of MySQL 5.7.19 on Mac OS 10.12 using the .dmg from dev.mysql.com. There's no my.cnf in any of the places that mysql --help says it looks in. And there's no my-default.cnf in /usr/local/mysql/support-files/ or anywhere else I've found. Turns out that "as of MySQL 5.7.18, my-default.cnf is no longer included in or installed by distribution packages". – Carmelitacarmelite

In the same situation as Chris, Mac 10.12.6, native MySQL 5.7.19, I was able to get MySQL Workbench to generate a default configuration file at /etc/my.cnf with a default mysqld section, but I don't think it is taking effect. The file was still empty, so I used that program to show the diff, copied it into a text editor to save with admin permissions, restarted, but still nothing. I wanted to enable named pipes, the socket, and disable the network communication, but doesn't seem to change anything, or it enabled the socket, but did not disable the normal networking connection path. – Innes

Where is "INSTANCE" in the menu? Which menu? My screenshot : imgur.com/a/KyiIwGM – Ostensory

think different – Comical

This answer is old and depends on which installer you used to install mysql. Homebrew puts it one place, the dmg installer puts it another – Nicolenicolea

As on date, in step 3, the Workbench just says "Location of conf file not specified" and it doesn't generate a new one automatically. – Americana

Solution: https://mcmap.net/q/116938/-location-of-my-cnf-file-on-macos – Cundiff

Answer 2

In case of Mac OS X Maverick when MySQL is installed via Homebrew it's located at /usr/local/opt/mysql/my.cnf

Flagelliform

which is /usr/local/Cellar/yourMySqlVersion/my.cnf – Hygrometry

/usr/local/opt/mariadb/VERSIONNUMBER/ in my case MariaDB is installed – Vermiculate

Mine is in /usr/local/etc/my.cnf – Quadrinomial

Mine was /usr/local/mysql/etc. I'm on High Sierra but it was installed a few versions back. – Gallon

I checked there, and haven't found it. I finally found that it is in a hidden folder there: .bottle/etc/my.cnf I copied the my.cnf out and modified it. After restart mysql, it worked. – Conifer

Answer 3

In general, on Unix and Unix-like systems, MySQL/MariaDB programs read config/startup files in the following locations (in the specified order):

• /etc/my.cnf - Global
• /etc/mysql/my.cnf - Global

• SYSCONFDIR/my.cnf - Global

  SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.

• $MYSQL_HOME/my.cnf - Server-specific (server only)

  MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe sets it to BASEDIR, the MySQL base installation directory.

• file specified with --defaults-extra-file=path if any

• ~/.my.cnf - User-specific
• ~/.mylogin.cnf - User-specific (clients only)

^{Source: Using Option Files.}

Note: On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.

---

Additionally on Mac there is a simple way to check it.

1. Run: sudo fs_usage | grep my.cnf

   ^{This will report any filesystem activity in real-time related to that file.}

2. In another Terminal, restart your MySQL/MariaDB, e.g.

   brew services restart mysql
   

   or:

   brew services restart mariadb
   

3. On terminal with fs_usage, the proper location should be shown, e.g.

   15:52:22  access            /usr/local/Cellar/mariadb/10.1.14/my.cnf                                         0.000002   sh          
   

   So if the file doesn't exist, create one.

Dispeople

Thanks! the sudo fs_usage | grep my.cnf method is quite efficient. I find this file on folder: /usr/local/etc/my.cnf – Keefe

Great method, however I get several results there: private/etc/my.cnf, /usr/local/etc/my.cnf, /usr/local/Cellar/mysql/<myVersion>/my.cnf, ~.my.cnfwith my_print_def in the right-most column. Then after a while it's only /usr/local/etc/my.cnf with my terminal in the right-most column. But all of these my.cnf files don't exist! – Preconceive

@Preconceive If none of them exists, create a new one. – Dispeople

@Dispeople Ok. At any of the listed locations? And what does actually happen there if the my.cnf files don't exist? Is it showing that it searches for my.cnf files there? What would be the order in which they are read? Or is just used? If so, which one? – Preconceive

@Preconceive In any location that is read. If config doesn't exist, MySQL uses the default settings. – Dispeople

Your last set of instructions re sudo fs_usage | grep my.cnf was the only thing that helped me on this page, as for some unknown reason mysqld was not loading from the default locations listed via mysql -? – Mascagni

when I execute fs_usage in my Mac terminal, it spilled out 'fs_usage' must be run as root... Do you have any idea what is going on and what to do with it? I did some search and executed this: % sudo fs_usage Password: ktrace_start: Resource busy – Adrieneadrienne

"On Unix platforms, MySQL ignores configuration files that are world-writable" helped solve my issue, thanks! I changed the permissions and it started reading the config file. I would suggest there's a flaw in the mysqld logic that it keeps running by default. If I were mysqld and I saw a config file in the "top-most" location (/etc/my.cnf), and I'm unwilling to read it due to permissions, and I haven't been provided an option that explicitly tells me "sure, just skip right on past config files until you can read one", I would error out and stop running. But I guess I'm not mysqld...yet. – Scatterbrain

Answer 4

If you are using macOS Sierra and the file doesn't exists, run

mysql --help or mysql --help | grep my.cnf

to see the possible locations and loading/reading sequence of my.cnf for mysql then create my.cnf file in one of the suggested directories then add the following line

[mysqld] sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

You can sudo touch /{preferred-path}/my.cnf then edit the file to add sql mode by

sudo nano /{preferred-path}/my.cnf

Then restart MySQL.

Dank

Answer 5

I don't know which version of MySQL you're using, but here are possible locations of the my.cnf file for version 5.5 (taken from here) on Mac OS X:

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. SYSCONFDIR/my.cnf
4. $MYSQL_HOME/my.cnf
5. defaults-extra-file (the file specified with --defaults-extra-file=path, if any)
6. ~/.my.cnf

Colonel

Answer 6

For MySQL 5.7 on Mac OS X El Capitan: /usr/local/mysql/etc/my.cnf

Copy default conf from /usr/local/mysql/support-files/my-default.cnf

Malevolent

Just to be clear, you have to create "etc/" folder yourself and you need root privileges for that "sudo su -" – Toothed

Not found in that location for me . I am using MAMP – Camiecamila

@mirzavu, see this link: #679145 – Malevolent

Answer 7

I'm running MacOS Catalina(10.15.3) and find my.cnf in /usr/local/etc.

Dropper

Answer 8

macOS sierra 10.12.6
MySQL version : 5.7.18_1
I run locate my.cnf and the path is

/usr/local/etc/my.cnf

Attar

consider adding explanation to your asnwer – Blinking

@charles.cc.hsu: try using command locate my.cnf on your terminal. what the result ? – Attar

Answer 9

The current MySQL package for Mac OS X Mavericks (mysql-5.6.17-osx10.7-x86_64 at the time of this writing) automatically creates a my.cnf during installation.

It is located at /usr/local/mysql-5.6.17-osx10.7-x86_64/my.cnf
Adapt your path according to your version.

Empiricism

Answer 10

So none of these things worked for me. I am using the current dmg install of mysql community server. ps shows that all of the most critical parameters normally in my.cnf are passed on the command line, and I couldn't figure out where that was coming from. After doing a full text search of my box I found it in:

/Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

So you can either change them there, or take them out so it will actually respect the ones you have in your my.cnf wherever you decided to put it.

Enjoy!

Example of the file info found in that file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Label</key>             <string>com.oracle.oss.mysql.mysqld</string>
    <key>ProcessType</key>       <string>Interactive</string>
    <key>Disabled</key>          <false/>
    <key>RunAtLoad</key>         <true/>
    <key>KeepAlive</key>         <true/>
    <key>SessionCreate</key>     <true/>
    <key>LaunchOnlyOnce</key>    <false/>
    <key>UserName</key>          <string>_mysql</string>
    <key>GroupName</key>         <string>_mysql</string>
    <key>ExitTimeOut</key>       <integer>600</integer>
    <key>Program</key>           <string>/usr/local/mysql/bin/mysqld</string>
    <key>ProgramArguments</key>
        <array>
            <string>/usr/local/mysql/bin/mysqld</string>
            <string>--user=_mysql</string>
            <string>--basedir=/usr/local/mysql</string>
            <string>--datadir=/usr/local/mysql/data</string>
            <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
            <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
            <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
             <string>--keyring-file-data=/usr/local/mysql/keyring/keyring</string>
             <string>--early-plugin-load=keyring_file=keyring_file.so</string>

        </array>
    <key>WorkingDirectory</key>  <string>/usr/local/mysql</string>
</dict>
</plist>

Riella

great! you saved my day. – Mobocracy

I had the same case, but I also had to add my.cnf file to /etc/my.cnf – Inhaul

Answer 11

You can create your file under any directory you want. After creation, you can "tell" the path to mysql config:

Plasmo

Answer 12

You can open a terminal and type locate my.cnf

Accommodation

Sorry to not be specific enough, I apparently need to copy 'my-large.cnf' to some folder and rename it 'my.cnf'. But I don't know to where.. – Sommelier

also, you do need to have a populated locate db. on mac osx: sudo launchctl load -w /System/Library/LaunchDaemons/com.apple.locate.plist – Holder

Answer 13

In mysql 5.6.22, which I installed it from Homebrew, the path of my.cnf is

/usr/local/opt/mysql/my.cnf 

Vagary

Answer 14

Open Terminal and use below command:

sudo find / -name my.cnf

Macdougall

Answer 15

In case of installing MySQL with Homebrew in Mac M1 with MacOS Monterey 12.0.1 the location is /opt/homebrew/etc/my.cnf

Ressler

Answer 16

You can check the file

/usr/local/bin/mysql.server and see from where my.conf is being read from.

Usually it is from /etc/my.cnf or ~/my.cnf or ~/.my.cnf

Kendall

Correction: ~/.my.cnf. Add a dot before the file name. – Unmixed

Answer 17

READ THE DOCS!

The MySQL Documents for which version you are using will help. It is usually described as a Options File or MySQL Config File.
The docs have the location of these files in the documentation, As well as other VITAL information like the location & small examples of what the config file must look like.

MySQL Version 8

MySQL Version 5.7

MySQL Version 5.6

MySQL Version 5.5

MySQL Version 5.6 Japanese

Important Notes:

On Unix platforms, MySQL ignores configuration files that are world-writable.

This is intentional as a security measure.

In other words, if you have the wrong permissions set on your config file the will NOT load.

Example of initial setup permission of one of the config files:

RIZZOMBP$ ls -lah /etc/my.cnf
-rw-r--r--  1 myusername  wheel     0B Feb 25 20:40 /etc/my.cnf

Note:

It is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files.....

...MySQL makes no guarantee about the order in which option files in the directory will be read...

Any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

Examples of how to find your location of your config or log files ect.:

SQL

This will not show you the config file but will help you locate your installation files/folders.

MySQL Version 5.7 & 8+

SELECT * FROM PERFORMANCE_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_VALUE LIKE '%\/%';

MySQL Version <= 5.6

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_VALUE LIKE '%\/%';

SHELL/Terminal

RIZZOMBP$ mysql --help | grep "Default options" -A 1

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

Above Command Credit To: Erwin Mayer from ServerFault

RIZZOMBP$ mysqld --verbose --help | grep '/my.cnf' -B 1

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

OR

RIZZOMBP$ ps aux | grep -i mysqld | grep -v $USER

_mysql 106 0.4 2.5 4232336 422112 ?? Ss 7:01PM 0:57.12
/usr/local/mysql/bin/mysqld
--user=_mysql
--basedir=/usr/local/mysql
--datadir=/usr/local/mysql/data
--plugin-dir=/usr/local/mysql/lib/plugin
--log-error=/usr/local/mysql/data/mysqld.local.err
--pid-file=/usr/local/mysql/data/mysqld.local.pid
--keyring-file-data=/usr/local/mysql/keyring/keyring
--early-plugin-load=keyring_file=keyring_file.so

Sample File

Should you need a detailed Reference Sample my.cnf:

1. https://gist.github.com/search?utf8=%E2%9C%93&q=my.cnf+mysql&ref=searchresults
2. https://www.linode.com/community/questions/5749/mysql-sample-config-files
3. https://www.fromdual.com/mysql-configuration-file-sample

No affiliation/association to the URL's

My System:

RIZZOMBP$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109

RIZZOMBP$ mysql -V
mysql  Ver 8.0.15 for macos10.14 on x86_64 (MySQL Community Server - GPL)

RIZZOMBP$ mysqld -V
/usr/local/mysql-8.0.15-macos10.14-x86_64/bin/mysqld 
Ver 8.0.15 for macos10.14 on x86_64 (MySQL Community Server - GPL)

Volunteer

mysql --help | grep "Default options" -A 1 . this helped. – Boucicault

Answer 18

macOS 13 and MySQL 8 current if installed directly from the MySQL Installer from Oracle:

By default, no my.cnf will be created. To get a default one created easily without needing to use any Terminal commands, here is how:

1. Open MySQL Workbench on your Mac. Connect into your localhost instance of MySQL.

2. Click the little wrench icon next to INSTANCE on the left pane above Startup / Shutdown.

3. Select macOS as System Type, macOS as Installation Type, and the rest will default into place. Copy the location it gives you for the my.cnf file.

4. Click Close

5. Now click Options File on the left. It will notice that you don't have a file and will show you default values.

6. Click the Apply... in the bottom right corner.

7. It will then ask you for a password of your current logged in user of your Mac to create and save the my.cnf file.

8. Now in the Finder use the keyboard combo of Command-Shift-G and copy in your location we copied earlier (i.e. /etc/my.cnf)

Shirelyshirey

Answer 19

For MAMP 3.5 Mac El Capitan, create a separate empty config file and write your additional settings for mysql

sudo vim /Applications/MAMP/Library/my.cnf

And Add like this

[mysqld]
max_allowed_packet = 256M

Camiecamila

Answer 20

macOS High Sierra version 10.13.6

mysql Ver 14.14 Distrib 5.7.22, for osx10.13 (x86_64) using EditLine wrapper Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved

Default options are read from the following files in the given order:

/etc/my.cnf 
/etc/mysql/my.cnf 
/usr/local/etc/my.cnf 
~/.my.cnf

Maag

Answer 21

it should be usually under /usr/local/etc on mac if you do not find it you can create one

Taurus

Answer 22

/private/etc/my.cnf for Catalina

Robbyrobbyn

Answer 23

I checked in macOS Sierra, the homebrew installed MySql 5.7.12

The support files are located at

/usr/local/opt/mysql/support-files

Just copy my-default.cnf as /etc/my.cnf or /etc/mysql/my.cnf and the configuration will be picked up on restart.

Isologous

I just installed 5.7.18 on 12.12.4 via homebrew and they are not there. – Jiggle

Answer 24

a quick way to figure out on MAC OS was the following:

sudo find /usr/ -type f -iname "*my.cnf*"

or

brew list [email protected] |grep -i my.cnf

Greetings

Ostend

Answer 25

I am using mysql version 5.7.17 in macOS High Sierra version 10.13.3 and I have found the mysql config file here.

cd /usr/local/mysql/support-files/my-default.cnf

Quass

Thanks for the tip on finding the default configuration file. Note, this is just a template file so you'll want to copy this to a location found in mysql --verbose --help | grep my.cnf where it will get picked up, as per: DO NOT EDIT THIS FILE. It's a template which will be copied to the default location during install, and will be replaced if you upgrade to a newer version of MySQL. – Gallon

Answer 26

For mysql 8.0.19, I finally found the my.cnf here: /usr/local/opt/mysql/.bottle/etc I copied it to /usr/local/opt/mysql/ and modified it. Then I restart the mysql service, it works.

Conifer

Works for me on BigSur with 8.0 installed via homebrew, thanks – Jamilla

Answer 27

For Mac , what worked for me is creating a .my.cnf file in my ~ path. Hope this helps.

Ocean

Answer 28

Copy /usr/local/opt/mysql/support-files/my-default.cnf as /etc/my.cnf or /etc/mysql/my.cnf and then restart mysql.

Harmonicon

Answer 29

For me in sierra version

copy the default configuration at:

/usr/local/Cellar/mysql/5.6.27/support-files/my-default.cnf

to

/usr/local/Cellar/mysql/5.6.27/my.cnf

Reinwald

Answer 30

In my case, the file did not exist. In MySQL Workbench I went to OPTIONS FILE and found some default values. I clicked apply. It asked for permission. It then created the my.cnf file under /etc. However, it is very important to keep in mind that the first time you click "apply", you do not make any changes to the default configuration. Once the file has been created, you can make changes which will be applied when you click "apply". Otherwise you will not be shown the apply button when you make changes.

Moxley

I have an options file at /etc/mysql/my.cnf but MySQL Workbench is not aware of it. Instead, it complains that the configuration file with an empty path cannot be found and then offers to create a new file. – Epicritic

Answer 31

After the 5.7.18 version of MySQL, it does not provide the default configuration file in support-files directory. So you can create my.cnf file manually in the location where MySQL will read, like /etc/mysql/my.cnf, and add the configuration you want to add in the file.

Bennett

Answer 32

rDefault options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

Dentation

Answer 33

I'm running MacOS Mojave (10.14.6) and to get MySQL to recognize my config file, I had to place it in /usr/local/mysql-5.7.26-macos10.14-x86_64/etc/my.cnf. Also I have a symbolic link pointing to it from /usr/local/@mysql/etc/my.cnf .

I was trying to turn off sql_mode=only_full_group_by and setting that option in the config file was the only way I could get the setting to persist across sessions. The contents of the config file are:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

I'm using the native install of MySQL, not the Homebrew set up.

Hyder

Answer 34

Alternatively to all these great answers. If you are working in an environment where you don't get sudo access. Your Homebrew could have been installed in your home directory ~ if that's the case and your mysql has been installed by brew install mysql your my.cnf could be located in ~/homebrew/etc/my.cnf alternatively you could just search for it in your home find ./ -name 'my.cnf'

Degraded