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

Hey guys,

I am working with MySQL, then want to export the data to csv file. 

mysql> select name,address from devices into outfile '/tmp/devices.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n';ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Oops! unlucky, it returns the error. Before me, someone started MySQL with --secure-file-priv

mysql> SHOW VARIABLES LIKE "secure_file_priv"; 

+------------------+-----------------------+| 

Variable_name    | Value                  

|+------------------+-----------------------+| 

secure_file_priv | /var/lib/mysql-files/ | 

+------------------+-----------------------+ 

1 row in set (0.00 sec)

it looks like that all of files store at /var/lib/mysql-files/ as declared. Then I try to export again.

mysql> select name,address from devices INTO OUTFILE '/var/lib/mysql-files/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';Query OK, 891 rows affected (0.01 sec)

it works!!!

Tiến Phan - R0039

Knowledge is Endless
Sharing for Success