MySQL - SELECT * INTO OUTFILE LOCAL ?

Question

MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client.
So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE ....

Now, we moved the database to a different server and SELECT * INTO OUTFILE .... no longer works, understandable - security reasons I believe. But, interestingly LOAD DATA INFILE .... can be changed to LOAD DATA LOCAL INFILE .... and bam, it works.

I am not complaining nor am I expressing disgust towards MySQL. The alternative to that added 2 lines of extra code and a system call form a .sql script. All I wanted to know is why LOAD DATA LOCAL INFILE works and why is there no such thing as SELECT INTO OUTFILE LOCAL?

I did my homework, couldn't find a direct answer to my questions above. I couldn't find a feature request @ MySQL either. If someone can clear that up, that had be awesome!

Is MariaDB capable of handling this problem?

Answer 1

From the manual: The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host."

http://dev.mysql.com/doc/refman/5.0/en/select.html

An example:

mysql -h my.db.com -u usrname--password=pass db_name -e 'SELECT foo FROM bar' > /tmp/myfile.txt 

Answer 2

You can achieve what you want with the mysql console with the -s (--silent) option passed in.

It's probably a good idea to also pass in the -r (--raw) option so that special characters don't get escaped. You can use this to pipe queries like you're wanting.

mysql -u username -h hostname -p -s -r -e "select concat('this',' ','works')"

EDIT: Also, if you want to remove the column name from your output, just add another -s (mysql -ss -r etc.)