How to Disable Secure_file_priv option in MySQL – Linux

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.

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=""

(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.