On Wed, May 2, 2007 8:54 am, 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. You could run mysql_dump once by hand and test just how bad it is, and be ready to kill -9 it if the server gets hurt... But, really, as a long-term SAFE solution, the best bet would probably be do set up a master/slave replication in MySQL, and have the *slave* be something non-production that you mysql_dump. There are some caveats to the replication, such as making SURE you have enough hard drive space for the log files, and enough bandwidth that the log files never grow too large. But once you get that working well, you can pretty much do whatever you want to the slave and not risk the master being bogged down. This is also a VERY common way to set up a "reporting" server, so your marketing weenies can write the ad hoc queries they love that tend to bring down a server... :-v > 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 you can't do a replication setup, or the budget is ridiculously low, running a PHP script with a sleep call and a set_time_limit to make sure it never dies, you can slowly and surely dump out a few records at a time. The risk there is inconsistencies in your dump, for related keys between tables... It will *NOT* be faster than mysql_dump, ever, by any means, almost for sure, but you'll be able to control how much it pounds the server more easily. mysql_dump *may* have some kind of command line flag to limit the resources it consumes so you don't drag down the DB... -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
