MySQL ERROR 1290 (HY000) --secure-file-priv option

Question

I am trying to write the results of MySQL script to a text file using the following code in my script.

SELECT p.title, p.content, c.name FROM post p
LEFT JOIN category c ON p.category_id=c.id
INTO OUTFILE 'D:\MySql\mysqlTest.txt';

However, I am getting the following

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

How do I solve this?

Answer 1

Ubuntu 16.04 (EASY): Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

Then, just write there

mysql> SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

Mac OSX: Mysql installed via MAMP

Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

NULL means you're screwed so you have to create the file "~/.my.cnf"

Enable read/write for MySQL installed via MAMP (on Mac):

1. open "MAMP" use spotlight
2. click "Stop Servers"

3. edit ~/.my.cnf (using vi or your favorite editor) and add the following lines:

   $ vi ~/.my.cnf

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

4. click "Start Servers" (in MAMP window)

Now check if it works:

a. start mysql (default MAMP user is root, password is also root)

$ /Applications/MAMP/Library/bin/mysql -u root -p 

b. in mysql look at the white-listed paths

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /Users/russian_spy/          |
+---------------------------+
1 row in set (0.00 sec)

c. Finally, test by exporting a table train into a CSV file

mysql> SELECT * FROM train INTO OUTFILE '/Users/russian_spy/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

Answer 2

1. Edit the (/etc/my.cnf file for CentOS) or (my.ini file for Windows)
2. Add secure-file-priv = "" line at the end
3. Stop mysql service using systemctl stop mysqld
4. Restart it using systemctl start mysqld

It will now allow you to import and export the data.

Answer 3

Replace "\" to "/" in your file path.

Like this:

INTO OUTFILE 'D:/MySql/mysqlTest.txt';

Answer 4

You cannot export data as it is configured in mysql config files. Open my.cnf config file and check.

Quote from MySQL doc

This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

secure_file_priv may be set as follows:

• If empty, the variable has no effect.

• If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.

• If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.

(An empty value is the default, or it can be explicitly specified in my.cnf as secure_file_priv="". A NULL value can be set with secure_file_priv=NULL.)

So, if you want to export data, then you need to comment this option and restart mysql server. Then you will be able to export.

Answer 5

Just create a file /etc/my.cnf with the following content

[mysqld]
secure_file_priv            = ''

You can use this oneliner:

echo "[mysqld]\nsecure_file_priv\t\t= ''\n" | sudo tee /etc/my.cnf

And then restart mysql. If brew was used to install the mysql run the following command:

brew services restart mysql

Answer 6

FOR MAC OS, if installed via HOMEBREW:

Edit my.cnf PATH: /usr/local/etc/my.cnf

COPY this:

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 0.0.0.0
secure-file-priv = ''

SAVE

Explanation: Secure_file_prive = NULL -- Limit mysqld not allowed to import and export Secure_file_priv = '/tmp/' -- Limit mysqld import and export can only occur in /tmp/ directory Secure_file_priv = '' -- does not restrict the import of mysqld

Answer 7

This is the example of my SQL that worked perfectly in WAMP SERVER (windows):

SELECT display_name,user_email,user_registered FROM test_wp_users
ORDER BY user_registered ASC
INTO OUTFILE 'C:/wamp64/tmp/usersbyemail.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

PS: Note that bars in path should be left-to-right to work perfectly in MYSQL.

Answer 8

In my my.ini I only had

# Secure File Priv.

and I tried to put:

# Secure File Priv.
secure-file-priv = ""

and

# Secure File Priv.
secure-file-priv = NULL

without making it work.

I finally deleted the line and left alone:

secure-file-priv = ""

Working correctly.

Answer 9

That's because secure_file_priv is set to NULL

mysql> show variables like secure_file_priv;

| secure_file_priv | NULL  |

You must stop mysql server

shell>/usr/local/mysql/support-files/mysql.server stop

Then restart mysql with the option defining where you want your files to be written to, for example to /tmp

shell>/usr/local/mysql/support-files/mysql.server start --secure-file-priv=/tmp

Then in mysql terminal you should see where your files can now be written to

mysql> show variables like secure_file_priv;

| secure_file_priv | /private/tmp/ |

On Mac you can find this folder by using Go To Folder /private/tmp in Finder

Answer 10

Go to C:\ProgramData\MySQL\MySQL Server 8.0 and you will find my.ini file. Open my.ini file in text editor (recommended notepad++) and then search for secure_file_priv and then replace will secure_file_priv="" and then save the file.

and restart your laptop.

Answer 11

For someone who is trying to export - from dbeaver.

You can right-click the query, and get the exact output of custom query as export.

No need for another conf file.