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

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

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 youre 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>

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.

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

Replace to / in your file path.

Like this:

INTO OUTFILE D:/MySql/mysqlTest.txt;