I have a huge MySQL table, 2.1 million records, 200MB. Once a week I
need to dump it in CSV format and zip the file.
This is not on my server, and it's in production, so I don't want to
risk testing different methods and possibly hanging up their server
for a period of time, so I wanted to seek advice here first to find
what's the best way to proceed.
I can easily use PHP to query the table for the results I want and
write a file line by line and then zip it, but I'm worried that might
take too long and hang up the machine. The other way to go is some
kind of sql dump command, which I guess would be faster, but not sure
how much control I'd have over the exact format of the file. Any
suggestions which way I should proceed? Not hanging up their server
is my prime concern.
SELECT * INTO OUTFILE "/directory/myfile.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'