Multi tool use

Clash Royale CLAN TAG

Location of my.cnf file on macOS

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.

my.cnf

21 Answers
21

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/.

/usr/local/mysql/support-files/

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

my.cnf

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

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

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

/usr/local/mysql/etc

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

/etc/mysql/my.cnf

SYSCONFDIR/my.cnf - Global

SYSCONFDIR/my.cnf

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.

SYSCONFDIR

SYSCONFDIR

CMake

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

$MYSQL_HOME/my.cnf

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.

MYSQL_HOME

my.cnf

MYSQL_HOME

mysqld_safe

mysqld_safe

BASEDIR

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

--defaults-extra-file=path

~/.my.cnf

~/.mylogin.cnf

^{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

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.

fs_usage

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.

sudo fs_usage | grep my.cnf

private/etc/my.cnf

/usr/local/etc/my.cnf

/usr/local/Cellar/mysql/<myVersion>/my.cnf

~.my.cnf

my_print_def

/usr/local/etc/my.cnf

my.cnf

my.cnf

my.cnf

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

--defaults-extra-file=path

~/.my.cnf

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

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

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

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

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.

/usr/local/mysql-5.6.17-osx10.7-x86_64/my.cnf

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

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


You can open a terminal and type locate my.cnf

locate my.cnf

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

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

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

[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 touch /{preferred-path}/my.cnf

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

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

Then restart mysql, voilaah you are good to go. happy coding

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!

you can check the file /usr/local/bin/mysql.server and see from where my.conf is being read. usually it is from /etc/my.cnf or ~/my.cnf

~/.my.cnf

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


I checked in macOS Sierra, the homebrew installed MySql 5.7.12

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.

my-default.cnf

/etc/my.cnf

/etc/mysql/my.cnf

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


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

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.

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

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

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

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.

/etc/mysql/my.cnf

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.

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

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.