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.
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
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
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.
In mysql 5.6.22, which I installed it from Homebrew, the path of my.cnf is
/usr/local/opt/mysql/my.cnf
For Mac , what worked for me is creating a .my.cnf file in my ~ path. Hope this helps.
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
You can open a terminal and type locate my.cnf
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, voilaah you are good to go. happy coding
I'm running MacOS Catalina(10.15.3) and find my.cnf
in /usr/local/etc
.
Copy /usr/local/opt/mysql/support-files/my-default.cnf as /etc/my.cnf or /etc/mysql/my.cnf and then restart mysql.
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
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.
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.
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
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.:
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 '%\/%';
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
Should you need a detailed Reference Sample my.cnf:
No affiliation/association to the URL's
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)
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>
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
hope it help.
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
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
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
Open Terminal and use below command:
sudo find / -name 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
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
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.
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.
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.
/private/etc/my.cnf for Catalina
it should be usually under /usr/local/etc
on mac if you do not find it you can create one
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
- GlobalSYSCONFDIR/my.cnf
- Global
SYSCONFDIR
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.
Source: Stackoverflow.com