How to Fix MySQL ERROR 1290: \'The --secure-file-priv Option\'?

MySQL ERROR 1290 (HY000): The --secure-file-priv Option

Explanation:

The error "ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option" occurs when the MySQL server has been configured to enforce strict file access controls. The --secure-file-priv option prevents the server from writing to or reading files outside of a specified directory.

Solution:

There are several methods to resolve this error:

1. Check Allowed Write Directory:

• Run the query SELECT @@GLOBAL.secure_file_priv; to determine the directory where MySQL has permission to write files.
• If the result is NULL, you must create a file with the name .my.cnf in the home directory of the MySQL user. Add the following lines to the file:

[mysqld]
secure_file_priv="/path/to/allowed/directory"

• Replace /path/to/allowed/directory with the directory where you want to allow MySQL to write files.

2. Grant Access to Specific Directory (Ubuntu 16.04):

• Run the query SELECT @@GLOBAL.secure_file_priv; to determine the current write directory.
• Create a directory inside the write directory, e.g., /var/lib/mysql-files/report_directory.
• Run the query GRANT FILE ON '/var/lib/mysql-files/report_directory' TO 'your_user'@'host'; to grant the MySQL user permission to write to the new directory.

3. Disable --secure-file-priv Option (Not Recommended):

• Note that this option should only be used for testing purposes.
• Run the query SET GLOBAL secure_file_priv=''; to temporarily disable the --secure-file-priv option. This will allow you to write to any directory.
• It is recommended to re-enable the option after testing (SET GLOBAL secure_file_priv='/allowed/directory';) to maintain security.

The above is the detailed content of How to Fix MySQL ERROR 1290: \'The --secure-file-priv Option\'?. For more information, please follow other related articles on the PHP Chinese website!