1. Home
  2. Databases
  3. Secure_file_priv option in MySQL – Modify/Disable
  1. Home
  2. Programming Tools
  3. Secure_file_priv option in MySQL – Modify/Disable

Secure_file_priv option in MySQL – Modify/Disable

If you are getting an error “The MySQL server is running with the secure_file_priv option so it cannot execute this statement” while using ‘LOAD DATA INFILE‘ or ‘INTO OUTFILE‘, then here’s how you can fix it.

Here are the snapshots 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

You might also get the above error while loading data file using ‘LOAD DATA INFILE‘ in MySQL.

secure_file_priv in mysql

To fix this error, you need to check the value of ‘secure_file_priv‘ option.

mysql> show variables LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

(or)

mysql> SELECT @@secure_file_priv;
+-----------------------+
| @@secure_file_priv |
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+

As you can see the value of ‘secure_file_priv‘ is ‘/var/lib/mysql-files‘. It means, if you ever want to load a file (import), then the file should be located within this directory. Similarly, if you want to output into a file (export), then it can only output to file that’s located within this directory. Let’s check this now!

mysql> select option_value from wp_options WHERE option_value LIKE ('%sess%') INTO OUTFILE '/var/lib/mysql-files/sam.txt';
Query OK, 2 rows affected (0.02 sec)

So, what if you want to output into a different directory? To do that, you need to restart MySQL service with new value set to secure_file_priv option.

How to change the value of secure_file_priv in MySQL?

Step 1: Open my.cnf

# vim /etc/my.cnf

(or)

#vim /etc/mysql/my.cnf

Step 2: Set secure_file_priv in [mysqld] section.

[mysqld]
secure_file_priv="/usr/local/mysql-files"

Step 3: Restart MySQL service:

# /etc/init.d/mysqld restart

How to disable/remove secure_file_priv in MySQL?

You need to set ‘secure_file_priv‘ with an empty value as shown below and restart MySQL service.

#vim /etc/mysql/my.cnf

And add the below line under [mysqld] section:

[mysqld]
secure_file_priv=""
Caution

If secure_file_priv is empty, then it has no effect. It’s not a best practice in terms of security.

(or)

Alternatively, you can set NULL as value to secure_file_priv.

secure_file_priv=NULL

Restart MySQL service.

# /etc/init.d/mysqld restart

Now, you should be able to load data file or output into a file that’s located anywhere in the system.

mysql> select option_value from wp_options WHERE option_value LIKE ('%sess%') INTO OUTFILE '/tmp/out.txt';
 Query OK, 2 rows affected (0.01 sec)

You might want to read more about secure_file_priv.

Updated on September 15, 2017

Was this article helpful?

Related Articles

Add A Comment