Export a csv file from mysql to a local machine

Question

I'm working with a remote server to which I access via ssh. At the server there's a mysql database from which I'm making query

SELECT * FROM my_table where date >  '2010-01-01'';

So far so good, the issue raises when I tray to create an output file:

SELECT * FROM my_table where date >  '2010-01-01'  INTO OUTFILE '/home/ubuntu/data/my_output.csv';

I getting a permission error:

ERROR 1 (HY000): Can't create/write to file '/home/ubuntu/data/my_output.csv' (Errcode: 13)

Is there a way sent the file directly to my local machine instead of writing it first on the server where I don't have permissions to write files from the database?

Possibly useful: http://stackoverflow.com/questions/4250243/select-into-outfile-cant-write-to-file — Tim Biegeleisen, Jan 06 '17 at 14:55
Is there _anywhere_ you can write to, perhaps a temp directory? — Tim Biegeleisen, Jan 06 '17 at 14:55
@TimBiegeleisen thanks, that actually worked. Still I'm interested in the option of sending it directly to the local machine — Luis Ramon Ramirez Rodriguez, Jan 06 '17 at 15:07

score 3 · Accepted Answer · answered Jan 06 '17 at 15:05

You can tunnel the mysql port (on the server) via ssh.

Example of an ssh connection tunneling a port of the server (here in a .ssh/config file on ubuntu):

Host my_mysql_server
Hostname mysql_host_server
Port ssh_port
LocalForward 3307 localhost:3306
User ssh_user

Terminal example:

ssh -L 3307:localhost:3306 ... (hostname, ...)

You can change the 3307 to whatever port you want (should be a port that is not used yet).

Then you can do an export on your local machine. Put your sql query in a sql file (example: my_query.sql) and export it via following command in terminal (ubuntu):

mysql -h localhost -P 3307 your_database_name -u mysql_user -p < my_query.sql > export.csv

This seriously can't be the answer. Not dissing you at all, I'm dissing MySQL. This is a ridiculous thing to have to do... — Mark Gerolimatos, Sep 21 '17 at 04:57

Export a csv file from mysql to a local machine

1 Answers1