MySQL turn off secure file priv is an easy task with this handy guide by our experts. 

At Bobcares, we offer solutions for every query, big and small, as a part of our MySQL Support Services.

Let’s take a look at how our MySQL Support Team helped our customers turn off secure file priv variable in MySQL.

How to turn off secure file priv MySQL variable

Recently, one of our customers kept running into the following error while using ‘INTO OUTFILE‘ or ‘LOAD DATA INFILE‘:

The MySQL server is running with the secure_file_priv option so it cannot execute this statement.

Additionally, here is a look at the snapshot of the error:

mysql> select option_value from wp_options WHERE option_value LIKE ('%sess%') INTO OUTFILE '/tmp/sam.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Fortunately, configuring MySQL secure file priv variable to fit our use case will resolve this error in no time.

To begin with, we can specify program options when we start mysqld server. In fact, these options help unlock several MySQL features, impose restrictions as well as change variables:

• mysqld: This reads options from the [server] and [mysqld] groups
• mysqld_safe: It reads options from the [server], [mysqld], [mysqld_safe], as wella s [safe_mysqld] groups
• mysql.server: This  reads options from the [mysql.server] and [mysqld] groups.

Additionally, we can take a quick look a the options supported by MySQL via this command:

$ mysqld –help

The secure_file_priv variable is responsible for limiting the effect of data export and import operations. For instance, operations that occur as a result of LOAD DATA or SELECT…INTO OUTFILE statements, as well as the LOAD_FILE() function.

In fact, these operations are allowed to go ahead only if the users have the FILE privilege.

We can check the current settings by logging into MySQL as a root user and running the following command:

mysql> SHOW VARIABLES LIKE "secure_file_priv";

This results in the directory path of the secure_file_priv variable. We can change the secure file priv variable directory by opening the MySQL options file and setting the variable under [mysqld] section as seen below:

[mysqld]
secure-file-priv=/mysqlfiles

After that, we have to create the directory we just configured

sudo mkdir /mysqlfiles
sudo chown -R mysql:mysql  /mysqlfiles/

Once we restart the MySQL service, we can confirm the changes have come into effect by running the SHOW VARIABLES command again.

Similarly, we can disable the secure-file-priv variable by setting it to a NULL variable as seen here:

[mysqld]
secure-file-priv = ""

Let us know in the comments how configuring the secure file priv variable in MySQL helped resolve your error.

[Need assistance with a different issue? We are available 24/7.]

Conclusion

In a nutshell, we learned how to resolve the MySQL server is running with the secure_file_priv option error. Our skilled MySQL Support Engineers at Bobcares demonstrated how to configure the secure file priv variable to suit our requirements.