MySQL on MACOS Sierra secure-file-priv setting

Question

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 >/dev/null &  

/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:

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

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.

Answer 1

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

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

restart MySQL and check:

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

Answer 2

Create a new file /etc/my.cnf and add the following lines

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

and restart

Answer 3

echo "[mysqld]\nsecure_file_priv\t\t= ''\n" | sudo tee /etc/my.cnf

And then restart mysql. If brew was used to install the mysql run the following command:

brew services restart mysql