Transferring a large phpbb2 database to new server

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Transferring a large phpbb2 database to new server

Post by MrGibbonz » Tue Nov 11, 2008 12:45 am

Hi,

I did a search but couldn't seem to find a solution for big database transfers specifically. :oops:

My database is approximately 1gb in size. I was wondering how I should go about uploading a large phpbb2 database like this to my new server? I'm aware the procedure changes when the database grows to a certain size due to timeout issues.

Hope someone can help and thank-you for your time.
Last edited by ric323 on Fri Nov 14, 2008 11:02 pm, edited 1 time in total.
Reason: Topic icon changed

naive
Registered User
Posts: 43
Joined: Sun Apr 15, 2007 3:32 pm

Re: Transferring a large phpbb2 database to new server

Post by naive » Tue Nov 11, 2008 1:19 am

It would be easier to have linux server shell access to transfer the large database.

My database was not that large, about 700M.

1. I converted phpBB2 to phpBB3 at my old server. The database of old server for phpBB3 is phpbb3oldserver.
2. I dumped the phpbb3oldserver database to phpbb3oldserver.sql using mysqldump.
3. I set up a new phpBB3 at a new server with the brand new database phpbb3newserver.
4. I overwrote the phpbb3newserver database with phpbb3oldserver.sql using the mysql command.
5. Of course you have to update your folders like files and images etc.

Psersonal experience just for your reference.

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 » Tue Nov 11, 2008 1:33 am

MrGibbonz wrote:Hi,

I did a search but couldn't seem to find a solution for big database transfers specifically. :oops:

My database is approximately 1gb in size. I was wondering how I should go about uploading a large phpbb2 database like this to my new server? I'm aware the procedure changes when the database grows to a certain size due to timeout issues.

Hope someone can help and thank-you for your time.
You didn't search in the Knowledge Base

Knowledge Base - How to move a phpBB 2.0.x board to a new server
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

Slackervaara
Registered User
Posts: 195
Joined: Thu Feb 28, 2008 7:46 am

Re: Transferring a large phpbb2 database to new server

Post by Slackervaara » Tue Nov 11, 2008 5:29 am

Take a look at MySQLDumper, but it will take pretty long time to backup and restore such a big database with it. A restore of a 140 MB database took 1 hour for me.

http://www.mysqldumper.de/en/

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz » Wed Nov 12, 2008 2:01 pm

ric323 wrote: You didn't search in the Knowledge Base
My apologies for not looking there. :?

I have followed the procedure of the very helpful guide you linked me to, but for some reason bigdump seems to freeze when there is only a few percent of the dump remaining.

I have tried twice now to dump the database but whenever it comes to the final percent it just stops and nothing happens. If I close the window and return to it, it appears completely white and the bigdump page vanishes. I have waited up to an hour in the hope it may resume, but nothing happens!

Heres a screenshot of the bigdump screen when it froze during my last attempt...
Image

I'm hoping someone can help me out as i'm not sure what else to do! :(

Thanks for your time.

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

Re: Transferring a large phpbb2 database to new server

Post by Lumpy Burgertushie » Wed Nov 12, 2008 4:21 pm

open the bigdump.php file, find this:

Code: Select all

$filename         = '';     // Specify the dump filename to suppress the file selection dialog
$csv_insert_table = '';     // Destination table for CSV files
$ajax             = true;   // AJAX mode: import will be done without refreshing the website
$linespersession  = 3000;   // Lines to be executed per one import session
$delaypersession  = 0;      // You can specify a sleep time in milliseconds after each session
                     
 
change the delaypersession number to something like 300

then try again.

I have not used bigdump for that size of a database so I don't know if the size has any affect. If so, then you may have to split it up with some type of file splitter program and restore it in chunks.

Also, the best way is to get your host to do it. As long as the backup file is on your server somewhere, then can do it very easily and quickly for you .


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz » Wed Nov 12, 2008 6:25 pm

Thanks for the reply lumpy, i did try messing around with the configuration but that didn't fix the issue. In the end it seems that internet explorer was causing the fault as I tried using bigdump in firefox and it seemed to work ok.

Only problem now is that a few tables seem to be missing once the database is upped.

To be specific, its the following tables that are being left out when the database is uploaded to the new server...

phpbb_sessions
phpbb_sessions_keys
phpbb_shout
phpbb_shoutbox
phpbb_shoutbox_sessions
phpbb_smilies
phpbb_themes
phpbb_themes_name
phpbb_topics
phpbb_topics_watch
phpbb_users
phpbb_user_group
phpbb_vote_desc
phpbb_vote_results
phpbb_vote_voters
phpbb_words

One problem gets resolved and another one follows. Anyone have any ideas? :(

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz » Wed Nov 12, 2008 8:17 pm

Just to add.... Everytime I export the database from phpmyadmin the file size of the database once download keeps varying. I've downloaded it about 5 times now and each time the file size of the database is different.

I'm assuming the tables go missing during the export process, but I have no clue how I can fix this. :cry:

Im transferring the database from a dedicated hosting plan to my newly acquired dedicated server. I have access to web host manager if that might be of any additional use.

Thanks for your time.

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

Re: Transferring a large phpbb2 database to new server

Post by Lumpy Burgertushie » Wed Nov 12, 2008 8:50 pm

are you gzipping the database when you export it?

it is apparently timing out with the search tables.

try doing the export but instead of selecting all the tables, leave out the three search tables and the two sessions tables.
They are going to be at least half of the database size if not 2/3 of it.
You will have to recreate those tables later and to get the data back;
You can rebuild the search tables later and the sessions tables will just start over and everyone will just have to log in the next time they visit.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz » Wed Nov 12, 2008 9:27 pm

Lumpy Burgertushie wrote:are you gzipping the database when you export it?
No i'm downloading it in .sql format as shown in the screenshot in the guide.
Lumpy Burgertushie wrote: You will have to recreate those tables later and to get the data back;
You can rebuild the search tables later and the sessions tables will just start over and everyone will just have to log in the next time they visit.
I'll try what you suggested now but which sessions tables should I leave out as I have four of them? I'm not sure what search tables you mean either as i have three of those. Heres all the tables in the database...

phpbb_admin_logs
phpbb_auth_access
phpbb_banlist
phpbb_categories
phpbb_config
phpbb_confirm
phpbb_disallow
phpbb_easymod
phpbb_easymod_processed_files
phpbb_forums
phpbb_forum_prune
phpbb_groups
phpbb_ina_ban
phpbb_ina_categories
phpbb_ina_challenge_tracker
phpbb_ina_challenge_users
phpbb_ina_chat
phpbb_ina_cheat_fix
phpbb_ina_data
phpbb_ina_favorites
phpbb_ina_gamble
phpbb_ina_gamble_in_progress
phpbb_ina_games
phpbb_ina_hall_of_fame
phpbb_ina_last_game_played
phpbb_ina_rating_votes
phpbb_ina_scores
phpbb_ina_sessions
phpbb_ina_top_scores
phpbb_ina_trophy_comments
phpbb_logs
phpbb_logs_config
phpbb_posts
phpbb_posts_text
phpbb_privmsgs
phpbb_privmsgs_text
phpbb_ranks
phpbb_search_results
phpbb_search_wordlist
phpbb_search_wordmatch
phpbb_sessions
phpbb_sessions_keys
phpbb_shout
phpbb_shoutbox
phpbb_shoutbox_sessions
phpbb_smilies
phpbb_themes
phpbb_themes_name
phpbb_topics
phpbb_topics_watch
phpbb_users
phpbb_user_group
phpbb_vote_desc
phpbb_vote_results
phpbb_vote_voters
phpbb_words
Last edited by MrGibbonz on Wed Nov 12, 2008 9:37 pm, edited 2 times in total.

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 » Wed Nov 12, 2008 9:30 pm

The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz » Wed Nov 12, 2008 11:44 pm

I've left the following tables out of the export and am downloading now...

phpbb_search_results
phpbb_search_wordlist
phpbb_search_wordmatch
phpbb_sessions
phpbb_sessions_keys
phpbb_shoutbox_sessions

Hope these are the correct ones?

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

Re: Transferring a large phpbb2 database to new server

Post by Lumpy Burgertushie » Thu Nov 13, 2008 3:30 am

MrGibbonz wrote:I've left the following tables out of the export and am downloading now...

phpbb_search_results
phpbb_search_wordlist
phpbb_search_wordmatch
phpbb_sessions
phpbb_sessions_keys
phpbb_shoutbox_sessions

Hope these are the correct ones?
you could have left the shoutbox one, but it will be fine.
you can always put it back when you are putting the others back.

robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz » Thu Nov 13, 2008 11:40 am

I dumped the database excluding those tables on my new server. The database shows up in phpmyadmin with all tables present, but when I compare the file sizes of each table with the sizes of the tbales from the original database on my shared hosting, the file sizes of most of the tables differ.

For example the table phpbb_words differs greatly from the original database to the transferred database on my new server.

Original Table Space usage
Data 10,608 B
Index 2,048 B
Overhead 8,364 B
Effective 4,292 B
Total 12,656 B

Transferred Table Space usage
Space usage Type Usage
Data 2,244 B
Index 2,048 B
Total 4,292 B

Should I proceed or is the fact that table sizes differ going to mean that data was lost again somewhere? I'm really starting to despair and don't understand what's going wrong! :cry:
Last edited by MrGibbonz on Thu Nov 13, 2008 11:46 am, edited 1 time in total.

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 » Thu Nov 13, 2008 11:43 am

Keep going, and see what happens.
Your original databse would appear to be larger if you hadn't done a cleanup on it to reclaim deleted data.
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

Locked

Return to “2.0.x Support Forum”

cron