If you have ever dived deeper into the settings letting you unleash the full power of MySQL, you might already come across a couple of settings letting you perform a variety of different tasks – one of such settings is the secure_file_priv variable.
What is the secure_file_priv Variable and How Does it Work?
The secure_file_priv variable is heavily associated with bulk importing of data inside of your InnoDB-based database instances. Remember how we said that LOAD DATA INFILE is significantly faster than INSERT statements due to the fact that it comes with many so-called “bells and whistles” unique to itself? Part of that magic is load_data_infile. The variable usually resides in your my.cnf file (which itself is located in your /var/bin/mysql directory) and looks something like the following (the following example refers to the variable being used in Windows environments):
secure_file_priv=”d:/wamp64/tmp”
This variable, simply put, puts a restraint on the directories that can be used to load data into your MySQL database instance. In other words, if you run a LOAD DATA INFILE query and the file you load into your InnoDB-based database instance does not reside in this directory, MySQL will come back with an error like so:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
To overcome this error, you either need to remote the --secure-file-priv setting from your my.cnf file, or load your data from a directory specified in the value of the variable. Once you do so, your data should be loaded in without any issues!
Other Concerns
Aside from loading data into your MySQL database instances using LOAD DATA INFILE and making use of this privilege, the value of such a variable might also be relevant when exporting data out of MySQL using SELECT … INTO OUTFILE. this variable can also be used to disable import and export operations using LOAD DATA INFILE and SELECT ... INTO OUTFILE: simply set this variable to NULL in such a case. In general though, you can also run a query like SHOW VARIABLES LIKE ‘secure_file_priv’ or SELECT @@secure_file_priv in order to observe the name of the directory that this variable is set to. Here is what Arctype would come up with:
As you can see, the directory is indeed set to ”d:/wamp64/tmp” as in the example we gave you above.
Summary
The secure_file_priv variable denotes the directory from which data files can be loaded into a given database instance or to which directory data can be written when exporting data when using LOAD DATA INFILE or SELECT ... INTO OUTFILE. MySQL allows you to observe the value of this parameter at all times by running either SELECT @@secure_file_priv queries or queries like SHOW QUERY VARIABLES LIKE ‘secure_file_priv’. The value of this parameter is worth keeping an eye on at all times: even if you think that you would not ever need to load bigger data sets into a given database instance or export such data sets from it.