Results 1 to 24 of 24
  1. #1
    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!

  2. #2
    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.

  3. #3
    Join Date
    Jun 2004
    Posts
    2,853
    Wow 1.5gigs for a forum DB?

    What is WHT's database size ?

  4. #4
    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.

  5. #5
    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.

  6. #6
    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

  7. #7
    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)...

  8. #8
    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

  9. #9
    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.

  10. #10
    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

  11. #11
    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.

  12. #12
    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.

  13. #13
    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.

  14. #14
    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.

  15. #15
    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

  16. #16
    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.

  17. #17
    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.
    Go find another host ...
    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.

  18. #18
    Join Date
    Oct 2001
    Posts
    348
    Believe me, I want to, but I need my backups from my host, and they wont answer emails.

  19. #19
    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

  20. #20
    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.

  21. #21
    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.
    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.

  22. #22
    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.
    I only use phpmyadmin non-critical information.

    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.

  23. #23
    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?

  24. #24
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •