[RESOLVED] Help with inserting HUGE data into MySql

vinny199

Hi,

I am moving a freind's phpBB forum to a new server.
What I did not realize is that he has around 100mb of data! and that some tables are around 30 bm!

even with maximum gzipped compression when exporting the table, the files are too bit, and phpMyAdmin on the new server will not post so much data at once.

i would have to break it down bit by bit, and this would take so long to do.

Ny question is:

could i possibly post the entire data by creating a php page (even if that page is 30 meg!), so i could call up the page in my browser and it will start posting data to the database?

My data is presented as below:

INSERT INTO phpbb_posts VALUES (12939, 738, 5, -1, 1111838264, '52344e25', '', 1, 0, 1, 0, NULL, 0);
INSERT INTO phpbb_posts VALUES (12940, 763, 5, 8, 1111838887, '9719964a', '', 1, 0, 1, 1, NULL, 0);
INSERT INTO phpbb_posts VALUES (12941, 738, 5, 10, 1111838897, 'd59c3467', '', 1, 0, 1, 0, NULL, 0);
INSERT INTO phpbb_posts VALUES (12942, 738, 5, -1, 1111840056, 'd58c0670', 'vaginoo', 1, 0, 1, 0, NULL, 0);
INSERT INTO phpbb_posts VALUES (12957, 775, 5, 18, 1111853697, '51d02450', '', 1, 0, 1, 0, NULL, 0);
INSERT INTO phpbb_posts VALUES (12956, 738, 5, 109, 1111853671, 'd58c1169', '', 1, 0, 1, 0, NULL, 0);
INSERT INTO phpbb_posts VALUES (12943, 738, 5, 101, 1111840207, 'd58c1649', '', 1, 0, 1, 0, NULL, 0);

etc...

Any idea how i could do this?

Many thanks,

vinny

bjwilke

store the SQL-dump on the server, modify the following snippet to work for you:

system("/usr/bin/mysql -uUSERNAME -pPASSWORD -hHOST DBNAME <DUMPFILE", $fp);
if ($fp==0) {
   echo 'Import succeeded.'
} else {
   echo 'Import failed.'
}

Place the php into the same dir as the dumpfile and run it via the browser. Theres no size limit now and if your DB should manage to import 30mb in under 30secs.

vinny199

Thanks for your reply,

i've tried with the code below, but got a "import failed." response.

Can you see anything wrong with this?

Unfortunatly, the username has a - in it, hope that's not a bother.

<?php system("/usr/bin/mysql -ustar-luck -ppokidfSd -hlocalhost DBildebosciomedia-davide <phpbb_posts_text.sql", $fp);
if ($fp==0) {
echo 'Import succeeded.';
} else {
echo 'Import failed.';
}

thanks,

Vinny

vinny199

Still can't work it out...

any other wayy I could try?

Thanks,

Vinny

bjwilke

If you have SSH/Telnet access to the DB-Server, execute the mysql-command I gave you directly on the prompt. (Best&Fastest way)

If you can connect to the DB from your local computer via a 3rd party tool, then use a non-php-based tool (like http://www.mysql.com/products/tools/administrator/) to connect and to import the data.

If neither solution works, I found another tool to try: http://www.ozerov.de/bigdump.php

Good luck.

vinny199

Thank you,

I've managed to do it!

Cheers,

Vinny