40

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.

Improve this question
8
  • How about specifying the location of the my.cnf file? Simply follow the link here and read this. Commented Oct 2, 2016 at 23:21
  • 1
    Read it before. Don't really understood it. Commented Oct 2, 2016 at 23:46
  • @RobIII very sorry about it. Anyway I fixed it thanks. Commented Jan 12, 2017 at 2: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. Commented Jan 18, 2017 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 Commented Feb 4, 2017 at 9:18

9 Answers 9

Reset to default
60

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

what if it is empty? What do I do now?
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.
I was able to do this by changing the Installation Type: to one of the other options besides the default Custom
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.
This DOES NOT address the issue. This only works for LOCAL MySql installations. If it's running locally, you probably already have root access to change privileges or adjust server configs. A working solution must be done from the client side only to compensate for MySql Workbench not exposing the no-tablespaces option.
|
26

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.

Improve this answer

2 Comments

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?
If it is there (which it usually isn't) the path and name of the configuration file is given right after it.
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.

Improve this answer

1 Comment

I didn't have the ini file. I extracted it for windows. I don't know root password also. And I'm stuck.
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:

Improve this answer

1 Comment

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.
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.

Improve this answer

Comments

1

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

Improve this answer

Comments

1

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)

Improve this answer

1 Comment

This is the perfect answer for MAC m1 . This worked for me. Thank you @Ethan Allen
0

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

Improve this answer

Comments

0

As of November 2023, the correct answer is that there is currently no way to fix this issue. As correctly noted in other answers, you need to supply the no-tablespaces option to mysqldump. MySql Workbench does not provide a way to do that.

All of the provided answers require changing server-side settings to return this as the default behavior. This IS NOT an acceptable solution to a client-side problem.

If you are using a local MySql installation, or if you have system level access to your server installation, you may proceed with the accepted answers as workarounds.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.