Mysql ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option
Issue
In mysql 8, when I try to use SELECT ... INTO OUTFILE
to export data to a file, but finally it throws an error.
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:
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.
Check the secure_file_priv
configuration with mysql command.
secure_file_priv
is read-only and cannot be changed with the set global
command.
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
2. To a specified path
Note that in Linux/Unix, since mysql is run as a user named mysql, it cannot be exported to other user directories, such as: