Where to find where mysql startup variables are set?

Question

I am encountering a similar problem to this, in that I cannot export to a file, but is not a duplicate as I am not asking how to fix that problem, but to find where the variables are set.

When I run 'mysqld --verbose --help', secure-file-priv is ONLY set to null. And it does read:

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 

BUT this does not seem to be the actual config, but the default. I installed with homebrew.

I tried many of the provided solutions, but that variable just seems to not change after I make a change and then stop, start servers.

What I have tried:

1. adding 'secure-file-priv = ""'

   /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/federated/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndb_memcache/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndb_ddl/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndb_binlog/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndb_big/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndb/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndbcluster/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndb_team/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/ndb_rpl/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/rpl_ndb/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/rpl/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/suite/rpl/extension/bhs/my.cnf /usr/local/Cellar/mysql/5.7.16/mysql-test/include/default_my.cnf

2. I have set that same item to an actual location. Still shows as NULL.

3. I have edited mysqld.cnf to have the same settings.

4. I have edited com.oracle.oss.mysql.mysqld.plist to have that setting (I am on Mac El Capitain)

I think my next step is to uninstall and reinstall, but would prefer not to.

Answer 1

The error I was getting

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I was able to fix it by opening /usr/local/mysql/support-files/mysql.server and changing the following line:

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" -- $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

to

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="" $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

Answer 2

If you install MySQL via MAMP, the config file is simply the local dot file here:

~/.my.cnf

To enable file read/write for MySQL installed via MAMP (on Mac):

1. open "MAMP" use spotlight
2. click "Stop Servers"

3. edit ~/.my.cnf (using vi or your favorite editor) and the following lines:

   $ vi ~/.my.cnf

[mysqld_safe]
[mysqld]
secure_file_priv="/Users/russian_spy/"

4. click "Start Servers" (in MAMP window)

Now check if it works:

a. start mysql (default MAMP user is root, password is also root)

$ /Applications/MAMP/Library/bin/mysql -u root -p 

b. in mysql look at the white-listed paths

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /Users/russian_spy/          |
+---------------------------+
1 row in set (0.00 sec)

c. Finally, test by exporting a table train into a CSV file

mysql> SELECT * FROM train INTO OUTFILE '/Users/russian_spy/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>