MySQL SELECT INTO OUTFILE to a different server?

Question

I have a shell script on server a. The script spits out a csv file to a local directory. The problem is the database server is on server b. How do I use select * into outfile in a setup like this?

I get

Can't create/write to file '/home/username/test.csv/' (Errcode: 2)

Solved load data infile by using 'LOCAL' keyword. Is there something like that for outfile?

score 20 · Accepted Answer · answered May 10 '10 at 18:08

20

select into outfile can only create the file on the server, not the client.

Here's what the manual recommends for your situation:

If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.

http://dev.mysql.com/doc/refman/5.1/en/select.html

answered May 10 '10 at 18:08

Ike Walker

64,401
14
110
109

1

Any chance they will be implementing the LOCAL keyword into outfile like they do with infile in the next release? This simple issue is causing a great deal of extra work.. Thank you! – ThinkCode May 11 '10 at 13:15
I doubt it. Have you tried the recommended approach using `mysql -e` and redirecting the output to a local file? – Ike Walker May 12 '10 at 14:20
2

Since it was dumping it as text and there is no way of dumping it as csv file, I had to ignore this approach altogether. MySQL is making it really difficult to perform such a simple task :( Thanks again! – ThinkCode May 19 '10 at 15:45
Well, you can dump the results to a local tab-delimited file using `mysql -B -e` and then convert it to CSV. Here are some tips on doing the conversion: http://stackoverflow.com/questions/2535255/fastest-way-convert-tab-delimited-file-to-csv-in-linux – Ike Walker May 19 '10 at 17:52

score 2 · Answer 2 · edited Dec 22 '17 at 05:55

2

Use the command below:

mysql -uusername -ppassword -h DBIP DBNAME -e \
"SELECT * FROM tablename" > /destinationpath/outputfilename.csv

edited Dec 22 '17 at 05:55

clemens

16,716
11
50
65

answered Dec 22 '17 at 05:42

ASHIQ KT

21
2

not working.... – Syamsoul Azrien Jan 21 '22 at 00:54

MySQL SELECT INTO OUTFILE to a different server?

2 Answers2

Linked