Table to large to export in phpMyAdmin

Started by flapper, November 16, 2005, 01:35:31 PM

Previous topic - Next topic

flapper

My server is coming to the end of its contract and i want to move my forum to a new server.
I can export all the Tables except smf_messages as its 45.7 MB, my existing sql server uses MySQL 4.0.20-standard and phpMyAdmin 2.5.7-pl1, can some one please help me.

Oldiesmann

I would recommend just doing that table in chunks:
Login to phpMyAdmin and click on the messages table in the left-hand frame.
Click on the "Export" tab.

Choose the appropriate options. If you need to export the table structure, make sure you either check the box next to "Add IF NOT EXISTS" or you un-check that box after the first time. Otherwise you'll end up getting "Table smf_messages already exists" errors and you'll have to manually edit all the sql files and remove the "create table" stuff...

You should see something like "Export {x} rows starting with record # {y}.".
Figure out how many rows you want to export at one time, using a nice even number (to keep things simple). The best way to do it is to divide the number of rows in the table by 10 and choose some nice even number in that range (example: if you've got 25,423 messages, then do 2500 rows at once).

Then, fill in the first box with your chosen number and save the resulting dump to your computer (hint: append a number to the end of the filename to help you keep track (smf_messages[1].sql.gz, etc). While this might take a little while and will result in quite a few files (the above example will result in 11 different files), it's probably the best way to do it.
Michael Eshom
Christian Metal Fans

flapper

Ive tried what you said but the zip files seem to be corrupt, is there a script that i could use to backup large individual table and split them into the right sizes.

Ed Bowers

#3
I use BigDump.php to upload large databases.  It's a free program to download (the developer does accept donations though, and to be honest, it is worth a few bucks for the trouble it's saved me in the past), and works very well.  It does like was suggested a few posts back by uploading in chunks.  It will take a few minutes to do this, but if you are like me and have a large database, and almost no knowledge of MySQL, it's the way to go. 
Proud Paratrooper of the 82nd Abn Div.

US Paratroopers
History, Camaraderie, Service

Advertisement: