Exporting select result from MySQL and getting the error ‘ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option’

To use ‘select … into outfile’ on MySQL the user needs to have the FILE permission as described here.

Even with this privilege however, if there server is running with the –secure-file-priv option you’ll see this error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option

When this option is enabled there’s usually one or more paths by default that are configured that you can import and export to. You can find these paths using:

mysql> SHOW VARIABLES LIKE "secure_file_priv";

+------------------+-----------------------+

| Variable_name    | Value                 |

+------------------+-----------------------+

| secure_file_priv | /var/lib/mysql-files/ |

+------------------+-----------------------+

1 row in set (0.02 sec)

Knowing where our trusted location is for importing/exporting, lets try again:

select examplecol from exampletable 
order by createdate desc limit 1 
into outfile '/var/lib/mysql-files/mysqlout.txt';

Success!

This is discussed in answer to this question here.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.