database – How should I tackle –secure-file-priv in MySQL?

database – How should I tackle –secure-file-priv in MySQL?

Its 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:

  1. Move your file to the directory specified by secure-file-priv.
  2. 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.

database – How should I tackle –secure-file-priv in MySQL?

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 = 

Leave a Reply

Your email address will not be published.