35

Location of MySQL configuration file (ie: my.cnf) not specifiedenter image description here

How can I fixed this thing? I been trying to reinstall mysqlserver 5.5 / 5.7 and workbench countless of time. But I still not able to fix this issue.

Tedd Ang Teck Loon
  • 413
  • 1
  • 5
  • 11
  • How about specifying the location of the my.cnf file? Simply follow the link [here](https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-navigator-instance-options-file.html) and read [this](https://dev.mysql.com/doc/refman/5.7/en/option-files.html). – RobIII Oct 02 '16 at 23:21
  • 1
    Read it before. Don't really understood it. – Tedd Ang Teck Loon Oct 02 '16 at 23:46
  • @RobIII very sorry about it. Anyway I fixed it thanks. – Tedd Ang Teck Loon Jan 12 '17 at 02:15
  • 1
    I am having the same question. MySQLWorkbench says the location is not specified, but it does not tell me where and how to specify the location to MySQLWorkbench. The online documentation does not give this information either. Unfortunately, the questioner says he fixed it, but does not provide any info about how he did so. – not2savvy Jan 18 '17 at 15:17
  • Any Idea for this problem ? No one fixed it yet ? I got the same problem here. @RobIII : Forget about the rules, we really need help , you know ? Just answer it. If the question is a really poor question, then just edit the question make it better. I'm not sure you are willing to help here. Well I think you only just spamming comment for reputation :p – Iman Tumorang Feb 04 '17 at 09:18
  • That solution worked fine for me http://stackoverflow.com/a/31330398/4445238 – Alex Rewa Feb 10 '17 at 09:07
  • @not2savvy Sorry about it. I can't remember how I fixed it. When I recall I will let you know about it. – Tedd Ang Teck Loon Feb 16 '17 at 01:39
  • @TeddAngTeckLoon Never mind, I have solved it in the meantime and put my findings in below answer. – not2savvy Feb 16 '17 at 14:59

8 Answers8

51

I solved this problem. Step one: click here

enter image description here

Step two: and then click here to choose your path

enter image description here

Step three: my path is C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

maritree
  • 511
  • 4
  • 3
  • 1
    what if it is empty? What do I do now? – cegprakash Apr 09 '19 at 19:40
  • Awesome - the whole point: by clicking Server and then Options File from the menu as pointed out by RobIII is what gave the Error message - it can't find it - so how to set it and THIS answers that question!! Great thx for paying attention and providing screen shots.. I solved my issue quickly. I want to give you 10 upvotes. – Ken Jul 15 '19 at 21:41
  • 1
    I was able to do this by changing the Installation Type: to one of the other options besides the default Custom – Christopher Bell Jan 04 '20 at 20:47
  • Thanks Christopher Bell. You pointed out the problem: this file doesn't yet exist. The program says 'could not find', when it should say 'file not created', then prompt for a path. It's an odd miss in what seems to be an otherwise well-designed and solid program. – Stewii Apr 21 '20 at 21:43
24

If you already know where your configuration file is located, but just don't know how to specify it in mySQLWorkbench, you can skip steps 1-3 and just follow step 4 in these instructions.

Step 1: Make sure which mysql is running (just in case you have multiple installations on your system) and how: 

$ ps aux | grep mysql

may result in something like:

_mysql            120   0,0  3,4  5122736 565108   ??  Ss   Fr01pm   61:05.93 /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

If, in this output, you see that mysqld has been started with the --defaults-file option, that option specifies the configuration file used by msql. If so, you have found the configuration file and can skip to step 4.

If not, check if this returns the same binary location as above command:

$ which mysqld

If not, then make sure you use the full path for mysqld from the first output in the following step.

Step 2: Ask mySQL which configuration files it is using:

$ mysqld --verbose --help | grep -A 1 "Default options"

should output something like:

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

Step 3: Check the given locations in that order. The first finding should be your mySQL configuration file.

Step 4: In mySQLWorkbench go to Database > Manage Connections, then select the connection, select tab System Profile, and enter the path and file name in Configuration File.

This should be it.

not2savvy
  • 2,902
  • 3
  • 22
  • 37
  • Thank you so much for your guide, but I still have a question about the command in Step 1. You say in the output we can see the mysqld start with '--defaults-file', but how do you see that? And if this is the case, where is the my.cnf located then? – n4feng Feb 16 '17 at 19:47
  • If it is there (which it usually isn't) the path and name of the configuration file is given right after it. – not2savvy Feb 16 '17 at 22:16
8

And if still nothing happens, you may have used the installer Package. If you have, then you must select the Installation Type from the drop-down to reflect that before you locate the configuration file.

Here's a screenshot that follows from the above screenshots here's a screenshot that follows from the above screenshots

Also, there may not be a config file and you can create one here, then get out of the Options File, go back in and you will see all the options.

Leonardo Alves Machado
  • 2,747
  • 10
  • 38
  • 53
8

I changed my setting from Custom to MacOSx (MySQL Package) under: Database → Manage Connections → MySQL Connections → Select DB Connection → System Profile → Installation Type → (default setting) MySQL Package.

Change from custom to default setting:

Reconnect by testing connection:

double-beep
  • 5,031
  • 17
  • 33
  • 41
ketchuponpizza
  • 101
  • 1
  • 4
  • After doing this 10 times and 10 different ways over the years, this is the easiest method. Then just create /etc/my.cnf and restart mysql. – TommyGunn32 Aug 02 '22 at 13:16
1

For maria-db users

  1. back up the original /etc/mysql/my.cnf
  2. copy and rename /etc/mysql/mariadb.conf.d/50-server.cnf to /etc/mysql/my.cnf.

This would fix the problem.

Remember to revert changes after finish.

hoymkot
  • 438
  • 1
  • 4
  • 11
0

For those with Mac OS Catalina:

I did not have a my.cnf file. I went to /usr/local/mysql/ and created a directory named etc, which I did not have either. Then, inside that directory, I created the file (my.cnf).

This is the link that helped me How to fix --secure-file-priv option error

simplemente humano
  • 45
  • 9
0

for mysql 5.7 users and MAMP: just create /etc/my.cnf

Nir O.
  • 1,563
  • 1
  • 17
  • 26
0

macOS 13 and MySQL 8 current:

  1. Open MySQL Workbench on your Mac. Connect into your localhost instance of MySQL.

  2. Click the little wrench icon next to INSTANCE on the left pane above Startup / Shutdown.

  3. Select macOS as System Type, macOS as Installation Type, and the rest will default into place. Copy the location it gives you for the my.cnf file.

  4. Click Close

  5. Now click Options File on the left. It will notice that you don't have a file and will show you default values.

  6. Click the Apply... in the bottom right corner.

  7. It will then ask you for a password of your current logged in user of your Mac to create and save the my.cnf file.

  8. Now in the Finder use the keyboard combo of Command-Shift-G and copy in your location we copied early (i.e. /etc/my.cnf)

Ethan Allen
  • 14,425
  • 24
  • 101
  • 194