Moving to new host - very large table

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Anti-Spam Guide
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Post Reply
azzurri
Registered User
Posts: 899
Joined: Fri Jul 21, 2006 7:48 pm

Moving to new host - very large table

Post by azzurri » Mon Jan 15, 2018 7:33 am

Hi!

Im moving to a new host and upload limit in phpmyadmin is 200 mb. My database is around 500 mb (300 mb when exported in sql format). So I will have to export and import separate tables. But one table, phpbb_search_wordmatch, is 250 mb alone which is too large. Is it possible for me to empty this table in phpmyadmin and then rebuild the search wordmatch in phpbb admin panel once I have moved the site?

Or is there any better approach?

Thank you

User avatar
Crizzo
Translations & International Support Teams Manager
Translations & International Support Teams Manager
Posts: 742
Joined: Thu Apr 23, 2009 1:20 pm
Location: Germany
Name: Christian
Contact:

Re: Moving to new host - very large table

Post by Crizzo » Mon Jan 15, 2018 7:37 am

Hi,

You don't need the content of these two search tables.

You can just import the structure and the recreate the search index with the ACP.

Or you use something like mysqldumper to import a database without this phpmyadmin limit.

Regards
My extensions for phpBB: crizzo.de
German phpBB Support at www.phpbb.de

User avatar
Lumpy Burgertushie
Registered User
Posts: 64253
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Moving to new host - very large table

Post by Lumpy Burgertushie » Mon Jan 15, 2018 7:38 am

yes, that is the best way to do it.
empty all three search tables before you make your backup of the database.

then, you should not have a problem with the import.

you can also use bigdump Knowledge Base - Using Bigdump to restore large database dumps

robert
I am available for custom work on a donation basis. Please send me a PM with your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

azzurri
Registered User
Posts: 899
Joined: Fri Jul 21, 2006 7:48 pm

Re: Moving to new host - very large table

Post by azzurri » Mon Jan 15, 2018 7:48 am

Perfect! Thank you so much guys!

User avatar
warmweer
Registered User
Posts: 963
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Moving to new host - very large table

Post by warmweer » Mon Jan 15, 2018 8:55 am

azzurri wrote:
Mon Jan 15, 2018 7:33 am
Im moving to a new host and upload limit in phpmyadmin is 200 mb. My database is around 500 mb (300 mb when exported in sql format). So I will have to export and import separate tables.
...
Emptying the search tables can already cut the size of the database in half if you're lucky.
The next largest table is probably the posts_table which you could export seperately. If even that is too large, you could export it in seperate parts (e.g. part 1 = drop and create statement + records 1 to 50.000, part 2 = records 50.001 to the end).
A bug is a feature that hasn't made it to the manual (yet)

User avatar
HiFiKabin
Community Team Member
Community Team Member
Posts: 2766
Joined: Wed May 14, 2014 9:10 am
Location: Swearing at the PC, UK
Name: James
Contact:

Re: Moving to new host - very large table

Post by HiFiKabin » Mon Jan 15, 2018 1:20 pm

Exporting all of the database tables in one is not a problem, but exporting them separately is never a good idea as you may end up with problems that will never be solved. By all means drop the content of the search table as that can be rebuilt after the transfer.

Export the database as one from the ACP, then you can use BigDump to import the whole database to your new host.

User avatar
warmweer
Registered User
Posts: 963
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Moving to new host - very large table

Post by warmweer » Tue Jan 16, 2018 9:25 am

HiFiKabin wrote:
Mon Jan 15, 2018 1:20 pm
Exporting all of the database tables in one is not a problem, but exporting them separately is never a good idea as you may end up with problems that will never be solved. By all means drop the content of the search table as that can be rebuilt after the transfer.

Export the database as one from the ACP, then you can use BigDump to import the whole database to your new host.
Quite a few hosts have size limit on uploading and downloading (mine is 200MB but trying to make a backupfile near that limit, times out regularly.)
Exporting tables separately does require (at least) making sure no forum actions take place (writing, deleting) I always set the board offline in that case and (just in case) redirect users (via htaccess) to a parallel site (also offline so all they know is that it's temporarily offline) .
A bug is a feature that hasn't made it to the manual (yet)

Post Reply

Return to “[3.1.x] Support Forum”

Who is online

Users browsing this forum: No registered users and 16 guests

cron