Site deployment automation

TL;DR: To support server deployment automation, the WordPress front end has been merged together with other OW sites. Unfortunately, this breaks user registration until the package maintainers fix it. I’ll have to look into a fix another day. I’m really busy these days. Existing accounts should work as usual. Please report anything else that’s broken.

Previously, the WoW site was a stand alone WordPress site served at its URL by the web server under the same domain. Now, it’s just another site that’s part of the Obsidian Worlds multi-site deployment. This required merging the stand-alone site and multi-site databases. I’m documenting the process here for prosperity since the bulk of my time was spent hunting down the information. I also did some work on the authentication between CMangos and WordPress, but I won’t go over that here.

The databases were from WordPress 4.7.5. Here’s a overview of the merge.

  1. Backup the stand-alone database
  2. Create the new site
  3. Move user IDs out of the way
  4. Import the stand-alone database into the multi-site database with table renames
  5. Apply some database fixes

Backup the database

mysqldump obsidianworlds_wow | pv | pigz > obsidianworlds_wow.sql.gz

Here, the ‘pv’ command just tracks progress and ‘pigz’ is a multi-core compressor that can substantially increase compression time.

Create the new site

Create a new site with Network Settings -> Sites. Make note of the ID in the URL. You’ll need that rename tables.

Move user data out of the way

This is where things get messy. Fortunately for me, my multi-site only has a few admins that didn’t clash with other user IDs. All I had to do was move user meta data:

update wp_usermeta set umeta_id = umeta_id+24332 where user_id = 1;

For any IDs you change, you’ll need to fix the auto-increment for:

alter table wp_usermeta AUTO_INCREMENT = 25900;

Import the database

To rename and import the database at once:

zcat obsidianworlds_wow.sql.gz | pv | sed 's/`wp_/`wp_4_/g' | mysql obsidianworlds

Replace 4 with your new site ID from above.

At this point you’ll have two tables that don’t belong: ‘wp_4_users’ and wp_4_usermeta’. Multi-site WordPress installations share authentication so these tables will never be used but they’re imported so we can merge them into the real tables.

To merge the two tables:

insert into wp_users select * from wp_4_users;
insert into wp_usermeta select * from wp_4_usermeta;

Apply database fixes

When you go to add the new users to the site, you’ll notice there are no available roles to select from. To fix this:

update wp_4_options set option_name = 'wp_4_user_roles' where option_name = 'wp_user_roles';

Next, fix up users site associations by renaming user capabilities:

update wp_usermeta set meta_key = 'wp_4_capabilities' where meta_key = 'wp_capabilities' and user_id > 1;
update wp_usermeta set meta_key = 'wp_4_user_level' where meta_key = 'wp_user_level' and user_id > 1;

For me, only user ID 1 didn’t need to be fixed up. Be careful here, as this may mess up pre-existing users in the original multi-site installation. This’ll likely be different for you.

Leave a reply