A few observations: 1 - if the table is in the InnoDB format, you aren't going to lock up their server, as InnoDB doesn't do table locking. SHOW TABLE STATUS LIKE 'tablename' will tell you.
2 - Ryan's mysqldump script looks useful - also, there's a little-used option with mysqldump that lets you specify a where clause to get just the records you want into the SQL file. 3 - since you're not operating on the server itself, but transferring over the net, the time for the transfer could become a problem, especially if you're not using InnoDB. You could copy the data into a temp table and then work with that to your heart's content, without tying up production tables. Something like this: DROP TABLE IF EXISTS _tmp_tablename; CREATE TABLE _tmp_tablename LIKE tablename; INSERT INTO _tmp_tablename SELECT * FROM tablename WHERE whatiwant blah blah then use select into outfile, mysqldump, php etc. on the _tmp_tablename table. While this does involve copying lots of records, in my experience, this sort of thing can be very fast, since it's all self-contained in the database software. Not having any indices on your temp table will help too. HTH, Dan On 5/2/07, Brian Dunning <[EMAIL PROTECTED]> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
