1

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.

3

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.

  • 1) as a note --secure-file-priv=/mysql_exp means that only the /mysql_exp directoy will be read/written when export and import operations. 2) For more flexibility, leave it empty --secure-file-priv=. 3) Using xcode I am not able to edit the file as it complains about permissions, what I did is using terminal+sudo I copied it to another dir, edited and the recopied it to the original location (always store an unchanged copy somewhere in case of a problem). – loco.loop Nov 14 '17 at 20:05
  • I do not have the com.oracle.oss.mysql.mysqld.plist file in LaunchDaemons. – Christia Oct 30 '18 at 7:34
0

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

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

and restart

  • This did not work for me (replacing "abc" with my username"). – Christia Oct 30 '18 at 7:33
-1
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
  • sudo tee /etc/my.cnf -- wouldn't that overwrite the entire configuration file? Seems a bit dangerous to me. Besides, it disables file security altogether, when you'd probably want to limit it to a certain directory only. – mustaccio May 10 at 21:19
  • Fair enough. In my case I was interested in getting a csv export of a mysql dump created by someone else. So installing a fresh mysql introduced the secure_file_priv constraint and /etc/my.cnf didn't exist. So there was nothing to overwrite and I didn't care where to save the export so that oneliner worked for me well. But I agree that I didn't think about overwritting the config file and the dump location might be inappropriate in some cases. – koleS May 11 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.