Migrate/Repair CiviCRM to new server

This is simply a set of personal notes based on the instructions at https://docs.civicrm.org/sysadmin/en/latest/misc/switch-servers/. I’ve trimmed it down to a bare bones walk-though for my WordPress situation and to include some solutions for ‘gotchas’ I ran into as I went. It assumes you’re pretty familiar with various command line tools and the Linux CLI in general. There are probably ‘better’ or more efficient ways to do this. I was trying to be very methodical and give myself as many recovery points as possible to avoid having to start over from the beginning.

1. Create two new databases. One for WP and a separate one for CiviCRM. I used the same user for both, but it might be better to use different users. You don’t have to separate your CMS and CRM databases, but I am increasingly of the opinion that the minor inconvenience of separating them at the start makes for easier maintenance down the road, especially if/when you have to move or attempt repair.
2. Set up the new WordPress server. In this case I used a new subdomain, but that may not be desirable. If re-using a subdomain, you’ll have to play some more games with hosts files and maybe ssl certificates to smooth the process, or take the old one offline to avoid conflicts. Definitely solvable, but outside the scope of this document.
   1. create the web server directory and download the latest WordPress code. I used wp-cli to download the latest en-CA version: ‘wp core download --locale=en_CA‘
   2. create the appropriate vhosts file in /etc/apache/sites-available and symlink to sites-enabled.
   3. Run the WordPress web install to get the DB credentials set or do it manually in wp-config.php.
   4. use ‘certbot --apache -d site.name‘ to get ssl running. Or you can copy certificates, keys and manually sort out your vhosts files if you are keeping the same domain.
   5. You may wish to use a WP plugin like ‘Customizer import and export’ to migrate some look and feel items from the old site to the new one.
3. Setup CiviCRM on the new server.
   1. Download the CiviCRM plugin for WordPress. Do not simply use the latest CiviCRM, download the version that matches your existing database. E.g. ‘wget https://download.civicrm.org/civicrm-VERSION-wordpress.zip‘
   2. Optionally, download the matching localisation files if your CiviCRM install is multilingual. 'wget https://download.civicrm.org/civicrm-VERSION-l10n.tar.gz'.
   3. Unzip civicrm-VERSION-wordpress.zip to the WP plugins directory (and localisation files in plugins/civicrm)
   4. Activate CiviCRM from the WP plugins page.
   5. When answering the questions on the CiviCRM setup, read carefully and provide the database credentials for the CiviCRM database to ensure it doesn’t get mixed with WP.
   6. Review the settings from the old site, but DO NOT migrate the civicrm.settings.php file. You may need to transfer a value or two, but don’t bring the whole thing over. Sometimes I don’t need to copy anything.
   7. Add CiviCRM modules and extensions to match the old database.
   8. Confirm the the new site is behaving well.
4. Make code and database backups of this new server. This ensures we can get back to this ‘clean’ state if things go sideways.
5. Clean the old database to prepare for import
   1. Export the database of the old CiviCRM installation using 'mysqldump --skip-triggers' (CiviCRM will rebuild triggers, which is preferable to having to clean up triggers that don’t work because of new locations, etc.)
   2. Create a temporary database for cleaning the old database.
   3. Tweak the dumpfile you are about to import and clean. In spite of my note above and below about --skip-triggers I discovered a problem during the dump of the cleaned data below. I was getting ‘DEFINER’ errors because that was attached to some views in the dump file. Maybe these can be removed and rebuilt somehow, but I decided to do a search and replace before the import in the next step so that the old username that was listed as the DEFINER was replaced by the new username. I foolishly used vim this time, but my dumpfile was very large and it was annoying. Better to use sed next time: sed -i 's/Old_Username/NEWUSER/g' dumpfile.sql > NEWUSER_dumpfile.sql
   4. Import the dump file into your temporary database.
   5. Drop extraneous tables from the temporary database.
      1. Some will always be worth deleting:
         • civicrm_db_name.civicrm_acl_cache, civicrm_db_name.civicrm_acl_contact_cache, civicrm_db_name.civicrm_cache,
civicrm_db_name.civicrm_domain,
civicrm_db_name.civicrm_group_contact_cache
      2. Others drops will be more discretionary. I deleted all the 'log_%' tables. I also dropped tables that were clearly for modules and extensions that I had removed, but left crap in the database. I was presuming that these tables were part of my problem. However, this is a dangerous game, so be careful. Go slowly and consider each removal. Because this is a backup and not the live DB, it’s not disastrous, but could set you back several steps if you guess badly. Dropping large numbers of tables can be tedious as you can’t use a wildcard, but the following code will create a script that you can then feed back in to drop the tables. I used Adminer to do this, but it should work ok in the command-line client too.
         • SELECT CONCAT(‘DROP TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’)
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME LIKE ‘log_%’
           AND TABLE_SCHEMA = ‘civicrm_db_name‘;
6. Dump this cleaned temporary database for importing into the new site database.
7. Disable the CiviCRM plugin on the new site. The first migration I did, I didn’t do this and it turned out ok, but it makes sense.
8. Import the cleaned sql dump file into your newly setup CiviCRM database: 'mysql -u mysql_username -p civicrm_db_name < civi_dump_file_name_of_your_choice.sql'
9. Clear CiviCRM cache: 'rm -rf /var/www/civi.site/wordpress/wp-content/uploads/civicrm/templates_c/*'
10. Re-enable the CiviCRM plugin.
11. Use the following URLs in your browser to review and update directory paths and base URLs.
    See CiviCRM Menu: Administer >> System Settings >> Cleanup Caches and Update Paths
    • http://example.org/wp-admin/admin.php?page=CiviCRM&q=civicrm/admin/setting/updateConfigBackend&reset=1
    • Review paths: http://example.org/wp-admin/admin.php?page=CiviCRM&q=civicrm/admin/setting/path?reset=1
    • Rebuild menus: http://example.org/wp-admin/admin.php?page=CiviCRM&q=civicrm/menu/rebuild&reset=1
    • Rebuild database triggers: http://example.org/wp-admin/admin.php?page=CiviCRM&q=civicrm/menu/rebuild&reset=1&triggerRebuild=1