How to Resolve MySQL Error 1290 (HY000): --secure-file-priv Option?

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

When attempting to export MySQL script results to a text file using the INTO OUTFILE statement, you may encounter an error 1290 (HY000) due to the --secure-file-priv option. This error signifies that the MySQL server has been configured to restrict file access based on specific paths.

Solution

To resolve this issue, you need to configure your MySQL server to allow file access to a specific path where you wish to write the results:

For Ubuntu 16.04:

1. Use the following command to identify the allowed file writing directory:

   mysql> SELECT @@GLOBAL.secure_file_priv;

2. Specify the allowed directory within your INTO OUTFILE statement:

   mysql> SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';

For Mac OSX (MySQL installed via MAMP):

1. Check if the secure_file_priv option is set to NULL:

   mysql> SELECT @@GLOBAL.secure_file_priv;

2. If NULL, create a .my.cnf file in your home directory:

   $ vi ~/.my.cnf

3. Add the following lines to the file:

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

4. Restart the MySQL service.

5. Now, specify the allowed directory in your INTO OUTFILE statement:

   mysql> SELECT * FROM train INTO OUTFILE '/path/to/allowed/directory/test.csv' FIELDS TERMINATED BY ',';

The above is the detailed content of How to Resolve MySQL Error 1290 (HY000): --secure-file-priv Option?. For more information, please follow other related articles on the PHP Chinese website!