Tag: linux

I can never remember how to do this, so leaving this here:

select post_title, post_content from wp_posts
order by post_date asc 
into outfile '/var/lib/mysql-files/yourfile.txt';

Also see:

mysql ‘select … into outfile’ access denied

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.