How to do a raw backup of a single large MySQL table?

phpMyAdmin, Perl script, and mysqlhotcopy haven't worked

[MichaelBluejay]

A client wants me to send him the raw data for a 350Mb table.

First I try to Export with phpMyAdmin. My browser gives an error (lost connection or somesuch). I subsequently learn that phpMyAdmin isn't good for backing up large tables.

Then I write a Perl script:

while ( ($rawtime,$size,$creative,$pageid) = $cursor->fetchrow) { 
  print FILE qq[$rawtime\t$size\t$creative\t$pageid\n]; 
}

I learned by accident long ago that if I set the Content-type: to something other than "text/html" (say, "downloadipoo/html") that the output will save to a file on my hard disk when run from a browser. My script works fine when my SQL statement retrieves just a little data, but when I tell it to retrieve the whole table, my output file winds up being completely empty.

Cruising Google I found mysqlhotcopy. I copied & pasted the code and saved it to a file called mysqlhotcopy.sh. But when I enter the command:

mysqlhotcopy dbname /tablename/

The shell tells me,

-bash: mysqlhotcopy not found

And yes, I executed that command from the same directory where mysqlhotcopy.sh is.

I suppose I could run my Perl script a bunch of times, limiting the number of records chosen each time and appending the retrieved records to the end of the text file, but that seems so cheap and sleazy. Is there a way to finish this race without limping across the finish line?

[MichaelBluejay]

Here are two more things I tried which didn't work, and then something that DID work.

I located the actual DB files on the server. I can do an "ls" and see a list of the databases, which are directories (which presumably hold the individual tables), but I'm unable to "cd" into a database directory. If I try "cd databasename" then I get the error "Permission denied", while if I try "sudo cd databasename" (I am logged in as sudo), then I get the error "cd: command not found".

I also found the "SELECT ... INTO OUTFILE" MySQL command. But when I tried issuing it from the command prompt, I got the error, "Access denied".

But then I hit on the solution: You know that Perl script I wrote? I simply ran it from the command line instead of from a web browser (duh). Took ten minutes to process, but it worked. Case closed.

May this thread help somebody else in the future.

[zCat]

If you have shell access on the server, you should be able to use MySQL's "mysqldump" utility, which is the best way to extract data.

[MichaelBluejay]

mysqldump adds all the MySQL commands to the output. That would triple or quadruple the size of my file. When you're talking about a 330Mb raw file, that's not trivial. That's why I was looking for a solution to get the raw data. Anyway, I was able to, whew.

[zCat]

mysqldump adds all the MySQL commands to the output. That would triple or quadruple the size of my file.

Ah, I thought mysqldump would have some method to dump the raw data, but can't see any obvious option.