Trying to import files into MySQL (5.7.16), got Er

Question

I installed MySQL(5.7.16) using Homebrew, now I'm trying import a sql.file, here's the notification I got. Error Code: 1290( --secure-file-priv). My mac is 10.12.1. I saw the other answer and it suggested that the solution is to should add secure-file-priv = ""MySQL ERROR 1290 (HY000) --secure-file-priv option

But how can I do that on mac?

Answer 1

For MySQL installed via MAMP (on Mac):

Enable file i/o to/from local user account:

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>

Answer 2

I just got forced into upgrading to Sierra (10.12.1), and ended up downloading 5.7.16. I ran into the same issue where I couldn't import/export. To solve the issue, you have to include a --secure-file-priv option in your mysql command. Since I installed mysql to start automatically, the options are controlled by a plist file. On my installation, it was

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

Depending on OS and how you install (source, pkg, macports, etc), you're default starting process could be different. I installed from Oracle with a dmg and had this plist file controlling my startup. If you did the same, edit the plist file with sudo (I use vi):

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

and you should see a section that looks like

<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=/PATH/TO/DIR/</string>
    </array>

Add/edit that last line to the directory you want to import/export from/to.

When using IMPORT or EXPORT in mysql, you have to use the fullpath filename, i.e.:

select * from user INTO OUTFILE "/PATH/TO/DIR/file.txt";

Trying with just the filename won't work. The directory will also need write privileges if you plan to export (I had my directory under my personal user space and needed to make the specified directory writable to all - i.e.

chmod a+w /PATH/TO/DIR

That solved the issues for me.