For homebrew mysql installs, where's my.cnf?

Question

For homebrew mysql installs, where's my.cnf? Does it install one?

Jericon · Accepted Answer · 2014-02-04T02:44:45.463

323

There is no my.cnf by default. As such, MySQL starts with all of the default settings. If you want to create your own my.cnf to override any defaults, place it at /etc/my.cnf.

Also, you can run mysql --help and look through it for the conf locations listed.

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

As you can see, there are also some options for bypassing the conf files, or specifying other files to read when you invoke mysql on the command line.

edited Feb 04 '14 at 02:44

answered Nov 02 '11 at 00:14

Jericon

4,992
3
20
22

56

This no longer seems to be the case; I see a my.cnf file in /usr/local/Cellar/mysql/5.6.15/ (or whichever version you have installed) – William Turrell Dec 30 '13 at 20:02
8

@williamt "mysql --help" doesn't list that file as being used, I think it's just a default that comes with the installation files – Vinicius Braz Pinto Jan 27 '14 at 17:01
1

@williamt I think Vinicius is right. I see /usr/local/Cellar/mysql/5.6.20_1/my.cnf but when I added the log-bin to it and restarted mysqld, it still didn't haven't binary logging enabled. It wasn't until I copied that my.cnf to /etc/my.cnf that binary logging was enabled. Perhaps that my.cnf file is used during the initial install, I'm not sure. – Mark Aug 27 '14 at 20:00
3

I'm on 5.6.26 and I don't see it there. – Adam Grant Oct 01 '15 at 20:19
5

On on 5.6.26 can be lolcated by running: `ls $(brew --prefix mysql)/*.cnf` – danielgpm Jan 12 '16 at 00:59
43

`mysql --help | grep cnf` was actually easier to find the lines. – vinyll Mar 21 '16 at 22:32
@Mark if you take a look at the init.d script in support-files/mysql.server, you can see it checks for an /etc/my.cnf – darethas May 25 '16 at 14:30
To add to @vynyll's snippet, adding 1 before-context line to grep makes the filtered docs a bit easier to read: `mysql --help | grep -B 1 cnf` – Luke Griffiths Oct 28 '16 at 17:54
Interesting that if you want to change the bind-address, you can't because it's overridden in the cli args. Turns out the plist actually has it: ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist – VladFr Nov 07 '16 at 18:35
3

As of now (Feb. 2020) `my.cnf` is located under `/usr/local/etc` (`mysql@5.7` brew formula) – Benito Feb 24 '20 at 10:27
I use "mdfind -name my.cnf" to find my.cnf file – Iqbal Khan Jan 10 '22 at 14:28

ewalshe · Answer 2 · 2017-03-02T13:53:05.980

275

The homebrew mysql contains sample configuration files in the installation's support-files folder.

ls $(brew --prefix mysql)/support-files/my-*

If you need to change the default settings you can use one of these as a starting point.

cp $(brew --prefix mysql)/support-files/my-default.cnf /usr/local/etc/my.cnf

As @rednaw points out, a homebrew install of MySQL will most likely be in /usr/local so the my.cnf file should not be added to the system /etc folder, so I’ve changed the command to copy the file into /usr/local/etc.

If you are using MariaDB rather than MySQL use the following:

cp $(brew --prefix mariadb)/support-files/my-small.cnf /usr/local/etc/my.cnf

edited Mar 02 '17 at 13:53

answered Dec 22 '11 at 01:21

ewalshe

3,684
2
19
19

4

`sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf` – Matt Clegg Aug 29 '13 at 12:23
9

If Homebrew installed MySQL in `/usr/local/` (which is the default I think), you can also place the `my.conf` in `/usr/local/etc/`, which don't require root privileges. – gitaarik Oct 08 '15 at 11:23
1

brew --prefix mysql doesn't give the correct path, for me, it shows '/usr/local/Cellar/mysql/5.7.16', but indeed mariadb is installed in '/usr/local/opt/mariadb/' – zhaozhi Dec 07 '16 at 10:28
@zhaozhi Use `brew --prefix mariadb`. In the MariaDB distribution the `my-default.cnf` does not exist - so use `my-small.cnf`. Try this `cp $(brew --prefix mariadb)/support-files/my-small.cnf /usr/local/etc/my.cnf` – ewalshe Dec 08 '16 at 13:05
This needs to be the accepted answer. The first one is incorrect. The question asked was relating to homebrew. – BugHunterUK Dec 11 '16 at 18:41
This solution was help me, although I installed the percona-server. Thanks. – De Nguyen Mar 01 '17 at 03:20

score 36 · Answer 3 · answered Nov 01 '11 at 23:22

36

One way to find out:

sudo /usr/libexec/locate.updatedb
# wait a few minutes for it to finish
locate my.cnf

answered Nov 01 '11 at 23:22

John Bachir

22,495
29
154
227

5

Awesome answer, I learned about locate.updatedb. However, there is no config file by default, see the answer below – glebm Dec 01 '12 at 23:24
22

You can use `mdfind -name my.cnf` instead of `locate` command on OSX – JacopKane Mar 20 '16 at 12:08
For me `locate my.cnf` worked directly. I didn't run `sudo /usr/libexec/locate.updatedb` – Andru Feb 09 '18 at 11:20
3

`/usr/local/Cellar/mysql/8.0.16/.bottle/etc/my.cnf` and `/usr/local/etc/my.cnf` was what I got – Vincent Tang Jun 03 '19 at 03:27

score 25 · Answer 4 · answered Aug 10 '17 at 15:10

25

in my system it was

nano /usr/local/etc/my.cnf.default

as template and

nano /usr/local/etc/my.cnf

as working.

answered Aug 10 '17 at 15:10

Fusca Software

709
6
11

Simba · Answer 5 · 2023-01-31T02:54:12.227

Add another answer cause

The accepted anwser is right. Since we're talking about Homebrew installed mysql, not MySQL installed manually, there's more direct way to find the conf.
The former answers may be a little outdated, Homebrew on M1 Mac is stored in a different location

Conf

The my.cnf is copied by Homebrew to following places during installation

/usr/local/etc/my.cnf for x86 Mac
/opt/homebrew/etc/my.cnf for M1 Mac

Homebrew chooses /usr/local, or /opt/homebrew to store packages, so the default conf files are not stored in /etc/ but /usr/local/etc or /opt/homebrew/etc.

In fact, homebrew changed the -DSYSCONFDIR= (default conf location) flag during compiling mysql from source.

Start the Service

A short answer: run brew info mysql and check the tips.

The recommended way is brew services start mysql, which uses the launchd to manage services. (launchd is deemed a systemd alternative on macOS)

For anyone wanna start it manually, mysql.start without any option is enough to start the service. (mysql.start is a script provided by mysql to help start the service)

You can also use `brew services run mysql` which runs the server without setting it up for auto-start. — Dilum Ranatunga, Jan 21 '22 at 17:15
thanks ! for pointing out this one. /opt/homebrew/etc/my.cnf for M1 Mac — ejabu, Sep 11 '22 at 05:12

naabster · Answer 6 · 2019-03-19T19:54:55.360

20

Nothing really helped me - I could not overwrite settings in a /etc/my.cnf file. So I searched like John suggested https://stackoverflow.com/a/7974114/717251

sudo /usr/libexec/locate.updatedb
# wait a few minutes for it to finish
locate my.cnf

It found another my.cnf in

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

changing this file worked for me! Don't forget to restart the launch Agent:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Update:

If you have a fairly recent installation of homebrew you should use the brew services commands to restart mysql (use your installed homebrew mysql version, i.e. mysql or mysql@5.7):

brew services stop mysql
brew services start mysql

edited Mar 19 '19 at 19:54

answered Oct 17 '14 at 14:58

naabster

1,494
12
14

I think you can also run `brew services stop mysql` and `brew services start mysql` in place of the `launchctl unload ...` lines. – mhulse Mar 18 '19 at 22:05
1

That ist true - but at the time of writing this answer these homebrew commands were not yet available. I'll update the answer – naabster Mar 19 '19 at 19:51

score 19 · Answer 7 · answered Sep 08 '18 at 07:27

19

Since mysql --help shows a list of files, I find it useful to pipe the result to ls to see which of them exist:

$ mysql --help | grep /my.cnf | xargs ls
ls: /etc/my.cnf: No such file or directory
ls: /etc/mysql/my.cnf: No such file or directory
ls: ~/.my.cnf: No such file or directory
/usr/local/etc/my.cnf

For my (Homebrew installed) MySQL 5.7, it seems the files is on /usr/local/etc/my.cnf.

answered Sep 08 '18 at 07:27

Emil Stenström

13,329
8
53
75

/usr/local is correct for home-brew on an intel Mac. with Apple Silicon home-brew is now at /opt/homebrew and the paths are changing – Bernhard Zürn Dec 12 '22 at 09:22

score 12 · Answer 8 · answered May 19 '14 at 06:39

On your shell type my_print_defaults --help

At the bottom of the result, you should be able to see the file from which the server reads the configurations. It prints something like this:

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

score 7 · Answer 9 · answered May 29 '20 at 06:59

7

Server version: 8.0.19 Homebrew. macOS Catalina 10.15.5 and installed MySQL via Homebrew. Found this file here:

/usr/local/etc/my.cnf

This solution helped :)

answered May 29 '20 at 06:59

Connor Leech

18,052
30
105
150

score 6 · Answer 10 · answered Jun 09 '16 at 15:04

6

You can find where the my.cnf file has been provided by the specific package, e.g.

brew list mysql # or: mariadb

In addition to verify if that file is read, you can run:

sudo fs_usage | grep my.cnf

which will show you filesystem activity in real-time related to that file.

answered Jun 09 '16 at 15:04

kenorb

155,785
88
678
743

Brilliant! With grep, amazing! – user3413723 Apr 19 '23 at 01:21

score 4 · Answer 11 · answered Nov 01 '11 at 23:27

4

I believe the answer is no. Installing one in ~/.my.cnf or /usr/local/etc seems to be the preferred solution.

answered Nov 01 '11 at 23:27

chrismealy

4,830
2
23
24

3

On my MBP only /etc/my.cnf allows me affect the Homebrew installation of mysql. – ewalshe Dec 22 '11 at 01:11

score 2 · Answer 12 · answered Feb 11 '21 at 21:14

2

run

sudo find / -name my.cnf

Usually the first result is the correct one. Should be in

/usr/local/etc/

answered Feb 11 '21 at 21:14

detzu

701
6
12

score 1 · Answer 13 · answered Jul 09 '18 at 16:10

In case of Homebrew, mysql would also look for my.cnf in it's Cellar directory, for example:

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

For the case one prefers to keep the config close to the binaries - create my.cnf here if it's missing.

Restart mysql after change:

brew services restart mysql

score 0 · Answer 14 · answered Sep 10 '21 at 06:57

0

If you are using mac m1 (Apple silicon), the my.cnf is located at

/opt/homebrew/etc/my.cnf

and can also be found by mysql --help

answered Sep 10 '21 at 06:57

Sinux

1,728
3
15
28

score 0 · Answer 15 · answered May 26 '22 at 13:07

0

I have installed MySQL 5.7 using Homebrew

my.cnf file is located in "/opt/homebrew/etc/my.cnf"

answered May 26 '22 at 13:07

Sathish

1,481
5
20
33

score -1 · Answer 16 · answered Sep 01 '18 at 05:38

For MacOS (High Sierra), MySQL that has been installed with home brew.

Increasing the global variables from mysql environment was not successful. So in that case creating of ~/.my.cnf is the safest option. Adding variables with [mysqld] will include the changes (Note: if you change with [mysql] , the change might not work).

<~/.my.cnf> [mysqld] connect_timeout = 43200 max_allowed_packet = 2048M net_buffer_length = 512M

Restart the mysql server. and check the variables. y

sql> SELECT @@max_allowed_packet; +----------------------+ | @@max_allowed_packet | +----------------------+ | 1073741824 | +----------------------+

1 row in set (0.00 sec)

score -1 · Answer 17 · answered Mar 14 '20 at 03:29

-1

$ps aux | grep mysqld /usr/local/opt/mysql/bin/mysqld --basedir=/usr/local/opt/mysql --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/opt/mysql/lib/plugin
Drop your my.cf file to /usr/local/opt/mysql
brew services restart mysql

answered Mar 14 '20 at 03:29

TiN

179
1
12

For homebrew mysql installs, where's my.cnf?

17 Answers17

Conf

Start the Service

Linked

Related