4

I wanted to disable some options in the MySql server strict mode, but for some reason I'm not even able to find its configuration file (my.cnf)

Currently installed MySql: Ver 14.14 Distrib 5.7.20, for macos10.12 (x86_64)

Output generated by 

mysql --help

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

But there is no such file as my.conf

I had done some research work on SO but none of the advises has worked for me.

What I'm supposed to do?

TrebledJ
  • 8,713
  • 7
  • 26
  • 48
Christian Felix
  • 644
  • 1
  • 9
  • 28

4 Answers4

8

There are defaults built into MySQL. The configuration file(s), if any, override the defaults.

Note that the config files are not looked at except during startup. So, editing or creating such a file has no effect until you restart mysqld.

If you mess up the syntax, mysqld will not start. Then you need to find the error, either during startup, or in a log file. (Or you could ask here "what is my syntax error".)

As for the location, and name, of the config files that will be used:

$ mysql --help

gives you the list for mysql, but perhaps you need it for the server, so try

$ mysqld --help --verbose

gives you long output; maybe 50 lines from the top, you should see something like:

Usage: mysqld [OPTIONS]

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

You asked about my.conf; was that a typo?

Regardless, If those files are missing, you can create them, make them readable by mysql, and put things into it preceded by [mysqld] so that the server will see them.

Personal override

Regardless of what is in the defaults and/or config files, you can add to the last file given. In the examples above that is 'hidden' .my.cnf in your home directory. All you need to do is create a few lines:

[mysql]
some_setting = somevalue
[client]
some_setting = somevalue

That helps for the "mysql" commandline tool and/or other clients (maybe).

But if you need to make changes to the server mysqld, it needs to be in one of the other files. Again, the minimum is something like

[mysqld]
some_setting = somevalue

Note the mysqld to refer to the server. And remember to restart the service.

Another note: If you see (in an existing config file), !includedir ..., then go to that directory to find any number of further files. You could add your own file, say z.cnf (so it would be picked last) with the two (or more) lines as indicated above.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • that's the point. @'L'l has recommended this approach also. But the question remains, how does it work without thoses config files? Where is this file on the OS system? and yes I had already tried to create the file manually, coping some default my.cnf content found on the internet. After some time of crafting around I gave it completly up as the mysql server couldn't start anymore. For me seems the efford inadequate to my current issues – Christian Felix May 18 '19 at 19:43
  • @ChristianFelix - I added 4 paragraphs to the beginning of my answer; I hope they address your concerns. – Rick James May 18 '19 at 19:50
  • @ChristianFelix - and more at the end of my Answer. – Rick James May 18 '19 at 20:01
  • that's the best answer so far. the bounty is yours. – Christian Felix May 18 '19 at 20:32
4

If you have installed Mysql 8 or Above using the package installer from mysql website, then follow what i did

  1. Create the config file in your home directory vim ~/.my.cnf
  2. Do open System preferences in mac

enter image description here

  1. Then Select the configuration file you created on the option. enter image description here
abdul rashid
  • 730
  • 9
  • 21
3

The content in this website says.

I had the urgent need to configure some specific stuff in MySQL 5.7 on my developer machine, a MacBook Pro running Mac OS Sierra. Unfortunately, I did not find a my.cnf file that could be customized anywhere?

Internet research showed that MySQL Database Server on Mac OS runs without a my.cnf config file by default, simply started with default values.

Luckily, it is pretty simple to customize the MySQL Server installation by creating and editing a custom my.cnf file like this:

sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf Then edit / customize the /etc/my.cnf file as required and restart your MySQL Server.

Or you can refer to this answer

Cybertronian
  • 473
  • 1
  • 8
  • 17
  • Link-only answers aren't generally helpful. Every answer should at least give a significant amount of value even if the user isn't able to follow the links (e.g. because the content has been removed). Links can be useful *additions*, but I'd recommend against posting an answer which is *nothing* but links. – Jon Skeet May 18 '19 at 10:42
  • I had done some resarch work before posting this issue also... but as stated previously, there is no such file as my-default .cnf on my system. – Christian Felix May 18 '19 at 19:46
  • Unfortunately, different systems (Windows, Ubuntu, Linux, Max, WAMP, LAMP, Percona, etc) choose to put the config files and/or default samples in different places with different names. – Rick James May 18 '19 at 19:52
1

You could try:

$ locate mysql | grep my.cnf

It should reveal the location(s) if it exists.

l'L'l
  • 44,951
  • 10
  • 95
  • 146
  • no results. I had also used: sudo find / -name *.cnf -type f ... and found some files but nothing of the related to mysql – Christian Felix May 06 '19 at 21:06
  • @ChristianFelix: what is the output of `which mysql`? – l'L'l May 06 '19 at 21:09
  • also no results...mysql server is still running – Christian Felix May 06 '19 at 21:15
  • @ChristianFelix: That makes absolutely no sense; try `ps -ax | grep mysql` – l'L'l May 06 '19 at 21:17
  • _mysql 96 0,0 0,1 4677960 4348 ?? Ss 27Apr19 0:29.64 /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 philip – Christian Felix May 06 '19 at 21:20
  • @ChristianFelix: Okay, I think I know what's going on now... the file actually doesn't exist on your system and you'll need to create it. See this answer: https://stackoverflow.com/a/10757261/499581 – l'L'l May 06 '19 at 21:22
  • @ChristianFelix: Did you create the file manually as it was explained? If `my.cnf` is not in `/etc` then you’ll need to ensure it is. – l'L'l May 06 '19 at 21:27
  • I wanted rather to copy it from some source file to the destination file.... this source file has to exists somewhere.. – Christian Felix May 06 '19 at 21:29
  • @ChristianFelix: If you want a default example try looking inside `/usr/local/mysql/support-files/` then make a copy of it and modify it however you want. – l'L'l May 06 '19 at 21:40