ContextI was working on a data migration task where I needed to export a large table to a CSV file for a reporting tool. I ran a standard SELECT ... INTO OUTFILE command, expecting a quick result. Instead, MySQL blocked me immediately with a security error. This also happens frequently when trying to bulk-load data using LOAD DATA INFILE.
mysql> SELECT * FROM orders INTO OUTFILE '/tmp/orders_dump.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
This error is a security feature, not a bug. MySQL uses the secure-file-priv variable to limit where the server can read or write files. This prevents an attacker who gains SQL injection access from reading sensitive system files (like /etc/passwd) or writing malicious scripts to the web root.
Debug ProcessThe first thing to do is find out exactly how the server is restricted. You can check the current value of the secure_file_priv variable directly from the MySQL shell:
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)
Interpreting the Output- A specific path (e.g., /var/lib/mysql-files/): The server will only allow import/export operations within this specific directory.- NULL: The server has disabled all import and export operations. This is common in newer MySQL installations by default.- Empty value (""): There are no restrictions. This is insecure but allows you to read/write anywhere the mysql user has OS-level permissions.## Solutions### Option 1: Use the Allowed Directory (Recommended)If the variable is set to a specific path, the easiest and most secure fix is to simply move your file there. Instead of trying to write to /tmp/, use the designated safe folder.
-- This will work if secure_file_priv is /var/lib/mysql-files/
SELECT * FROM orders
INTO OUTFILE '/var/lib/mysql-files/orders_dump.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
After the export, you can move the file from that directory to your desired destination using the command line.
Option 2: Changing the Configuration (Permanent Fix)If you need to export files to a different location or if the value is currently NULL, you must modify the MySQL configuration file.
On Linux (Ubuntu/Debian/CentOS)1. Open your MySQL configuration file (usually my.cnf or mysqld.cnf):
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# or
sudo nano /etc/my.cnf
- Look for the
[mysqld]section and find thesecure-file-priventry. If it doesn't exist, add it. To disable the restriction entirely, set it to an empty string:
[mysqld]
secure-file-priv = ""
- Save the file and restart the MySQL service:
sudo systemctl restart mysql
On Windows1. Locate your my.ini file (usually in C:\ProgramData\MySQL\MySQL Server X.X\). Note that ProgramData is a hidden folder.
- Find the
secure-file-privline and change it:
secure-file-priv=""
- Restart the MySQL service using
services.msc.
Option 3: Dealing with AppArmor (Ubuntu specific)Even if you set secure-file-priv = "", Ubuntu's AppArmor security module might still block the write operation to certain directories. If you see a "Permission denied" error despite the config change, you need to edit the AppArmor profile.
sudo nano /etc/apparmor.d/usr.sbin.mysqld
Add the paths you want to allow (e.g., /data/):
/data/ r,
/data/** rw,
Then reload AppArmor:
sudo systemctl reload apparmor
Verification StepsTo confirm the fix worked, log back into MySQL and check the variable again:
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+
If the value is empty, try a small test export:
mysql> SELECT 1 INTO OUTFILE '/tmp/test_write.txt';
Query OK, 1 row affected (0.00 sec)
If the file was created successfully in /tmp/, the issue is resolved.