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



ProgramArguments

--secure-file-priv=/mysql_exp

WorkingDirectory /usr/local/mysql



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

    Disabled
    
    ExitTimeOut
    600
    GroupName
    _mysql
    KeepAlive
    
    Label
    com.oracle.oss.mysql.mysqld
    LaunchOnlyOnce
    
    ProcessType
    Interactive
    Program
    /usr/local/mysql/bin/mysqld
    ProgramArguments
    
        /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
        --secure-file-priv=/mysql_exp
    
    RunAtLoad
    
    SessionCreate
    
    UserName
    _mysql
    WorkingDirectory
    /usr/local/mysql

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.

StackExchange Database Administrators Q#168768, answer score: 5