XF 1.1 Dificulties Moving Large DB to Another Server

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

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.
 
server goes away

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

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 = ?
 
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.
 
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
 
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.

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

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

Thanks Slavik, if it doesn't work out will try that.
 
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>
 
Top Bottom