Problem with huge database

[jeffblochjr]

I am trying to move a mysql database from a site on one server to a new site on a different server. I dumped the old site's structure and imported it into a new mysql database on the new web site... but there is 700 meg of data on the old database. 26 text files make up the bulk of this 700 meg, and they must be uploaded to 26 tables on this new mysql database on the new server. When I try to do this via phpMyAdmin, it freezes up on me because it takes so long (I'm even using a cable internet connection!). What are my other options for uploading the data of these tables? I have to get this information into the new database, but it always times out. Please help! Thanks.

Jeff

[Spookster]

Maybe you could work with your old host and give them a login for your new server and have them move the database over.

[jeffblochjr]

My problem is... I have the whole structure of the database all worked out and transferred over. Now I need to move the 700+ meg of data from the old database to the new. I have these 26 data files (text documents) on my hard drive, and I click on "Insert data from a textfile into table" for the respective table. Then, I direct it to look at the text file on my hard drive, and click Submit. Then I wait about 3 or 4 minutes... and the page basically reloads itself. No confirmation or error messages... it just reloads and stops reading the file. That's it. How else can I get the data from these text files into the tables? Please help, I am stumped.

[Kiwi]

Why not write a little php script to upload the files directly? I assume each file isn't too big (c. 10-20 Mb). Even if they are too big for php (or rather for your server's set-up of php), ftp the files to your server, upload them to mySql with php then delete them.

[jeffblochjr]

I have uploaded the files via ftp already. How can I "upload them to mySql with php"? I am not very experienced with this stuff, and I really appreciate your help. Where could I get this kind of php script to do this for me?

[Kiwi]

What is the format of the files: are they intsert data queries? Or do you have to parse the files?

If you can post the first line of one of the files, and the structure of the table it will go into, we can probably help you out.

I suggest that, for testing, you make a short file -- the first ten lines of one of the files. You can kill the data when it's working, then redo it with the whole lot. Finally, I'm guessing that you only need to do this once, which means we can take a quick and dirty approach.

[BenjZ]

Don't forget that 26 Meg = about 30min with a 128k-upload internet connection...
Furthermore, maybe these files are too big for PHP

The best solution is to go to server command-line tool directly (from ssh for example).
Type: mysqldump --opt [database] > DUMP.SQL
Copy this file to the destination server an type:
mysql < DUMP.SQL
If you don't have ssh access, ask the system administrator to do so.

Other solution: il your files are on FTP, and are phpmyadmin dump files, you can try a simple script like this:


$fp = fopen('yourfile.sql','r');
$id =mysql_connect('host','user','pass');
mysql_select_db('base',$id);
while (!feof($fp))
{
$line = fgets($fp,1024);
$buffer .= $line;
if ($line[strlen($line)-1] != ';') continue;
mysql_query($buffer,$id);
$buffer='';
}

mysql_close($id);


..hope php will be fast enough to send so big data in less than default max execution time of 30s (10 on some hosting services)...

Good luck !