MySQL error 1290 with "--secure-file-priv" means the server refused to read or write a file because of the secure_file_priv setting. It hits SELECT ... INTO OUTFILE, server-side LOAD DATA INFILE and LOAD_FILE(). In most cases the fix is to use the directory the server allows, or to do the file work on the client, not to switch the protection off.
mysql> SELECT * FROM orders INTO OUTFILE '/tmp/orders.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Error 1290 (ER_OPTION_PREVENTS_STATEMENT, SQLSTATE HY000) is generic: "The MySQL server is running with the %s option so it cannot execute this statement" (server error reference). The option name in the message tells you which problem you have. This article covers the two you will meet most: --secure-file-priv and --read-only / --super-read-only.
What secure_file_priv does
The variable limits data import and export operations such as LOAD DATA, SELECT ... INTO OUTFILE and LOAD_FILE(), which are allowed only for users with the FILE privilege (server system variables). It has three kinds of values:
- Empty: no restriction. The documentation calls this not a secure setting.
- A directory: import and export work only with files in that directory. The directory must exist; the server does not create it.
- NULL: import and export are disabled completely.
It is not dynamic: it can only be set at server startup. The default depends on how MySQL was built: /var/lib/mysql-files for DEB and RPM packages, empty for the STANDALONE layout, and mysql-files under the install prefix otherwise. If you set it to a path that does not exist, the server logs an error and exits; if the value is insecure (empty, inside the data directory, or world-accessible), it logs a warning at startup.
Step 1: find out what is allowed
SHOW VARIABLES LIKE 'secure_file_priv';
-- +------------------+-----------------------+
-- | Variable_name | Value |
-- +------------------+-----------------------+
-- | secure_file_priv | /var/lib/mysql-files/ |
-- +------------------+-----------------------+
A directory means you write and read only there. NULL means file import and export through the server are off; go straight to the client-side options below. An empty value means this setting is not your problem, and you should look at file permissions instead.
Step 2: use the allowed directory
SELECT id, customer_id, total
FROM orders
WHERE created_at >= '2026-11-01'
INTO OUTFILE '/var/lib/mysql-files/orders_2026_11.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Details that trip people up (SELECT ... INTO):
- The file is created on the server host, not on your laptop. With a managed or remote server, you will not see it on your machine.
- The target must not be an existing file; MySQL will not overwrite it. Use a new name or remove the old file first.
- The file belongs to the operating system user that runs
mysqld, and is created with a umask of 0640. You may needsudoor group membership to read it. - You need the
FILEprivilege.
The same rules apply in the other direction: server-side LOAD DATA INFILE requires FILE and a file inside the secure_file_priv directory (LOAD DATA), and LOAD_FILE() silently returns NULL when the file is outside that directory, unreadable, or larger than max_allowed_packet (string functions). In our experience the NULL case wastes more time than the error, because nothing tells you why.
mysqldump --tab uses the same mechanism: the server writes the .txt data files, so the directory must be writable by the server, the account needs FILE, and the documentation says to use it only on the same machine as the server (mysqldump). If --tab fails with 1290, point it at the secure_file_priv directory. For choosing a backup tool, see mysqldump vs XtraBackup.
Step 3: or do the file work on the client
Often you do not want the file on the server at all. Two client-side options avoid secure_file_priv entirely:
# export on the machine where you run the client
mysql -h db1 -u report -p --batch -e "SELECT id, customer_id, total FROM shop.orders" > orders.tsv
# import from the client machine: needs local_infile=ON on the server
mysql --local-infile=1 -h db1 -u loader -p shop \
-e "LOAD DATA LOCAL INFILE 'orders.csv' INTO TABLE orders FIELDS TERMINATED BY ','"
- Export: the documentation itself suggests
mysql -e "SELECT ..." > file_nameto create the file on the client host.--batchgives tab-separated output. - Import: with
LOAD DATA LOCALthe client reads the file and sends it over the connection. You do not need theFILEprivilege, and the file can be in any directory the client can read. ButLOCALworks only if both server and client allow it, and the server variablelocal_infiledefaults toOFF(local_infile).
LOCAL changes error handling. Without REPLACE, LOAD DATA LOCAL behaves like IGNORE: data interpretation errors become warnings and the load continues, even in strict SQL mode, because the server cannot stop the file transfer mid-way (LOAD DATA). Check SHOW WARNINGS and compare row counts after every load.
Step 4: if you really need to change it
Sometimes an ETL job legitimately needs a different exchange directory. Set it in the option file and restart:
# /etc/mysql/mysql.conf.d/mysqld.cnf (path varies by distribution)
[mysqld]
secure_file_priv = /srv/mysql-exchange
# the directory must exist and be accessible to the mysqld user
# install -d -o mysql -g mysql -m 750 /srv/mysql-exchange
Do not set it to an empty string to make the error go away. Combined with the FILE privilege, an empty value lets a database account read any file the mysqld user can read, and write new files wherever that user can write. Pick a dedicated directory outside the data directory, not readable by everyone, and grant FILE only to the accounts that need it. On distributions with AppArmor or SELinux, in our experience the security profile may also need to allow the new path, otherwise you get permission errors even after the restart.
The other 1290: --read-only and --super-read-only
SELECT @@global.read_only, @@global.super_read_only;
If the message names --read-only or --super-read-only, the server is refusing writes. With read_only, only users with CONNECTION_ADMIN (or the deprecated SUPER) can still update; super_read_only blocks them too. Replication threads, operations on TEMPORARY tables and a few maintenance commands still work, and read_only is not replicated, so each server has its own value (server system variables).
In practice this almost always means the application is connected to a replica, or a failover happened and the connection string or proxy still points to the old primary. Do not "fix" it by turning read_only off on a replica: writes there split your data from the primary. Fix the routing. In Group Replication and InnoDB Cluster, secondaries run with super_read_only=ON by design (single-primary mode); see MySQL Group Replication and InnoDB Cluster.
What changes with large exports and imports
With a 10 MB test table, INTO OUTFILE into /var/lib/mysql-files is instant. With production data:
- Disk space on the server.
/var/lib/mysql-filesis usually on the same file system as the data directory. A full export of a large table can fill the disk the database needs to keep running. In our experience this is one of the more common self-inflicted outages during migrations. Pointsecure_file_privat a separate volume, or export from the client side. - Long transactions. A multi-hour
SELECT ... INTO OUTFILEon InnoDB runs as one long read, which holds back purge; the history list grows while it runs. Export in key ranges instead of one giant statement. - Parallel import. For big files, MySQL Shell's
util.importTablesplits the file into chunks (50M by default) and loads them over up to 8 threads by default. It usesLOAD DATA LOCAL INFILE, solocal_infilemust beONon the target server (MySQL Shell parallel table import). Switch it back off when the import is done. - Replication. Huge loads produce huge transactions in the binary log, and replicas apply them as one unit, so in our experience lag jumps. Load in batches you can afford to replay.
- Silent data loss with LOCAL. Because
LOCALturns errors into warnings, a ten-million-row import can quietly skip or truncate thousands of rows. Validate counts and checksums, not just the exit code.
Key takeaways
- Error 1290 is generic; the option named in the message tells you the cause.
- Check
secure_file_priv: a directory means use it,NULLmeans server-side file operations are off. INTO OUTFILEwrites on the server host, never overwrites, and needsFILE.- For files on your machine, use
mysql -e ... > fileandLOAD DATA LOCALwithlocal_infileenabled. - Never set
secure_file_privempty in production; use a dedicated directory on its own volume. - "--read-only" means you are writing to a replica or a demoted primary: fix the routing, not the flag.