MySQL on MACOS Sierra secure-file-priv setting I cannot find where to set secure-file-priv. I get this message:


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


when running this statement:

SELECT COLUMN_NAME, data_type FROM tabledefs INTO  OUTFILE  'xpeter'


If I remove OUTFILE 'xpeter'  it works.  OUTFILE '/tmp/xpeter does not work either.

I tried to add --secure-file-priv="/tmp" , no change. Variable stays as NULL:

 $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="/tmp"    $other_args &gt;/dev/null &amp;  

/usr/local/mysql/bin/mysqld --verbose --help | grep secure-file
  --secure-file-priv=name 
secure-file-priv                                             NULL


There is no my.cnf file.  I run through whole filesystem, looking for any *.cnf file.  Could not find any other than: ./mysql-5.7.17-macos10.12-x86_64/data/auto.cnf and ./mysql-5.7.17-macos10.12-x86_64/support-files/my-default.cnf.  The line $bindir/mysqld...  is in support-files/my-default.cnf.

I rerun the query without OUTFILE 'xpeter',  query OK, no messages in log files except apache2 log.  Then I run

SELECT COLUMN_NAME, data_type INTO  OUTFILE  'xpeter' FROM tabledefs


no errors in  logfiles, only in phpmyadmin (where I run the query statement from, logged in as root): same message as before.

I tried the etc/my.cnf and also:

&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"&gt;
&lt;plist version="1.0"&gt;
&lt;dict&gt;
      &lt;key&gt;ProgramArguments&lt;/key&gt;
        &lt;array&gt;
   &lt;string&gt;--secure-file-priv=/mysql_exp&lt;/string&gt;
        &lt;/array&gt;
    &lt;key&gt;WorkingDirectory&lt;/key&gt;  &lt;string&gt;/usr/local/mysql&lt;/string&gt;
&lt;/dict&gt;
&lt;/plist&gt;


In both cases after reboot I was still not allowed to write a file.

I did not change the folder owner. If I add --secure-file-priv=/mysql_exp to Daemons and reboot MySQL does not run at all.
on mac os x startup parameters of mysql You can change by edit .plist file

location:

/Library/LaunchDaemons
com.oracle.oss.mysql.mysqld.plist


Use Xcode or for example BBEdit, and add line in section ProgramArguments

&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"&gt;
&lt;plist version="1.0"&gt;
&lt;dict&gt;
    &lt;key&gt;Disabled&lt;/key&gt;
    &lt;false/&gt;
    &lt;key&gt;ExitTimeOut&lt;/key&gt;
    &lt;integer&gt;600&lt;/integer&gt;
    &lt;key&gt;GroupName&lt;/key&gt;
    &lt;string&gt;_mysql&lt;/string&gt;
    &lt;key&gt;KeepAlive&lt;/key&gt;
    &lt;false/&gt;
    &lt;key&gt;Label&lt;/key&gt;
    &lt;string&gt;com.oracle.oss.mysql.mysqld&lt;/string&gt;
    &lt;key&gt;LaunchOnlyOnce&lt;/key&gt;
    &lt;false/&gt;
    &lt;key&gt;ProcessType&lt;/key&gt;
    &lt;string&gt;Interactive&lt;/string&gt;
    &lt;key&gt;Program&lt;/key&gt;
    &lt;string&gt;/usr/local/mysql/bin/mysqld&lt;/string&gt;
    &lt;key&gt;ProgramArguments&lt;/key&gt;
    &lt;array&gt;
        &lt;string&gt;/usr/local/mysql/bin/mysqld&lt;/string&gt;
        &lt;string&gt;--user=_mysql&lt;/string&gt;
        &lt;string&gt;--basedir=/usr/local/mysql&lt;/string&gt;
        &lt;string&gt;--datadir=/usr/local/mysql/data&lt;/string&gt;
        &lt;string&gt;--plugin-dir=/usr/local/mysql/lib/plugin&lt;/string&gt;
        &lt;string&gt;--log-error=/usr/local/mysql/data/mysqld.local.err&lt;/string&gt;
        &lt;string&gt;--pid-file=/usr/local/mysql/data/mysqld.local.pid&lt;/string&gt;
        &lt;string&gt;--secure-file-priv=/mysql_exp&lt;/string&gt;
    &lt;/array&gt;
    &lt;key&gt;RunAtLoad&lt;/key&gt;
    &lt;false/&gt;
    &lt;key&gt;SessionCreate&lt;/key&gt;
    &lt;true/&gt;
    &lt;key&gt;UserName&lt;/key&gt;
    &lt;string&gt;_mysql&lt;/string&gt;
    &lt;key&gt;WorkingDirectory&lt;/key&gt;
    &lt;string&gt;/usr/local/mysql&lt;/string&gt;
&lt;/dict&gt;
&lt;/plist&gt;


restart MySQL and check:

mysql&gt; show variables like 'secure%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| secure_auth      | ON          |
| secure_file_priv | /mysql_exp/ |
+------------------+-------------+
2 rows in set (0.00 sec)


Add:

You can achieve same result if will create my.cnf file straight in /etc/

sudo nano /etc/my.cnf


You can copy example file from 

/usr/local/mysql/support-files/


just notes about comments - "copy/past" it's not a way, even on Your Mac You really do not need file import/export at all
just use normal tools like:


MySQL Workbench
Navicat for MySQL
JetBrain IDE (most of them have database module)
DBVisualizer


any of them can help You:


export result of query into different format - csv, delimited file, json, xls
Import popular format of files direct into table, with mapping columns to datatypes


manual file level import/export could be useful in production environment where You need automate processes, but also for now it is more backup way, primary and proper - ETL scripts and tools.