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
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
One way to find out:
sudo /usr/libexec/locate.updatedb
# wait a few minutes for it to finish
locate my.cnf
I believe the answer is no. Installing one in ~/.my.cnf or /usr/local/etc seems to be the preferred solution.
run
sudo find / -name my.cnf
Usually the first result is the correct one. Should be in
/usr/local/etc/
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.
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)
$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
in my system it was
nano /usr/local/etc/my.cnf.default
as template and
nano /usr/local/etc/my.cnf
as working.
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 [email protected]):
brew services stop mysql
brew services start mysql
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
.
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
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 :)
Source: Stackoverflow.com