On 5/2/07, Richard Davey <[EMAIL PROTECTED]> wrote:
Brian Dunning wrote: > 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. If hogging their server is of prime concern then ideally you either need to (a) schedule down time for the site while the back-up happens, or (b) replicate the data to another MySQL server, and back-up that. I can't see any reason why you need to bring PHP into the equation here, MySQL has more than enough native tools to do exactly what you need - and probably a butt-load faster too. Cheers, Rich
Brian, As a comparison, on a *nix master/slave configuration, I'm mysqldumping nightly a database that's always growing and now is over a 1GB in size. The dump takes about 20 seconds to finish. Assuming a linear relationship, your 200MB backup should only tie up the db server for around 5 seconds. Bzipping the .sql file takes infinitiely longer, almost an hour, but a lower compression ratio should take only 1/4 to 1/3 as long. I'd say give some attention to the zip method you use. HTH, David
