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
What is the secure_file_priv Variable and How Does it Work?
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):
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!
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.
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.