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"

Napkin

Are you running this command on the remote server or your local server? – Banish

i don't think is doable, do you have ssh login to database server? – Byram

running this command on local server. got all my ssh keys working properly. i can get it to dump the file, but its on the remote server. i'm seeing now that OUTFILE is local to the server of the db. – Napkin

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

Gibb

sweet! nice trick. i wish i was good with sed. works like a charm. thanks for your help! – Napkin

Image the sed command divided into four parts, each separated by a semicolon. The first part of the sed command replaces every tab with "," (including the quotes). The second part replaces all beginning lines with double quotes. The third part replaces all ending lines with double quotes. The last part removes all newlines from the query. – Gibb

Great answer, thanks! I didn't want the fields to be quoted, so I changed the arguments to sed to s/\t/,/g and it worked smoothly. – Stearns