MySQL SELECT INTO OUTFILE to a different server?

mysql sql into-outfile
ThinkCode picture ThinkCode · May 10, 2010 · Viewed 20.7k times · Source

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?

Answer

Ike Walker picture Ike Walker · May 10, 2010

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

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