mkokes/migration.md

## migration.md

      
    Raw
  

              migration.md
            
          
    Migration Strategy

Why not just use the wordpress import/export plugin?

Recently I had lovely experience of moving several large wordpress installations into a single multisite installation. Even with tweaking php/server timeout settings beyond anything safe or realistic my servers were either completely choking when doing an import/export or the process was unbearably slow to the point it just wasn't feasible. I figured rather than relying on the wordpress import/export plugin & php it would probably be orders of magnitude faster to do it manually using some SQL queuries and a little bit of rsync to get everything moved over.
Intro

This document describes the steps taken to migrate content from a wordpress standalone site into a multisite installation. In this document the multisite installation will be referred to as site A and the standalone site will be referred to as site B. This assumes that themes and plugins have been manually copied from the source server to their destination, a password reset of your user account is going to be ok and that your environment is such that you're able to use rsync to move files from site A to site B. So again...
Multisite = Site A
Standalone = Site B
For the most part everything just moves right over, the tricky part is dealing with the user database. User accounts from the standalone need to be merged into the multisites user repository if they don't already exist there and then the posts table needs to have it's author IDs updated since the user merge we just performed most likely resulted in your user accounts having new IDs assigned to them
Create a new site in the multisite installation

Name it accordingly, this is mainly to get a site ID, this id # can be found by going to sites > all sites and hovering over your site and looking at the id that appears in the address bar at the bottom of the screen
Copy all media assets from source server to destination server

Since we'll be using queries to move our DB data we'll still need to move all of our media files and assets over, the easiest way would be using rsync, but if that's not an options you could also use ftp
sudo rsync -rahz --progress --ignore-existing username@siteB:/SiteBPath/wp-content/uploads/ username@siteA:/SiteAPath/wp-content/uploads/sites/##/

Assign ownership (this assumes ubuntu / www-data being the webservers account)

sudo chown -R www-data:www-data /path/wp-content/

Create a new database to be used for staging

We will use this database to stage the data so we don't have to mess with the multisite or standalone environments in the case that they are currently being used in a live production environment
Import tables into staging database from site B and update prefix with blog ID i.e. wp_##_

At minimum you'll want to bring the following tables into your staging DB, there may be more i.e. you want to bring data over from comments, gravity forms or another plugin, but these should be the minimum.

wp_postmeta
wp_posts
wp_terms
wp_term_relationships
wp_term_taxonomy
wp_users

After the tables are imported into your staging environment rename them using your site ID in place of the ## below

wp_##_postmeta
wp_##_posts
wp_##_terms
wp_##_term_relationships
wp_##_term_taxonomy
wp_##_users

Import users table from multisite install into the staging DB

Compare wp_users table, if the username is not set in the multisite table insert it

INSERT INTO wp_users (user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name)
SELECT b.user_login, b.user_pass, b.user_nicename, b.user_email, b.user_url, b.user_registered, b.user_activation_key, b.user_status, b.display_name
FROM wp_##_users b
LEFT OUTER JOIN `wp_users` a ON b.user_login = a.user_login
WHERE a.user_login is null

Update the post_author in wp_##_posts with the new author id.

To do this we will join Site B's users and posts table via the user id and then compare it to Site A's user table via the user name and update the posts table's author ID with the new user ID from Site A
Use this query to view what the IDs look like before we change them
SELECT s.user_login AS 'login', p.post_author as 'authorID', m.ID as 'multisiteID', s.ID as 'standaloneID'
FROM wp_##_posts p
LEFT OUTER JOIN `wp_##_users` s ON s.ID = p.post_author
LEFT OUTER JOIN `wp_users` m ON m.user_login = s.user_login
WHERE p.post_author <> 0 AND s.user_login IS not null
ORDER BY login

This query will update your posts table so that the author IDs coincide with the multisite user table
UPDATE wp_##_posts p
LEFT OUTER JOIN `wp_##_users` s ON s.ID = p.post_author
LEFT OUTER JOIN `wp_users` m ON m.user_login = s.user_login
SET p.post_author =  m.ID
WHERE p.post_author =  s.ID

Verify
SELECT s.user_login AS 'login', p.post_author as 'authorID', m.ID as 'multisiteID', s.ID as 'standaloneID'
FROM wp_##_posts p
LEFT OUTER JOIN `wp_users` m ON m.ID = p.post_author
LEFT OUTER JOIN `wp_##_users` s ON m.user_login = s.user_login
WHERE p.post_author <> 0 AND s.user_login IS not null
ORDER BY login

Update urls within posts and postmeta with the new wp-content/uploads path

UPDATE wp_##_posts SET post_content = REPLACE (
post_content,
'/wp-content/uploads/2',
'/wp-content/uploads/sites/##/2');

UPDATE wp_##_postmeta SET meta_value = REPLACE (
meta_value,
'/wp-content/uploads/2',
'/wp-content/uploads/sites/##/2');