How to fix secure-file-priv error in MySQL and MariaDB

MySQL and MariaDB include a security feature called secure-file-priv. This option restricts file access operations, such as loading data from or writing data to files, to a specific directory. If this option is not properly configured, you may encounter errors while trying to perform file-based operations.

The secure-file-priv setting is designed to prevent unauthorized access to the file system. It ensures that MySQL and MariaDB can only interact with files in approved directories. By default, this feature may block operations if files are located outside the designated directory. To continue using file-based operations, the value of secure-file-priv must be set correctly or disabled.

Modifying or disabling the secure-file-priv option requires editing the configuration file and restarting the server. You can either specify a directory for secure file access or set the option to NULL to allow unrestricted file operations. Each choice has security implications that must be considered based on your system's requirements.

1. Open the terminal.
2. Access the MySQL or MariaDB shell as the root user.

   $ mysql -u root -p
   Enter password:

3. Check the current value of secure-file-priv.

   mysql> SHOW VARIABLES LIKE 'secure_file_priv';
   +------------------+-----------------------+
   | Variable_name     | Value                 |
   +------------------+-----------------------+
   | secure_file_priv  | /var/lib/mysql-files/ |
   +------------------+-----------------------+
   1 row in set (0.01 sec)

   The value shows the directory where MySQL or MariaDB can perform file operations. A NULL value means no file operations are allowed.

4. Exit the database shell.
5. Open the my.cnf or my.ini file in a text editor.

   $ sudo vi /etc/mysql/my.cnf

   The location of the configuration file depends on your system. Common locations are /etc/mysql/, /etc/, or the MySQL installation directory.

6. Modify the secure-file-priv option under the mysqld section.

   [mysqld]
   secure_file_priv=/path/to/your/desired/directory/

   Setting secure-file-priv to NULL or using a directory with weak permissions can pose a security risk. Ensure the directory has proper permissions if changing this setting.

7. Save the file and close the text editor.
8. Restart the MySQL or MariaDB service.

   $ sudo systemctl restart mysql #For MySQL
   $ sudo systemctl restart mariadb #For MariaDB

   The command may differ depending on your Linux distribution or installation method.

9. Access the database shell again and verify the updated value of secure-file-priv.

   mysql> SHOW VARIABLES LIKE 'secure_file_priv';

10. Perform file-based operations in accordance with the updated settings.