13

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"
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
jdamae
  • 3,839
  • 16
  • 58
  • 78
  • Are you running this command on the remote server or your local server? – Raghuram Jan 20 '11 at 04:28
  • i don't think is doable, do you have ssh login to database server? – ajreal Jan 20 '11 at 04:38
  • 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. – jdamae Jan 20 '11 at 04:54

1 Answers1

23

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
Adam Prax
  • 6,413
  • 3
  • 30
  • 31
  • sweet! nice trick. i wish i was good with sed. works like a charm. thanks for your help! – jdamae Jan 20 '11 at 04:57
  • 1
    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. – Adam Prax Jan 20 '11 at 05:07
  • 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. – Shiva Mar 08 '18 at 10:33