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

Solution 1:^[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:^[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:^[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.

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:^[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:^[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.

Solution 6:^[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

Solution 7:^[7]

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

Solution 8:^[8]

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.

Solution 9:^[9]

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

Solution 10:^[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>

Solution 11:^[11]

You can open a terminal and type locate my.cnf

Solution 12:^[12]

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

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

Solution 13:^[13]

Open Terminal and use below command:

sudo find / -name my.cnf

Solution 14:^[14]

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

Solution 15:^[15]

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

Solution 16:^[16]

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

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:

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)

Solution 17:^[17]

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

Solution 18:^[18]

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

Solution 19:^[19]

macOS High Sierra version 10.13.6

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

Solution 20:^[20]

/private/etc/my.cnf for Catalina

Solution 21:^[21]

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.

Solution 22:^[22]

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

Solution 23:^[23]

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

Solution 24:^[24]

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.

Solution 25:^[25]

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

Solution 26:^[26]

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

Solution 27:^[27]

For me in sierra version

copy the default configuration at:

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

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

Solution 28:^[28]

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.

Solution 29:^[29]

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.

Solution 30:^[30]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	André Thompson
Solution 2	Darryl Hein
Solution 3
Solution 4	Alexandre Cassagne
Solution 5	Nimantha
Solution 6	Sergio Rodrigues
Solution 7
Solution 8
Solution 9	Nimantha
Solution 10	JayRizzo
Solution 11	sreimer
Solution 12
Solution 13	Prashant Pimpale
Solution 14	Sheldon Wei
Solution 15	JayRizzo
Solution 16	Community
Solution 17	Francesc Roca
Solution 18	Mirza Vu
Solution 19	Michael Lihs
Solution 20	Mac L. Lak
Solution 21	attaboyabhipro
Solution 22	Pravin Bansal
Solution 23	Reaz Murshed
Solution 24	civic.LiLister
Solution 25	Raman Singh
Solution 26	Nguyen Thanh Tung
Solution 27	iamroald
Solution 28	Sameer Khanal
Solution 29	Eawaun
Solution 30	Mayank Raipure

'Location of my.cnf file on macOS

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]

Solution 16:[16]