Issue

In mysql 8, when I try to use SELECT ... INTO OUTFILE to export data to a file, but finally it throws an error.

mysql> select * from user FIELDS TERMINATED BY '|' INTO OUTFILE '/Volumes/develop/workspace/mysql/outfile.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Solution

The variable of secure_file_priv is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

secure_file_priv may be set as follows:

Check the secure_file_priv configuration with mysql command.

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)

secure_file_priv is read-only and cannot be changed with the set global command.

mysql> set global secure_file_priv='/Volumes/develop/workspace/mysql';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

It can only be modified in the my.cnf file. Note that it must be added under the [mysql] tag. It can be modified to an empty string or a specified path.

1. To an empty string

[mysql]
secure-file-priv = ""

2. To a specified path

[mysql]
secure-file-priv = "/Volumes/develop/workspace/mysql"

Note that in Linux/Unix, since mysql is run as a user named mysql, it cannot be exported to other user directories, such as:

mysql> select * from pigcms_shop_order INTO OUTFILE '/Users/cpm/Downloads/develop/workspace/mysql/outfile.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement