[Resolved] how to import LARGE .sql file

perpetualshaun

I spent a most of my day at the office importing some .csv files to my local MySQL database. I had to look for duplicates after it was all done, and it was just much quicker to do so locally then on the web server.

I have a .sql file which I generated through PHP MyAdmin which is 17.3MB. I need to now import this file to the live MySQL database. However, when I try to do it through PHP MyAdmin, it times out on me every time, spitting out a "Page Cannot be Found" error after a couple minutes of processing time.

Does anyone out there know any tricks to get this file out there? I have NO IDEA how to interact w/ MySQL outside of the PHP MyAdmin tool.

Thanks,

Shaun

bad76

Hi,
if you can complete access to server machine, you can find where table files are stored, (on mine windows server under mysql/data/ directory) and upload it. Then just restart mysql server.

Else you can modify php.ini to support long-run script:
max_execution_time = 300000 may be enough...

Else try to download any front-end (my favourite for this kind of operation is "mysql-front"...) from mysql site.

see you

superwormy

Not usually a good idea to be copying entire MySQL tables around...

If you have a txt file with a bunch of INSERT statements in it, you can use the command line mysql tool to run those commands.

At command prompt...

myqsl -u your_username -p
( enter your password when it asks you )
use your_database_name
source /the/path/to/the/sql/file.sql

And youshould be all set. This is basically what phpMyAdmin does too, except it does it through PHP ( sloowwww ) and the form submissions usually die or timeout because the file upload is so damn big.

perpetualshaun

okay - so how do I get to the MySQL prompt? Sorry - probably seems like a pretty dumb question, but I've only used the PHP MyAdmin in the past.

n00854180t

Do you have access to the server? Or do you pay for a commercial host? If you have access to the server, just look in the mysql folder and run mysql. If you pay for a host either email them and have them do it for you or if you have an SSH account get a client such as Putty.

superwormy

Use Putty to SSH / Telnet in if you're allowed shell access ( most hosts will allow this, but sometimes you need to specifically ask them to enable it )

At the command prompt, do the stuff I typed in my last post. You don't need to be in any specific directory on nix machines, n00854180t or wahtever his name is was mistaken, and most hosts that use MySQL nad PHP are gonna be using nix, not Windows.

drawmack

why don't you ftp the file to the server then write a little php script that will read in the sql file 100 or so lines at a time and then execute those 100 or so lines into the database.

bad76

Originally posted by superwormy
Not usually a good idea to be copying entire MySQL tables around...

Some times i do this... are some counter indications? If i'm wrong, i'd like know it, thanks, i wouldn't want lost some db...

superwormy

Often times copying tables around in *nix systems can result in permissions problems, and if the tables are accessed while you're copying them it can botch things up.

The reccomended method is def. dumping and reloading to/from SQL files.

Not to mention that most web hosts aren't going to let you just go copying files around in the mysql database directory when other peoples databases are there too :-)

bad76

Then my work is safe: i copy only brand-new db, (when starting a new application...) so the table aren't accessed. And i'm my host. And with chmod/chown all work fine...

However, thanks for hint.