[PHP] RE: [X-POST] Fastest way to dump this huge table

[Jay Blanchard]

[snip]
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.
[/snip]

SELECT * INTO OUTFILE /directory/myfile.csv
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
  LINES TERMINATED BY '\n'
  FROM table;

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Re: [X-POST] Fastest way to dump this huge table

[Colin Guthrie]

clive wrote:
 
 I'd say give some attention to the zip method you use.
 
 or better yet rsync the file, send only what has changed.

Good advice but may not work as well with extended insert syntax which
results in smaller dump files and much faster import too.

I guess with CSV it could work well tho' (my mind is stuck in mysql dump
output mode ;))

COl

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php