Results 1 to 24 of 24
Thread: Big DB dump....how to do it?
-
08-03-2005, 10:11 PM #1Web Hosting Guru
- Join Date
- Oct 2001
- Posts
- 348
Big DB dump....how to do it?
Hey guys, Im really stuck here. My host says they cant get my forums to work because the DB is too large (about 1.5 gigs). They say it keeps timing out when uploading.
How do you suggest I go about getting this DB restored? I know I cant have the biggest DB out there, so how do others do it?
Ive been told to use this....anyone have experience with it?
http://www.ozerov.de/bigdump.php
Any other tips you can give me? I REALLY need to get my forums back up.
Thanks for ANY help!
-
08-03-2005, 10:22 PM #2Disabled
- Join Date
- Dec 2002
- Location
- chica go go
- Posts
- 11,876
Your database can be imported via the /usr/bin/mysql tool that is available via ssh. Simply run the following command:
mysql -u yourusername -p databasename < /path/to/your/databasedump.sql
Then enter your password, and it will be imported. It will take a pretty long time to import though.
You probably shouldn't be using a standard shared hosting account for hosting such a large forum either. A website this large is more suited for a VPS, or dedicated server.
Have a nice day.
-
08-03-2005, 10:25 PM #3Web Hosting Master
- Join Date
- Jun 2004
- Posts
- 2,853
Wow 1.5gigs for a forum DB?
What is WHT's database size ?
-
08-03-2005, 10:25 PM #4Web Hosting Master
- Join Date
- Oct 2003
- Posts
- 9,264
shibby,
Sounds like your host is is comprised of a newblet, I'd recommend seeking an alternate as well.
If they can't import a database on your behalf via Command Line: How are they going to fix the server when something breaks.
-
08-03-2005, 10:28 PM #5Web Hosting Master
- Join Date
- Jul 2002
- Posts
- 3,734
No doubt that WHT is larger than that.
If your host can't figure a way for you to get that database installed, they're either idiots or trying to get you to go elsewhere with your huge database. Mike is right. You should be looking to non-shared solutions for forum db's that size.
-
08-03-2005, 10:49 PM #6Web Hosting Master
- Join Date
- Jun 2004
- Posts
- 2,853
Well I know WHT is bigger than that, cause no one with 3million posts on their forum is going to ask how to upload a database
-
08-03-2005, 10:54 PM #7Web Hosting Master
- Join Date
- Jul 2002
- Posts
- 3,734
It sure would be a funny little skit kind of thing to go around to hosts offering ridiculous amounts of space and asking them to install the WHT database (or something of it's size)...
-
08-03-2005, 10:56 PM #8Web Hosting Master
- Join Date
- Oct 2002
- Location
- New York's Capital District
- Posts
- 2,198
We support a forum using VB that has a DB about 2GB ... 15,000 members ... 250,000+ posts and usually 700 - 1000 users on at one time on a shared server ...
It all depends on if your host knows how to manage their servers correctly and if they have enough horsepower ...Know It All Solutions Incorporated
Web Design, Web Development and Web Hosting
-
08-03-2005, 11:11 PM #9Web Hosting Master
- Join Date
- Jun 2003
- Location
- United States of America
- Posts
- 1,847
how many post and users are in this forum of yours?
Computer Steroids - Full service website development solutions since 2001.
(612)234-2768 - Locally owned and operated in the Minneapolis, Minnesota area.
-
08-03-2005, 11:13 PM #10Need Moar KAWFEE
- Join Date
- Apr 2003
- Location
- Atlanta, Jawja
- Posts
- 3,074
Wow, makes me happy that I'm running the kind of DB structure that I've got. 274.3 megs, 662 members, 528k posts.
You definitely need to follow the steps that mikeylove gives you. If you're having a hard time transferring your DB from the old server to the new one, tar/gzip it down, wget it onto the new server and then do the manual command that was given above.
It IS going to take a while for that DB to import, so go have a cuppa and relax for a bit.Doug Hazard - "retired" Web Hosting Master
* Full Stack (Web/WP) Dev for The Catholic Diocese of Richmond (160+ sites)
* Sports Photographer, Media Personality and CFB Historian
* Tech Admin for several Sports Networks/Sites
-
08-03-2005, 11:25 PM #11Web Hosting Guru
- Join Date
- Oct 2001
- Posts
- 348
See, the deal is, my host moved servers a few days ago, so my site has been down for 3 days. Theyre telling me they cant get my forums working because the DB is too large and causing a timeout.
The latest backup I have is from about a week ago...they have the latest one that was done before the move. So I dont even have the backup to do it myself...they have it, and theyre being of NO help.
I need my forums onlive so bad, and I dont know what to do anymore.
-
08-04-2005, 03:20 AM #12Web Hosting Master
- Join Date
- Mar 2003
- Location
- Duluth MN
- Posts
- 3,863
I use the program 'mysqldump' to dump large databases to .sql files.
If you want, you can forward me your mysql information and I can run the dump from one of my servers for you, tar it up, and let you download it.
[edit] I re-read the post and realized you're trying to import the databse. If you want, I can import the database for you as well. If your host can send you the backed up file that is.
If all else fails, tell the host to go into the mysql server directory, tar up your raw database folder, ftp it to the new server, and extract the raw database folder to the new mysql server directory.
-
08-04-2005, 10:19 AM #13Web Hosting Master
- Join Date
- Nov 2001
- Location
- Philadelphia, Pa
- Posts
- 948
That is one LARGE database.
But it most definitely can be done. But I would definitely be looking for a dedicated service.
-
08-04-2005, 04:48 PM #14Web Hosting Master
- Join Date
- Aug 2003
- Location
- Dallas, TX USA
- Posts
- 2,812
Well of course a 1GB+ file is going to time out when uploading via HTTP. Which is what they are probably doing.
Like people have suggested, FTP+SSH is the way to go. Using some script like bigdump probably won't work, or/and cause a load on the server.
-
08-04-2005, 05:10 PM #15Web Hosting Master
- Join Date
- Mar 2003
- Location
- Duluth MN
- Posts
- 3,863
you dont even need the FTP component. If you have the file on a linux machine in your office, go to command prompt and run:
mysql -h hostname -u username -p databasename < database.sql
-
08-04-2005, 05:15 PM #16Web Hosting Guru
- Join Date
- Oct 2001
- Posts
- 348
I dont know what to do. Ive told the host about all the suggestions you guys have given me, and still no response.
Im so screwed.
-
08-04-2005, 05:39 PM #17Web Hosting Master
- Join Date
- Jun 2001
- Location
- Denver, CO
- Posts
- 3,302
Originally posted by shibby
I dont know what to do. Ive told the host about all the suggestions you guys have given me, and still no response.
Im so screwed.Jay Sudowski // Handy Networks LLC // Co-Founder & CTO
AS30475 - Level(3), HE, Telia, XO and Cogent. Noction optimized network.
Offering Dedicated Server and Colocation Hosting from our SSAE 16 SOC 2, Type 2 Certified Data Center.
Current specials here. Check them out.
-
08-04-2005, 05:48 PM #18Web Hosting Guru
- Join Date
- Oct 2001
- Posts
- 348
Believe me, I want to, but I need my backups from my host, and they wont answer emails.
-
08-04-2005, 05:50 PM #19Web Hosting Master
- Join Date
- May 2001
- Location
- Dayton, Ohio
- Posts
- 4,977
Originally posted by shibby
See, the deal is, my host moved servers a few days ago, so my site has been down for 3 days. Theyre telling me they cant get my forums working because the DB is too large and causing a timeout.
The latest backup I have is from about a week ago...they have the latest one that was done before the move. So I dont even have the backup to do it myself...they have it, and theyre being of NO help.
I need my forums onlive so bad, and I dont know what to do anymore.
For databases over 1gb in size, normally its just easier to shut down MySQL and just move the Mysql binary files from one server to another than to mess with dumping and reading it from a text file.
They need to do their db dumps from shell instead of things like phpMyAdmin. If they cannot shut down MySQL then dumping each table into its own text dump would work better and then gzipping each table...
-Mat
-
08-04-2005, 06:40 PM #20Disabled
- Join Date
- Dec 2002
- Location
- chica go go
- Posts
- 11,876
I think the fact that they are using phpmyadmin for importing a database so huge shows that they are in fact idiots and you shouldn't rely on them to keep your website online.
-
08-10-2005, 05:41 AM #21Web Hosting Master
- Join Date
- Dec 2004
- Location
- Canada
- Posts
- 1,097
Originally posted by mikeylove
I think the fact that they are using phpmyadmin for importing a database so huge shows that they are in fact idiots and you shouldn't rely on them to keep your website online.
I remember seeing a PHP script around somewhere that would split the file into managable pieces that it would then import...but I don't know what it was called, and it's really a last-ditch thing. If you don't have a shell account, ask for one and do it yourself..but only if you can't switch to a more knowledgable host.
-
08-10-2005, 02:24 PM #22Web Hosting Master
- Join Date
- Nov 2001
- Location
- Philadelphia, Pa
- Posts
- 948
Originally posted by error404
The fact that they're using phpMyAdmin for importing a DB *at all* shows that they're idiots. It's fine and dandy to let your clients do so, and save yourself a bit of trouble when it works, but it's far too flaky and not suitable for any reasonably sized DB that anyone should want to use it as the default choice...
I remember seeing a PHP script around somewhere that would split the file into managable pieces that it would then import...but I don't know what it was called, and it's really a last-ditch thing. If you don't have a shell account, ask for one and do it yourself..but only if you can't switch to a more knowledgable host.
for example, I do some work with basketball boxscores. If it times out, not a big deal. In that regard, phpmyadmin is a great timesaver.
But yeah, if you use phpMyadmin, expect it to be a bit flaky.
-
08-10-2005, 08:03 PM #23Web Hosting Guru
- Join Date
- Nov 2003
- Location
- tampa, fl
- Posts
- 326
I really don't know if it would work, but, you might want to give MySQL Administrator a shot if you have to do it yourself. Especially if it's between that and phpmyadmin, seriously, it's a **** load faster. You will need to have remote access to the database. You might want to try spliting up the file in 100MB increments (just to test to see if it can handle that load).
I agree with error404 about them using phpmyadmin, jeez. Find yourself a new host. Did they honestly think that they would be able to import a db over a gig through phpmyadmin?
-
08-10-2005, 08:09 PM #24Web Hosting Guru
- Join Date
- Nov 2003
- Location
- tampa, fl
- Posts
- 326
Also, if it is a phpbb forum, you can remove the search words and match list from the sql file and regenerate it using a tool. Should decrease the sql file by a lot.
http://www.phpbbhacks.com/viewhack.php?id=434
Worth a shot and if it doesn't work, you can always reupload the tables again separately. Good luck!