mysql dump to localhost outfile from a remote database

Question

I'm stuck. I basically want to create a LOCAL data file (csv file) from a remote database using the OUTFILE command.

I am basically, pulling data.. and want to create it on my local file server vs. creating the outfile on the remote server. I'm limited on space remotely, thus I want to create the file locally. What am I missing on how to do this? Thanks!

This is my working syntax so far on the command line (it is creating the file I want, but on the remote server)

mysql -u test -pfoo --database test -h testdb201.name.host.com --port 3306 -ss -e "SELECT 'a','b','c' UNION SELECT col1, col2, col3 INTO OUTFILE '/tmp/mytest.csv' FIELDS TERMINATED BY ','  FROM tst_p000 limit 10"

Answer 1

According to the MySQL Select syntax, You can't use OUTFILE to output to a file outside the server itself.

You would need to converted the tab-delimited output of the query to CSV format like this (sed command credited here).

mysql -u test -pfoo --database test -h testdb201.name.host.com --port 3306 -ss -e "SELECT 'a','b','c' UNION SELECT col1, col2, col3 " | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > myDump.csv