LOAD DATA INFILE or INTO OUTFILE blocked by --secure-file-priv
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementShort answer
The server only reads and writes files inside the folder set in secure_file_priv. The value cannot be changed while MySQL runs. Run SHOW VARIABLES LIKE 'secure_file_priv', put your file in that folder, or load the file from the client side with LOAD DATA LOCAL INFILE.
Fix it fast
1Find out which cause you have
Print the value. A folder name means files must live there; NULL means import and export are switched off; empty means no restriction.
SHOW VARIABLES LIKE 'secure_file_priv';2Match what you see, then jump to its fix
- 1
The variable shows a path such as /var/lib/mysql-files/.The file is outside the folder named by secure_file_privFix - 2
SHOW VARIABLES prints NULL for secure_file_priv.secure_file_priv is NULL, so server-side files are disabledFix - 3
SET GLOBAL secure_file_priv fails, or the new value does not apply.You want a different folder and must change the settingFix
- 1
What does the error look like?
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Cause 1 of 3
The file is outside the folder named by secure_file_priv
1Check
With a non-empty directory name, the server limits import and export to that directory. The default on DEB and RPM packages is /var/lib/mysql-files.
SHOW VARIABLES LIKE 'secure_file_priv';2Fix
Copy the file into that folder so the server can read it, and use the full path. For an export, write into the same folder.
-- shell first: sudo cp data.csv /var/lib/mysql-files/
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
SELECT * FROM my_table
INTO OUTFILE '/var/lib/mysql-files/out.csv'
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';3Verify
The statement completes; the first load reports the number of rows.
SELECT COUNT(*) FROM my_table;Confirmed by dev.mysql.com
Cause 2 of 3
secure_file_priv is NULL, so server-side files are disabled
1Check
NULL disables import and export operations completely, so no folder will work.
SHOW VARIABLES LIKE 'secure_file_priv';2Fix
Use the client-side form, LOAD DATA LOCAL INFILE. The client reads the file and sends it, so no FILE privilege is needed and the file can be in any folder the client can read. It needs local_infile enabled on both server and client.
-- local_infile must be enabled on the server and in the client
LOAD DATA LOCAL INFILE '/home/me/data.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;3Verify
Rows are loaded from the client machine.
SELECT COUNT(*) FROM my_table;Confirmed by dev.mysql.com
Cause 3 of 3
You want a different folder and must change the setting
1Check
The variable is not dynamic: it can only be set at startup (command line --secure-file-priv=dir_name, or the option file). The directory must exist; the server does not create it, and a nonexistent path makes the server log an error and exit.
SHOW VARIABLES LIKE 'secure_file_priv';2Fix
Create the folder, then set the option under [mysqld] in the option file and restart the server.
# my.cnf, in [mysqld]
secure-file-priv=/srv/mysql-import3Verify
After the restart the variable shows the new path.
SHOW VARIABLES LIKE 'secure_file_priv';Confirmed by dev.mysql.com
How do I stop it coming back?
- Keep import and export files in one agreed folder that matches secure_file_priv.
- Do not set secure_file_priv to empty on a server others can reach; the manual calls that not secure.
- Prefer LOAD DATA LOCAL or a client-side export (mysql -e "SELECT ..." > file) when the file lives on another machine.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses writes: 'running with the --read-only option'
ERROR 1036 (HY000): Table 'orders' is read only
Error 1290 means the server is in read-only mode.
2 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
An EC2 instance cannot reach its RDS database
Could not open connection to the host, on port <port>: Connect failed
RDS denies all access until a security group allows it.
3 causes, each with a fixDatabases
Locked out of a fresh MariaDB install as root on Mac or Linux
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
A fresh MariaDB root account is not a normal password account.
3 causes, each with a fixDatabases
An Aria table fails with 'Read page with wrong checksum' (error 1030, code 176)
ERROR 1030 (HY000): Got error 176 "Read page with wrong checksum" from storage engine Aria
Code 176 means MariaDB's Aria engine read a data or index page whose checksum does not match, so that table file is damaged.
3 causes, each with a fix