How Do We Import Large .sql Files?

[NHFTRich]

Since we have no command line access.

I've been trying to import a large mysql dump file. It's about 15 MB of text.

Using phpmyadmin, I can easily dump this from my hostpc server to my local system.

But I have not been successful in reading in this sql file.

I'm dumping the file to my local system, doing some work on it, and then trying to reload it into a different database.

I know if I could access the mysql command line I could simpy type in:
source mynewdatabase.sql and it would work as I have a local system setup to try to duplicate this issue.

My local system also has trouble importing a file of any size through phpmyadmin but has no problems with importing via the mysql command line.

Do you have a suggestion on how to import a new database of a significant size without access to SSH?

[dbmasters]

I have done it table by table on occassion, getting all the small tables in one or two files, but doing the largest tables one at a time.

[eugene]

I either split the file into smaller chunks OR write a little php script to read in the file and execute the commands.

[ryan]

Upload your file to your space and open a HelpDesk (http://helpdesk.hostpc.com) ticket. Then we can run the import for you.

[NHFTRich]

Originally posted by eugene@May 12 2005, 07:53 PM
I either split the file into smaller chunks OR write a little php script to read in the file and execute the commands.
Quoted post


Could you give me an idea of what you do in this script? do you simply execute a 'source' command?

If you prefer, feel free to email an file to me.

Thanks!

[NHFTRich]

Originally posted by ryan@May 13 2005, 04:11 AM
Upload your file to your space and open a HelpDesk (http://helpdesk.hostpc.com) ticket. Then we can run the import for you.
Quoted post



Ryan,

Thanks for the offer, but sometimes I'm doing some experimenting or debugging. I may need to do this several times in a day if I have to make some fixes. I'd prefer not to bother you with a help desk ticket.

I guess I'm happy to see that others have the same issues as I do, but on the other hand, I'm perplexed that there isn't a better way to do this. It makes me wonder how difficult doing a restore from a backup will be if I have to do it a part at a time.

In this case, I'm trying to do some developmental work and need to do some testing, so I could be uploading a file a few times, so I need an easy solution to this problem.

[NHFTRich]

Originally posted by dbmasters@May 12 2005, 03:59 PM
I have done it table by table on occassion, getting all the small tables in one or two files, but doing the largest tables one at a time.
Quoted post


Dan,

Wow, this is a lot of work! :) Do you use phpmyadmin to do this?

I started splitting out tables in separate files but decided there had to be a better way.

I also wanted to try to understand the limitations of the problem. Is it a file size issue? Is it a execution timeout issue? Is it a combination of the two?

On my local test system the command line seems to work fine, but I get the same problems trying to use phpmyadmin. So I'm inclined to think that it's a issue with the phpmyadmin script.

I really would like SSH access soon! :D

[dbmasters]

It's really not that bad. Typically, one or two tables make up the bulk of any database schema, in such scripts like forums, the posts table is huge, but everything else is small...so many times it's only 2 or 3 pieces, not really that big of a deal...and yes, I have done it via phpMyAdmin.

[eugene]

Originally posted by NHFTRich+May 13 2005, 06:41 AM--><div class='quotetop'>QUOTE(NHFTRich @ May 13 2005, 06:41 AM)</div><div class='quotemain'><!--QuoteBegin-eugene@May 12 2005, 07:53 PM
I either split the file into smaller chunks OR write a little php script to read in the file and execute the commands.
Quoted post


Could you give me an idea of what you do in this script? do you simply execute a 'source' command?

If you prefer, feel free to email an file to me.

Thanks!
Quoted post
[/b][/quote]
Steps: open DB connection
open input file
read input file, parsing statements
execute query (for each statement)

[NHFTRich]

Thanks, I would think that this is the same that phpmyadmin would do. But I'll have to dig in further to see why it's failing.

[eugene]

Does phpMyAdmin take/read in a file already on the server or does it only accept a file via upload? If the latter, then you have to worry about timeouts more than with my approach to large files.