Mysql export resultset as CSV from remote server

Question

My application runs on server 1 and DB is on server 2.I want to get the output of select query as a CSV to server 1 i.e. my application server.How can I achieve this. Select into outfile wont help because it dumps only on the local server so if I run query on DB server it would create the file on it not the application server. mysql -e option also doesent help because it does not dump as CSV. Could anyone suggest how to directly create file locally as CSV from remote server? Thanks.

Actually I am also having the same scenario as you explained, I gone through the answer, still I am interested to in solution that you have actually implemented, whats experience? — Amogh, Jul 23 '15 at 20:13

score 13 · Accepted Answer · edited Jan 05 '16 at 16:29

13

You may use outfile and afterwards ftp the file to the local host or pass the result of a normal query to some sed/awk to convert it to csv?

What I found is: mysql -umysqlusername -pmysqlpass databasename -B -e "select * from \`tablename\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv

Besides that, we're going with the (s)ftp solution.

edited Jan 05 '16 at 16:29

Ben

54,723
49
178
224

answered May 30 '13 at 11:42

Axel Amthor

10,980
1
25
44

Or run mysql where you want the CSV file and connect using '-h yourdb.example.com' (assuming that it's directly accessible) – symcbean May 30 '13 at 11:44
1

@symcbean Connecting to a remote server with `-h` still writes the file to the remote DB server if you use the typical `SELECT... INTO OUTFILE` method. – mltsy Nov 10 '14 at 21:58
1

@mitsy: If you run the command as shown above, the file will be created on the client. – symcbean Nov 12 '14 at 10:10

score 2 · Answer 2 · answered Dec 20 '19 at 15:33

Newer versions of the MySQL clients (tested on 14.14) on Linux use tab delimiters by default when redirecting stdout to a file:

mysql -h serverhostname -uuserid -ppassword databasename -e 'select * from mytbale order by update_ts desc' >output.tsv

Most apps that accept csv will accept tsv, or you should be able to convert easily yourself, see How do I convert a .tsv to .csv?.

Mysql export resultset as CSV from remote server

2 Answers2