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 ....
SELECT * INTO OUTFILE ....
LOAD DATA INFILE ....
LOAD DATA LOCAL INFILE ....
LOAD DATA LOCAL INFILE
SELECT INTO OUTFILE LOCAL
From the manual:
The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. 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."
mysql -h my.db.com -u usrname--password=pass db_name -e 'SELECT foo FROM bar' > /tmp/myfile.txt