WP Engine – INN Labs

Sigh. Database migrations — often nerve-wracking, always tedious, never a task I’ve been fond of. However, as you know, they’re often necessary. For us, they come up frequently.

One of the benefits available to INN members is the option to have their Largo-powered WordPress website hosted on our platform, largoproject.org. As such, we often find we need to migrate their standalone blog to our multisite rig.

To ease the process, I wrote a single_to_multisite_migration command that’s included in our deploy tools repo.

Note: if you’re using WP Engine, our deploy tools include a bunch of helpful goodies. You can read more about them here.

Assumptions

You have mysql installed on your computer
You’re using INN’s deploy tools with your multisite project
The standalone blog uses the standard "wp_" WordPress database prefix

What it takes to migrate a standalone blog

My teammate Adam Schweigert put together this gist that describes the changes required to prepare a standalone blog’s database tables for a new home amongst other blogs in a multisite install.

The process involves renaming all of the blog’s tables from wp_[tablename] to wp_[newblogid]_[tablename]. For example, wp_posts might become wp_53_posts.

This is true for all tables except for wp_users and wp_usermeta. More on this later.

This brings up the question of where the value for new_blog_id comes from. The easiest way to get this value is to create a new blog in your multisite install. By doing this, you’re creating a skeleton of database tables that your standalone blog will fill in.

Step one: create a new blog in your multisite install

After creating a new blog, find it in your Network > Sites list. Hover the site’s link and you’ll see an url in your status bar.

The “id=53” is the part you want. Make note of the site ID as you’ll need it later.

Step two: retrieving a database dump and running the single_to_multisite_migration command

Let’s look at the signature of the single_to_multisite_migration command:

def single_to_multisite_migration(name=None, new_blog_id=None, ftp_host=None, ftp_user=None, ftp_pass=None):
    ...

The name and new_blog_id parameters are required. The name will be used when creating a database in your local mysql server. This is where we’ll load the single blog’s database dump. It doesn’t matter much what name is, but it should conform to mysql’s rules for identifiers.

The new_blog_id is the ID that you made note of earlier.

If you’re using WP Engine to host the standalone blog, deploy tools can retrieve a recent database dump for you automatically. For this to work, you’ll need to provide your FTP credentials when running single_to_multisite_migration.

Here’s an example:

$ fab single_to_multisite_migration:blogname,53,myinstallname.wpengine.com,ftpusername,ftppassword

If you’re not using WP Engine, you’ll need to get a database dump by some other means. Once you have it, place it in the root directory of your multisite project repo. The single_to_multisite_migration command expects a mysql.sql file in this location, so you may need to rename your dump file to meet this expectation.

After you have the mysql.sql dump in the root of your multisite project repo:

$ fab single_to_multisite_migration:blogname,53

Example of the output from the single_to_multisite_migration command — Example output from the single_to_multisite_migration command

Step three: wait… rejoice! Your multisite_migration.sql file is ready!

Depending how big your standalone blog’s database is, it may take a while for the command to finish.

Message indicating the single_to_multisite_migration command finished properly. — Message indicating the single_to_multisite_migration command finished properly

Step four: apply the multisite_migration.sql file to your multisite database

I leave it up to you to decide how to best to apply the migration file to your database. You may be able to import the sql using phpMyAdmin, or, if you’re using WP Engine, you might contact their lovely support staff and ask them to apply it for you. Be clear that you DO NOT want to drop all tables in your multisite database before importing the multisite_migration.sql file.

Aside from renaming the standalone blog’s tables, what does single_to_multisite_migration do?

Great question. Here’s the short list:

Finds the maximum user ID in your multisite database and uses that value to offset the ID’s of users in your standalone blog’s wp_users table so that they can be inserted into the multisite database without duplicate primary key errors.
Finds the maximum user meta ID in your multisite database and uses that value to offset the umeta_id's in your standalone blogs wp_usermeta table so that user meta can be inserted into the multisite database without duplicate primary key errors.
Retains the siteurl and home values in your multisite “skeleton” site’s wp_[new_blog_id]_options table. This means that you won’t have to (re)set your new multisite blog’s site url and home url values after applying the multisite_migration.sql file.
Looks through all of the standalone blog's posts to find "wp-content/uploads/" and replace it with "wp-content/blogs.dir/[new_blog_id]/files/" to help with migrating uploads to the multisite install.
Uses REPLACE, UPDATE and some tricky subqueries to insert or update rows. This means you can apply the multisite_migration.sql file to your multisite database and avoid duplicate wp_users and wp_usermeta entries. Helpful if you need to run several incremental migrations to get all of a blog’s content before making the official transition to the multisite install.

Migrating uploads

The other thing you'll need to do is move your standalone blog's uploads directory. The single_to_multisite_migration command doesn't do this for you. You'll have to manually move the contents of the standalone blog's "wp-content/uploads/" to the multisite install "wp-content/blogs.dir/[new_blog_id]/files/" directory.

Test your migrations thoroughly before deploying.

We’ve tested and used this method several times with great success. It works well for us.

That said, remember to thoroughly test migrations before applying them to any production environment.

Break your local development site or your staging server first and be happy about it! Then fix any mistakes and you'll be ready to apply to your production database.

One of my first tasks at INN was to familiarize myself with the tools and technology the team currently uses.

What I noticed was that our deployment process was pretty darned manual. We host a number of our WordPress sites on WP Engine and while they allow deployment via git and access via SFTP, they don’t give us shell access to the server. So our deployment process up until this point has involved an FTP client, dragging and dropping files for transfer, maybe picking and choosing parts of our code to deploy based on what we've been hacking on. This approach works, but is fragile and requires a lot more thinking than I'm typically willing to do. Remember, laziness is a virtue.

Requirements

Having a scripted deployment process means that the steps to deploy your code will be the same no matter who has the responsibility. Keeping a repo with your deployment scripts means the process is documented and can be critiqued and iterated upon.

We’re using Fabric because I am familiar with it. By all means, use Capistrano or some other deployment tool. Write a few shell scripts if that’s what you’re comfortable with. Do what you can to put the software to work for you.

A sound deployment strategy meets these criteria:

Extensibility

Want to deploy via git or sftp? Deploy a specific branch? Want to minify and compress assets before deployment? Maybe tag a commit for rollback purposes? Your deployment strategy should be extensible enough to accommodate these things and require little to no input from you. Write a script once and put it to work.

$ fab staging branch:exciting_new_feature deploy

Push button

Type the deployment command into your shell and hit enter.

$ fab staging master deploy

Rollback

If something goes wrong, issue another command to revert to a previous state and deploy.

$ fab staging rollback deploy

Fabric, git-ftp and WPEngine

Fabric is a wonderful Python library that allows you to script away application deployments and remote system administration tasks. It provides a way to wrap up what would otherwise be complex git commands/workflows in simple, easy to remember, fab commands.

Some of the things our fabfile is set up to handle include:

Use git-ftp to deploy files to WP Engine via sftp.
Check the commit hash of currently-deployed code and automatically tag it is as a rollback point.
Allow us to checkout and deploy a specific branch to a specific environment (i.e. master or stable to staging or production).

Why use git-ftp? In our experience, git deployment for WP Engine has been difficult to use and unreliable. Also, WP Engine requires an entire WordPress install as the basis for your repository, which isn’t how most of our repos are structured. Git-ftp allows us to work around these issues.

However, if we want to move to a git push-based deployment strategy or expand our toolkit to work with environments outside of WP Engine, adjusting or adding to our fabfile is trivial.

Usage

If you want to use our deployment tools with your project, you can follow the instructions in the README included in the repo. The README holds all the details regarding software prerequisites and how to install them.

If you’re already familiar with Python, Fabric and have your dev environment set up, you can find the “quick start” steps below.

These tools were meant to be added as a submodule in your project repo. This is based on the way Ryan Mark structured the Chicago Tribune’s deploy tools. To start:

$ git submodule add https://github.com/INN/deploy-tools.git tools

Then copy the files from tools/examples to the root of your project:

$ cp -Rf tools/examples/* ./

Finally, edit `fabfile.py` to use your WPEngine host, username and password:

# Environments
def production():
"""
Work on production environment
"""
env.settings = 'production'
env.hosts = [‘mywpinstall.wpengine.com’, ]
env.user = ‘mywpinstallusername’
env.password = ‘mywpinstallpassword’

After that, add and commit your changes. At this point, you should be able to deploy:

$ fab staging master deploy

If you want to perform a dry run beforehand to see exactly what files will be transferred:

$ fab staging master dry_run deploy

If you want to see a list of all available commands:

$ fab -l

Available commands:

branch                Work on any specified branch.
deploy                Deploy local copy of repository to target environment.
dry_run               Don't transfer files, just output what would happen during a real deployment.
master                Work on development branch.
path                  Specify the project's path on remote server.
production            Work on production environment
rollback              Deploy the most recent rollback point.
stable                Work on stable branch.
staging               Work on staging environment
verify_prerequisites  Checks to make sure you have curl (with ssh) and git-ftp installed, installs them if you do not.

Todos

Future plans include adding commands to handle asset minification and compression.

If you'd like to contribute, you can fork the code here.

Tag: WP Engine

How To Migrate A Standalone WordPress Blog To A Multisite Install