MySQL Error: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This is not an issue and its the default behavior of MySQL because of some security configuration. MySQL server has been started with --secure-file-priv option which basically limits data import and export operations from and to some specific directories only.

We can check the value of this variable by running the following query in MySQL shell.

>SHOW VARIABLES LIKE "secure_file_priv";

The default value of this variable will be "/var/lib/mysql-files/". We can import any files from this folder and also all the exports should be done to this folder.

We can disable this configuration if needed by following the below steps.

Step1: Open the MySQL configuration file.

#vi /etc/my.cnf

Step2: Update the following line under [mysqld] and save.

[mysqld]
secure-file-priv = ""

Step3: Restart the MySQL service.

#systemctl restart mysqld

Please note that this is recommended as we are disabling one security measure.

That's all…