How should I tackle --secure-file-priv in MySQL?
It's working as intended. Your MySQL server has been started with --secure-file-priv option which basically limits from which directories you can load files using LOAD DATA INFILE.
You may use SHOW VARIABLES LIKE "secure_file_priv"; to see the directory that has been configured.
You have two options:
- Move your file to the directory specified by
secure-file-priv. - Disable
secure-file-priv. This must be removed from startup and cannot be modified dynamically. To do this check your MySQL start up parameters (depending on platform) and my.ini.
I solved it using the LOCAL option in the command:
LOAD DATA LOCAL INFILE "text.txt" INTO TABLE mytable;
You can find more info here.
If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.
On Ubuntu 14 and Mysql 5.5.53 this setting seems to be enabled by default. To disable it you need to add secure-file-priv = "" to your my.cnf file under the mysqld config group. eg:-
[mysqld]
secure-file-priv = ""
I'm working on MySQL5.7.11 on Debian, the command that worked for me to see the directory is:
mysql> SELECT @@global.secure_file_priv;