For this migration I used 4 servers. The original WAMP server (Web1), a temporary Windows SQL Server (TempSQL), a temporary LAMP server (TempLAMP) and the final LAMP server (LAMP1).

  1. Make sure the two temporary servers above are fully configured.
  2. TempLAMP needs working MYSQL, phpmyadmin and apache.
  3. TempSQL just needs SQL Server and SQLYog installed.
  4. Back up the live SQL DB (on Web1) and restore it to the temporary SQL server (TempSQL).
  5. Configure MYSQL on the temporary LAMP server (TempLAMP) to allow connections from other locations (TempSQL) and create a DB in phpmyadmin (to be used for restore). Once this is done create a user DSN on TempSQL and point to this DB. SQLYog will use this DSN to convert/restore the database. You can create the DSN via SQLYog (probably the easiest option).
  6. Use SQLYog to migrate/convert the database (from TempSQL to TempLAMP). Only migrate the tables below. In the advanced options of the migration job make sure you select ‘replace‘ data or table if a match is found and ‘in case of duplicates‘ use data from source.

Tables to Migrate

‘acl_groups’,
‘acl_options’,
‘acl_roles’,
‘acl_roles_data’,
‘acl_users’,
‘attachments’,
‘config’,
‘poll_options’,
‘poll_votes’,
‘privmsgs_folder’,
‘privmsgs_rules’,
‘privmsgs_to’,
‘banlist’,
‘categories’,
‘disallow’,
‘forum_prune’,
‘forums’,
‘groups’,
‘posts’,
‘privmsgs’,
‘ranks’,
‘smilies’,
‘topics’,
‘topics_watch’,
‘user_group’,
‘users’,
‘vote_desc’,
‘vote_results’,
‘vote_voters’,
‘words’

  1. Install PHPBB on the temporary LAMP server (set the permissions) and point (config.php) the installation to the DB restored in step 4. Test access.
  2. Migrate attachments (/files) and avatars (/images/avatars/upload). Copy the files with FTP in binary mode and make sure the permissions are correct once the copy has finished.
  3. Set up the final LAMP server (LAMP1) and install PHPBB.
    Back up the MYSQL DB on the temporary LAMP server (TempLAMP) via phpmyadmin.
  4. Restore the DB to the new Linux server and re-point config.php if required.

NOTE: I did this migration in 2012 and a few versions of PHPBB3 have been released since so the tables list might need updating.

Troubleshooting

Errors such as ‘…doesn’t have a default value [1364]‘ might come up post migration when performing certain actions on the forum (posting, new topics etc). To fix these errors refer to Doesn’t have a default value errors on phpbb.com and make sure to run the script for your version of PHPBB3. To find how to execute SQL scripts from phpmyadmin refer to Executing SQL Queries in phpMyAdmin.

Tags: