mysql dump to localhost outfile from a remote database

mysql sql into-outfile
jdamae picture jdamae · Jan 20, 2011 · Viewed 17.4k times · Source

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

Adam Prax picture Adam Prax · Jan 20, 2011

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

Related questions

MySQL export into outfile : CSV escaping chars

I've a database table of timesheets with some common feilds. id, client_id, project_id, task_id, description, time, date There are more but thats the gist of it. I have an export running on that table to a CSV …

Read More
How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

I am trying to dump the contents of a table to a csv file using a MySQL SELECT INTO OUTFILE statement. If I do: SELECT column1, column2 INTO OUTFILE 'outfile.csv' FIELDS TERMINATED BY ',' FROM table_name; outfile.…

Read More
MySQL - SELECT * INTO OUTFILE LOCAL ?

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 .... …

Read More