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 <brian@stripped> 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/mysql?unsub=1
>
>
