Mysql ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option
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
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
secure_file_priv may be set as follows:
If empty, the variable has no effect. This is not a secure setting.
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.
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