What is the secure_file_priv Variable in MySQL?
Tags: programming, tutorial, mysql, productivity
Published: 2022-08-23 21:22:55

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](__GHOST_URL__/mysql-load-data-infile/)? 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:

![](https://arctype.com/blog/content/images/2022/08/image-8.png)

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.