How to Solve MySQL Error 1290 (HY000) --secure-file-priv Issue?

MySQL Error 1290 (HY000) -- How to Fix the --secure-file-priv Issue

When attempting to export MySQL script results to a text file using INTO OUTFILE, you may encounter the following error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This error occurs because the MySQL server's secure_file_priv option restricts writing to specific directories.

Ubuntu 16.04

Identify Allowed Write Directory:

mysql> SELECT @@GLOBAL.secure_file_priv;

Write to Allowed Directory:

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

Mac OSX (MAMP)

Identify Allowed Write Directory:

mysql> SELECT @@GLOBAL.secure_file_priv;

NULL Value:

If the result is NULL, create a ~/.my.cnf file:

$ vi ~/.my.cnf
[mysqld_safe]
[mysqld]
secure_file_priv="/Users/russian_spy/"

Non-NULL Value:

Modify the /etc/my.cnf file:

[mysqld]
secure_file_priv="/Users/russian_spy/"

Restart MySQL and Verify:

1. Stop MySQL servers (MAMP: "Stop Servers").
2. Update secure_file_priv in ~/.my.cnf or /etc/my.cnf.
3. Start MySQL servers (MAMP: "Start Servers").

mysql> SELECT @@GLOBAL.secure_file_priv;

Export Table to CSV File:

mysql> SELECT * FROM train INTO OUTFILE '/Users/russian_spy/test.csv' FIELDS TERMINATED BY ',';

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