MySQL ERROR 1290 (HY000) --secure-file-priv option

Question

I am trying to write the results of MySQL script to a text file using the following code in my script.

SELECT p.title, p.content, c.name FROM post p
LEFT JOIN category c ON p.category_id=c.id
INTO OUTFILE 'D:\MySql\mysqlTest.txt';

However, I am getting the following

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

How do I solve this?

Answer 1

Ubuntu 16.04 (EASY): Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

Then, just write there

mysql> SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

Mac OSX: Mysql installed via MAMP

Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

NULL means you're screwed so you have to create the file "~/.my.cnf"

Enable read/write for MySQL installed via MAMP (on Mac):

1. open "MAMP" use spotlight
2. click "Stop Servers"

3. edit ~/.my.cnf (using vi or your favorite editor) and add 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

1. Edit the (/etc/my.cnf file for CentOS) or (my.ini file for Windows)
2. Add secure-file-priv = "" line at the end
3. Stop mysql service using systemctl stop mysqld
4. Restart it using systemctl start mysqld

It will now allow you to import and export the data.

Answer 3

Replace "\" to "/" in your file path.

Like this:

INTO OUTFILE 'D:/MySql/mysqlTest.txt';