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 you're screwed so you have to create the file "~/.my.cnf"
Enable read/write for MySQL installed via MAMP (on Mac):
- open "MAMP" use spotlight
- click "Stop Servers"
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/"
- 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>
Replace "\"
to "/"
in your file path.
Like this:
INTO OUTFILE 'D:/MySql/mysqlTest.txt';
You cannot export data as it is configured in mysql config files. Open my.cnf config file and check.
Quote from MySQL doc
This variable is used to limit the effect of data import and export operations, such as those performed by the
LOAD DATA
andSELECT ... INTO OUTFILE
statements and theLOAD_FILE()
function. These operations are permitted only to users who have theFILE
privilege.
secure_file_priv
may be set as follows:
If empty, the variable has no effect.
If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.
If set to
NULL
, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.
(An empty value is the default, or it can be explicitly specified in my.cnf as secure_file_priv=""
. A NULL
value can be set with secure_file_priv=NULL
.)
So, if you want to export data, then you need to comment this option and restart mysql server. Then you will be able to export.
- Edit the (/etc/my.cnf file for CentOS) or (my.ini file for Windows)
- Add
secure-file-priv = ""
line at the end - Stop mysql service using
systemctl stop mysqld
- Restart it using
systemctl start mysqld
It will now allow you to import and export the data.