Reputation: 463

Location of MySQL configuration file (ie: my.cnf) not specified

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.

Upvotes: 40

Answers (9)

not2savvy

Reputation: 4243

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.

Upvotes: 25

Nilpo

Reputation: 4816

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.

Upvotes: 0

Ethan Allen

Reputation: 14835

macOS 13 and MySQL 8 current:

Open MySQL Workbench on your Mac. Connect into your localhost instance of MySQL.
Click the little wrench icon next to INSTANCE on the left pane above Startup / Shutdown.
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.
Click Close
Now click Options File on the left. It will notice that you don't have a file and will show you default values.
Click the Apply... in the bottom right corner.
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.
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)

Upvotes: 1

Nir O.

Reputation: 1651

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

Upvotes: 0

simplemente humano

Reputation: 55

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

Upvotes: 1

ketchuponpizza

Reputation: 111

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:

Upvotes: 8

hoymkot

Reputation: 458

For maria-db users

back up the original /etc/mysql/my.cnf
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.

Upvotes: 1

user9424581

Reputation:

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