Location of my.cnf file on macOS
Solution 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:
- Opening a connection
- Selecting the 'Options File' under 'INSTANCE' in the menu.
- MySQLWorkbench will search for my.cnf and if it can't find it, it'll create it for you
Solution 2
In case of Mac OS X Maverick when MySQL is installed via Homebrew it's located at /usr/local/opt/mysql/my.cnf
Solution 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
- GlobalSYSCONFDIR
represents the directory specified with theSYSCONFDIR
option toCMake
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-specificmy.cnf
file resides. IfMYSQL_HOME
is not set and you start the server using themysqld_safe
program,mysqld_safe
sets it toBASEDIR
, 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.
-
Run:
sudo fs_usage | grep my.cnf
This will report any filesystem activity in real-time related to that file.
-
In another Terminal, restart your MySQL/MariaDB, e.g.
brew services restart mysql
or:
brew services restart mariadb
-
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.
Solution 4
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:
/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf
$MYSQL_HOME/my.cnf
-
defaults-extra-file
(the file specified with--defaults-extra-file=path
, if any) ~/.my.cnf
Solution 5
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.
Related videos on Youtube
nicolas
Updated on July 08, 2022Comments
-
nicolas almost 2 years
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.-
Artefact2 almost 12 yearsI think this belongs to serverfault.com. But still, welcome to SO!
-
-
nicolas almost 12 yearsSorry 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..
-
nicolas almost 12 yearsThanks!! 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!
-
Jpsy about 10 yearsAt 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
-
m02ph3u5 almost 10 yearswhich is /usr/local/Cellar/yourMySqlVersion/my.cnf
-
arcseldon over 9 yearsalso, you do need to have a populated locate db. on mac osx: sudo launchctl load -w /System/Library/LaunchDaemons/com.apple.locate.plist
-
Steven Lizarazo over 9 years/usr/local/opt/mariadb/VERSIONNUMBER/ in my case MariaDB is installed
-
Darragh Enright over 8 yearsThanks @Jpsy - that was super helpful.
-
Steve Tauber over 8 yearsMine is in /usr/local/etc/my.cnf
-
ryantuck over 8 yearsyou may want to ensure that mysql is actually loading in whichever
my.cnf
file you're editing viamysql --verbose --help | grep my.cnf
-
trojan about 8 yearsJust to be clear, you have to create "etc/" folder yourself and you need root privileges for that "sudo su -"
-
Mirza Vu over 7 yearsNot found in that location for me . I am using MAMP
-
Mirza Vu over 7 yearscreate /Applications/MAMP/Library/my.cnf for OSX El Capitan
-
Sergio Rodrigues over 7 years@mirzavu, see this link: stackoverflow.com/questions/678645/…
-
Christia about 7 yearsOn 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.
-
norman_h about 7 yearsI just installed 5.7.18 on 12.12.4 via homebrew and they are not there.
-
Chris Bartley over 6 yearsJust 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".
-
Pysis over 6 yearsIn 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. -
jenny over 6 yearsgreat! you saved my day.
-
gary over 6 yearsThanks! the
sudo fs_usage | grep my.cnf
method is quite efficient. I find this file on folder: /usr/local/etc/my.cnf -
Andru over 6 yearsGreat method, however I get several results there:
private/etc/my.cnf
,/usr/local/etc/my.cnf
,/usr/local/Cellar/mysql/<myVersion>/my.cnf
,~.my.cnf
withmy_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 thesemy.cnf
files don't exist! -
glerYbo over 6 years@Andru If none of them exists, create a new one.
-
Andru over 6 years@kenorb 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 formy.cnf
files there? What would be the order in which they are read? Or is just used? If so, which one? -
glerYbo over 6 years@Andru In any location that is read. If config doesn't exist, MySQL uses the default settings.
-
Alan Snyder over 6 yearsI 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. -
undefined about 6 yearsCorrection:
~/.my.cnf
. Add a dot before the file name. -
Joshua Pinter almost 6 yearsThanks 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.
-
Saurabh Shrivastava almost 6 yearsWhere is "INSTANCE" in the menu? Which menu? My screenshot : imgur.com/a/KyiIwGM
-
Joshua Pinter almost 6 yearsMine was
/usr/local/mysql/etc
. I'm on High Sierra but it was installed a few versions back. -
Inder almost 6 yearsconsider adding explanation to your asnwer
-
Ishan Srivastava almost 6 yearsyou are a life saver
-
Carlos over 5 yearsthink different
-
slashdottir over 5 yearsThis answer is old and depends on which installer you used to install mysql. Homebrew puts it one place, the dmg installer puts it another
-
Teddy about 5 yearsAs on date, in step 3, the Workbench just says "Location of conf file not specified" and it doesn't generate a new one automatically.
-
wandermonk almost 5 yearsmysql --help | grep "Default options" -A 1 . this helped.
-
redbirdo over 4 yearsYour 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 -?
-
Eduard Mukans over 4 yearsI had the same case, but I also had to add my.cnf file to
/etc/my.cnf
-
Shashikant Soni over 4 yearsThat command sudo fs_usage | grep my.cnf saved my day, Thanks a million.
-
aginanjar over 4 years@charles.cc.hsu: try using command
locate my.cnf
on your terminal. what the result ? -
civic.LiLister about 4 yearsI 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.
-
B. Bulpett almost 3 yearsWorks for me on BigSur with 8.0 installed via homebrew, thanks
-
Paul.j almost 3 yearswhen 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 -
jbobbins almost 3 years"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.