Early this week we had a pretty big migration for a school we work with to move both old school faculty web spaces as well as breaking out individual sites from a WordPress Multsite (WPMS) blog into their own cPanel account. The first bit of this, moving files from the old school DIY sites was pretty straightforward. Tim scripted a sync between the two servers using rsync that moved the files of two hundred accounts cleanly between the two servers.
The second part of the migration, however, was a bit more labor intense. For this piece we needed to move individual WordPress blogs from a WPMS setup into their own cPanel account. Not nothing. So, we tried the plugin Duplicator Pro (with the WPMS addon) but that was not making things any easier at all in the WPMS context. We had to abandon that avenue, and look at what it would look like to do this manually for 100 sites. Tim and I did one together, and wee got the pieces down:
- identify blog ID and user (or users) ID for individual site to be moved on WPMS instance
- Use SequelPro to download that site IDs tables as well as wp_user and wp_user meta tables from WPMS database
- Re-write site site ID in SQL file downloaded from wp_ID_ to wp_
- On the cPanel side, we need to make sure the user had an account (something we scripted pretty easily using cPanel’s API)
- Create a WordPress site on the account we are migrating
- Drop all tables in PHPMyAdmin
- Import downloaded SQL file through PHPMyAdmin in cPanel
- Delete all users from wp_user and wp_usermeta save the one associated with that site on WPMS
- Run commands to update URL across site
- Run commands to change the uploads path from WPMS to one-off WP
Those steps effectively export and import database tables and user info from WPMS to the individual cPanel instance and make sure all is good in the database. The last bit was to make sure we had all files, plugins, and themes. To do this we downloaded (using rsync again0 all the WPMS files locally (this sped up the process enormously):
- Rsync files from remote WPMS instance locally
- rysnc uploaded files for the site we are migrating from blogs.dir/blog_ID/files/ to public_html/wp-content/uploads/
- rysnc plugins and themes
- fix permissions across entire account once this is all done
As you can see, this is a laundry list and it took some serious time. Doing this Tuesday morning I was averaging about two sites an hour. The method was sound, and things were moving cleanly, but timewise it was insane. Once Tim woke up and jumped in, he streamlined the process immensely by rsyncing WPMS instance locally and coming up with the commands that ensure the site and user IDs in the database are fixed and files synced seamlessly. The time to move a site went from 30 minutes to anywhere between 5-10, which allowed us to migrate the 100 sites in question on schedule. It is a thing of beauty to witness Tim streamline a process like this and make things run like a Swiss watch.
So, the rest of this post will be specific documentation for this process because I will be using the same method Tim perfected for this migration to finally break this blog free from its tyrannical WPMS beginnings into an individual WP site, only to re-subjugate it soon after 🙂
The information you need to get started is the blog_id and user_id for the site you will be migrating from the WPMS setup into a stand-alone site on cPanel. In the the WPMS you can go to the Network Admin and search in Sites and then Users to find the IDs in question. Just hover over the Edit button to see the ID in the browser as illustrated below.
Keep in mind often the site ID and the user ID are different, but they can also be the same. This screwed me up early on, so just a heads up.
Once that is done you are going to download the appropriate tables from the WPMS instance, along with the wp_user and wp_usermeta tables. I used Sequel Pro for this, and it is quite the tool for this stuff (although you can do it through PHPMyAdmin in cPanel):
The exporting dialog box is nice cause it shows you what tables are being downloaded.After this, you will have a downloaded SQL that you need to run one find and replace command on before you import it to your WordPress shell on cPanel. Blog tables in WPMS are uniquely identified in the database with an ID, such as wp_223_, but given this will be a stand alone site now you need to change all instances of wp_223_ to wp_.
After that, you dump all the database files of the WordPress instance you installed on your cPanel account, and then import this SQL files via PHPMyAdmin:
Now, if you remember, we grabbed the entire wp_user and wp_usermeta tables from the WPMS, which could be hundreds (or even thousands) of users. But in this instance we only need one, and Tim shared a slick SQL command that you can run to delete all users but the one you want. The user ID in this instance was 221, so the following code deletes all user info save that for ID 221:
DELETE FROM `wp_users` WHERE `ID`!=221; DELETE FROM `wp_usermeta` WHERE `user_id`!=221;
Noice the user ID is different from the site ID:
Now, all the data is moved over and the user can login with the original username and password. The only things left are to transfer files, clean up URLs in database, change uploads path, and fix permissions. This is where Tim’s playing with WordPress command line voodoo and file sync shaved 15-20 minutes off the migration process.
So, here are the codes for cleaning up the database and file path for this particular site:
wp --allow-root search-replace 'faculty.unlv.edu/wpmu/gill' 'gill.faculty.unlv.edu' wp --allow-root search-replace 'blogs.dir/269/files' 'uploads' wp --allow-root search-replace 'gill.faculty.unlv.edu/files' 'gill.faculty.unlv.edu/wp-content/uploads'
OK, so using WordPress’s command line feature, the above 3 commands re-write all instances of the old URL faculty.unlv.edu/wpmu/gill to gill.faculty.unlv.edu. The second replaces the WPMS file path (blogs.dir/269/files) with uploads, which is the default for a stand alone WP. Finally, the third command replaces any existing instances of the masked path for files in the WPMS (gill.faculty.unlv.edu/files) with the new path (gill.faculty.unlv.edu/wp-content/uploads). That’s it for cleaning up URLs.
Now we have to sync uploaded files, plugins, and themes. This was another huge time saver because rsync is so much faster and cleaner than FTP. So, what we had to do here is download all the WPMS files locally. This is the command I used for that, which was grabbing all files from the wpmu folder:
rsync -avz [email protected]:/www/html/wpmu ~/Desktop
After that, we can sync the appropriate files directly using the following commands:
rsync -avz blogs.dir/223/files/ [email protected]:/home/gill/public_html/wp-content/uploads/ rsync -avz plugins/ [email protected]:/home/gill/public_html/wp-content/plugins/ rsync -avz themes/ [email protected]:/home/gill/public_html/wp-content/themes/
Notice that the first command is specific to the site ID I’m working with, namely 223. I had to change this for each new site I migrated. The other two commands are syncing all the themes and plugins. These transfers literally take seconds, which changes everything in terms of waiting.
After that, you need to run the fixperms.sh script for the cpanel account, and you are done. I won’t say easy, necessarily, but see how all the pieces work make me think this could be scripted more seamlessly if we were to have schools that wanted to migrate their WPMS instances to a Domain of One’s Own instance. Anyway, this one will be a reference going forward, and further testament to the fact that Tim Owens is the best damn thing that ever happened to the bava!
Pingback: Changing URLs in WordPress Database via Command Line | bavatuesdays