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

Hello,

I wanted to export the csv file from MySQL command line. And used the guide following the article [HOW TO] Export mysql result to csv file. 

All are good if there is no any issue.

mysql> select * from devices where status = '2' INTO OUTFILE '/tmp/PLC_active.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

It means that my MySQL server has been started with --secure-file-priv option which basically limits from which directories you can load files using LOAD DATA INFILE

so I have two options
1. Move the file to specified directory
2. Disable --secure-file-priv

Now, I choose option 1.

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select * from devices where status = '2' INTO OUTFILE '/var/lib/mysql-files/PLC_active.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 769 rows affected (0.00 sec)

mysql> select * from devices where status != '2' INTO OUTFILE '/var/lib/mysql-files/PLC_inactive.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 113 rows affected (0.01 sec)

These files can export without any issue.

Tiến Phan - R0039

Knowledge is Endless
Sharing for Success