Exporting very large tables from phpMyAdmin?

Question

By very large tables, I mean tables with 5 million to 20 million records or even larger. I need to export them into MS Excel in multiple files, each of which will be 60,000 records.

It would be a manual chore to export them one by one, you know by entering Dump xxxx row(s) starting at record # xxxx and hitting Go a couple hundred times. Very annoying.

Is there any way to automate this process?

What I have tried thus far:

1. Add "print_r($_POST);exit();" at the top of phpmyadmin/export.php to see what variables have been passed when I'm exporting the table into Excel. Remove them and then use cURL to post the same variables to phpmyadmin/export.php from my own php script. This way, I can make it automated in saving the exported data and increasing starting record #. However, phpmyadmin/export.php keeps giving me this error when I'm posting all the vars to it:

   export.php: Missing parameter: what (FAQ 2.8)

   export.php: Missing parameter: export_type (FAQ 2.8)

Which is weird because I'm 100% sure that 'what' and 'export_type' are in the posted variables. Tried everything in (FAQ 2.8) but nothing worked neither.

2. Firefox iMacros add-on. Gave it a try but it doesn't seem to be able to automatically increase an form input value by a certain amount. Or does it?

Any idea how I can achieve this? There ain't any native support in exporting mysql into excel right?

Update: My client wants it. Not me. He's just obsessed with Excel and does everything with it.

Solution 1

Use the MySQL command line tool to export as CSV, and then use GNU split to split it every 65k lines or so.

SELECT fields INTO OUTFILE 'export.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\n' FROM tablename;

Solution 2

try with desktop application mySQL YOG

Solution 3

That size?!
You do not use a web-server to do that (time-limit is just one of the cones of that), you need a command-line/exe/desktop application to handle such sizes.
In php it wouldn't be too hard to write such a thing using the php-cli, and it should be done that way.
Bottom line: no web tool should do that.