How to fix secure-file-priv errors in MySQL or MariaDB

Server-side imports and exports in MySQL or MariaDB can fail when secure_file_priv blocks the file path. Moving the file into the server’s allowed directory and retrying the statement resolves these errors without removing the directory restriction.

A blocked LOAD DATA INFILE or SELECT … INTO OUTFILE statement stops with ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, and MariaDB names its own server in the same message. LOAD_FILE() raises no error and returns NULL instead. LOAD DATA LOCAL INFILE reads through the client and is outside this restriction.

File paths refer to the database server’s filesystem, even when the SQL client runs on another computer. Moving the file into the existing allowed directory needs no server restart, so it is the quickest fix. The SQL account still needs the FILE privilege, and the database service must be able to read or write the file.

1. Check the secure_file_priv value on the server raising the error.

   $ mysql -u root -p -e "SHOW VARIABLES LIKE 'secure_file_priv';"
   Enter password:
   +------------------+-----------------------+
   | Variable_name    | Value                 |
   +------------------+-----------------------+
   | secure_file_priv | /var/lib/mysql-files/ |
   +------------------+-----------------------+

   On MariaDB, use mariadb instead of mysql, and log in with the account you normally use if it is not root. A default Ubuntu install signs root in through its socket, so use sudo mysql there. Substitute the directory your server reports in the remaining steps. An empty value cannot raise this error, so the check reached a different server from the one reporting it.

   A NULL value on MySQL turns these statements off, so no path will work until the setting is changed. The setting can also move the allowed directory if the one shown is not usable.

   Related: How to change the secure-file-priv directory in MySQL or MariaDB

2. Copy the file you are importing into the allowed directory on the database server.

   $ sudo cp /tmp/new-orders.csv /var/lib/mysql-files/

   An export has no file to copy, so skip this step.

3. Open the SQL client with the account that reported the error.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   ##### snipped #####
   mysql>

   On MariaDB, use mariadb -u root -p.

4. Retry the failing statement with its file path inside the allowed directory.

   mysql> SELECT * FROM appdb.orders
       -> INTO OUTFILE '/var/lib/mysql-files/orders.csv'
       -> FIELDS TERMINATED BY ',';
   Query OK, 3 rows affected (0.00 sec)

   For an import, retry LOAD DATA INFILE with the copied file’s new path, which reports the records it loaded.

   An export never overwrites a file, so use a new filename each time. A permission error at this point comes from file ownership, AppArmor or SELinux, and removing the directory restriction would not fix it.