1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.1 Dificulties Moving Large DB to Another Server

Discussion in 'Troubleshooting and Problems' started by yavuz, Feb 2, 2012.

  1. yavuz

    yavuz Well-Known Member

    I recently decited to change my hosting provider. Three websites that are running on the server use XenForo which databases are very large avarage 500 MB. I tried cPanel's moving script, backup restore tool, command line (ssh) even third party scripts like bigdump but at a certain point the vps server gets overwhelmed and gives me an internal server error. I've tried increasing the maximum execution time, memory but it doesn't seems to help.

    Now I'm using a script called "mysqldumper" in which I'm getting similar errors at one point when it starts to import the search_index table. Is it safe to maybe empty this table and later re-run the search index?

    My VPS server specs: 4 GB Ram, 100 GB Space and below:

    Total processors: 2
    Processor #1
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz
    Speed
    2833.803 MHz
    Cache
    8192 KB
    Processor #2
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz
    Speed
    2833.803 MHz
    Cache
    8192 KB
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

  3. yavuz

    yavuz Well-Known Member

    I manually downloaded the databases from phpmyadmin and used:

    mysql -u user -p database < /path/sql.sql

    It works for a while but after a while the server goes away and I'm getting an internal server error.
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    An internal server error when running that restore command? It should be run through a shell connection, not through a browser.

    You should use the shell for both the backup and restore. Doing either through a web browser is likely to cause problems with a database as large as yours.
     
  5. SchmitzIT

    SchmitzIT Well-Known Member

    Have you looked at mysqldumper?

    Worst case, find a host that will migrate for you. Our host (nimbushosting.co.uk) offers exactly that.
     
    yavuz likes this.
  6. yavuz

    yavuz Well-Known Member

    Jake I ran the command on putty it you mean shell connection. When I hit enter it seems to do it's thing but after a while I can't reach cPanel, my websites anymore.
     
  7. yavuz

    yavuz Well-Known Member

    Yes, as a matter of fact I'm using it right now. It imports up until search_index table. After that the server goes away.
     
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Your server may just be under load from running the backup / restore. You should wait for the command to finish.
     
  9. yavuz

    yavuz Well-Known Member

    Even If I lose all connection to cPanel and other services ?
     
  10. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    As in an error that says "MySQL server has gone away"?

    That error is caused by one of these MySQL settings being exceeded:

    wait_timeout
    max_allowed_packet


    If you don't manage your own server then you need to ask your host or server person to increase these settings.
     
  11. yavuz

    yavuz Well-Known Member

    Unfortunately I'm my server person :)

    I added and later removed those lines from my.cnf. What would be the appropriate size for a database thats larger than 800 MB ?

    wait_timeout = ?
    max_allowed_packet = ?
     
  12. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    wait_timeout default is 28800 which is plenty.

    max_allowed_packet may need to be increased though. The default is 1048576 which is sometimes not enough. Try 16777216 (16MB). With this setting it doesn't hurt to overshoot as the memory is allocated as needed.
     
    yavuz likes this.
  13. Slavik

    Slavik XenForo Moderator Staff Member

    If the search table is causing the problem, exlude that table from the dump and do it seperately

    mysqldump -u USER -pPASSWORD -h localhost --ignore-table=database_name.search_index db_name > dump.sql
     
    yavuz likes this.
  14. yavuz

    yavuz Well-Known Member

    I went with your suggestion Jake added those to my.conf and now running it on command line.

    Thanks Slavik, if it doesn't work out will try that.
     
  15. yavuz

    yavuz Well-Known Member

    The command line is still active but lost connect with cpanel and other services, internal server error:

    Code:
    <h1 class="errortype">Internal Server Error</h1>
    <p class="errormessage">Can't fork at /usr/local/cpanel/Cpanel/SafeRun/InOut.pm line 22.
    <pre> at /usr/local/cpanel/Cpanel/SafeRun/InOut.pm line 22
        Cpanel::SafeRun::InOut::inout('GLOB(0x1d58aed0)', 'IO::Handle=GLOB(0x1d5d2310)', '/usr/local/cpanel/cpanel', './frontend/x3/sql/index.html') called at cpsrvd-ssl line 6506
        main::cpHandler() called at cpsrvd-ssl line 1876
        main::dodoc_cpaneld() called at cpsrvd-ssl line 1195
        main::dodoc('HASH(0xd91bc0)') called at cpsrvd-ssl line 997
    </pre></p>
    
     
  16. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Wait for it to return. The command will either complete or it will return an error.
     
  17. SchmitzIT

    SchmitzIT Well-Known Member

    My bad. I should read more carefully.
     
  18. yavuz

    yavuz Well-Known Member

    I'm also connected through WinSCP and can see inside /var/lib/mysql/ that the database size growing.
     
  19. yavuz

    yavuz Well-Known Member

    Ok, I successfully imported one database. Running the remanining two. Thank you all for your help :)
     

Share This Page