How to import large MySQL data dump using PHP?

[Brian Huether]

My webhost does not give me shell access. Normally, I use this sort of
command on my local machine:

c:\datac:\mysql\bin\mysql -uroot -pxxxxx db < file.sql

My webhost gives me PhpMyAdmin access, but I can never use that successfully
with large files. The sql file I need to import is 15MB.

thanks,

brian

[pwiegers]

Brian Huether schreef:

My webhost does not give me shell access. Normally, I use this sort of
command on my local machine:

c:\datac:\mysql\bin\mysql -uroot -pxxxxx db < file.sql

My webhost gives me PhpMyAdmin access, but I can never use that successfully
with large files. The sql file I need to import is 15MB.

This is a tricky one. I tried it myself, and only succeded by splitting
the large dump by hand, using a editor. Then you can upload it by php
myadmin.

I used this command:

mysqldump -uuser -p database --compatible=mysql323
--set-variable=net_buffer_length=16384 totaaldump20061027

to get a format that is easily editable by hand...

It worked for me.. if you find a better solution, I'm all ears....

Paul

[BlueNosE]

if u will send me a mail, i may give u a mysql-backup-code that i
created.
else, i will tell u my functions:
-----------------------------------
first function: mysql_list_tables ($DB);
-----------------------------------
this function allowed, at least at my server. like "SHOW TABLES FROM
{$DB}", but allowed.
after it i created a SQL syntax of table creation.
-----------------------------------
second function: mysql_query ("SHOW COLUMNS FROM {$TABLE}");
-----------------------------------
to see the field list and types, youll have to use it.
just do print_r and see.
-----------------------------------
second function: mysql_query ("SELECT * FROM {$TABLE}");
-----------------------------------
i dont think that i have to explain. simply use that list with INSERT
SQL syntax..

good luck!

[BlueNosE]

if u will send me a mail, i may give u a mysql-backup-code that i
created.
else, i will tell u my functions:
-----------------------------------
first function: mysql_list_tables ($DB);
-----------------------------------
this function allowed, at least at my server. like "SHOW TABLES FROM
{$DB}", but allowed.
after it i created a SQL syntax of table creation.
-----------------------------------
second function: mysql_query ("SHOW COLUMNS FROM {$TABLE}");
-----------------------------------
to see the field list and types, youll have to use it.
just do print_r and see.
-----------------------------------
third function: mysql_query ("SELECT * FROM {$TABLE}");
-----------------------------------
i dont think that i have to explain. simply use that list with INSERT
SQL syntax..

good luck!

[Petr Vileta]

"Brian Huether" <bh********@comcastSPAM.netpíše v diskusním pøíspìvku
news:Y_******************************@comcast.com. ..

My webhost does not give me shell access. Normally, I use this sort of
command on my local machine:

c:\datac:\mysql\bin\mysql -uroot -pxxxxx db < file.sql

My webhost gives me PhpMyAdmin access, but I can never use that
successfully with large files. The sql file I need to import is 15MB.

Here is problem with php script timeout. Usually webhosting provider set
this option to few seconds only and not allow to change it inside php
script.
Workaround of your problem is to split sql file to few parts and run these
in a right order.
Best way is to change webhosting provider :-)

--

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)