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

On 5/2/07, Richard Davey <rich@corephp.co.uk> 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