2014/12/14

Migrating WordPress MultiSite to a New URL

Migrating WordPress MultiSite to a New URL can be done with a combo of 2 excellent free plugins; WP Migrate DBand BackWPup  the former deals with the database and the latter deals with the files.


The key thing when updating the WordPress database to reflect a new hosting URL is making sure any data serialization stays intact, data serialization involves using the URL in data and if the URL is changed it breaks the data and doesn’t display it. Certain tools like WP Migrate DB take this into account when changing the URL whereas a blanket find and replace in a text editor or a database dump may put you in deep water.


This guide is based on a subfolder WordPress multisite installation,



Installation


Install both plugins in the originating URL and Network Activate them.



Migrating the Database with the New URL


Launch WP Migrate DB via the main admin Dashboard Settings > Migrate DB


wordpress-mutisite-migration


Add in your target URL and home directory webroot path. Take note not to include the webserving protocol in the URL – start with // and also don’t leave trailing slashes in either path.


The advanced options are not required but can be efficient in certain circumstances.


Click on Migrate DB and when done it will pop a gzipped archive in your download folder.



Migrating the Data


wordpress-mutisite-file-migration


Launch BackWPup via main network admin Dashboard and create a new job, just need the files, no need for the database. Save and run now, this plugin has a host of destination options, you can just do it to a local folder and then download when complete.


wordpress-mutisite-file-migration-download



Upload to new URL Destination


Upload both archives to the home directory of the new destination. Uncompress the archives:


Extract the data



tar -xvf files-archive.tar.gz

Remove any database it may have brought with it



rm *.sql

Change files ownership to correct user



chown -R realowner:realowner *

Extract the database



gunzip databasename.sql.gz

Assuming you have a new empty database setup, import the old database



mysql new_databasename < imported_database.sql

The above steps are all command line driven as root, if you don’t have shell access, this will have to be done with phpmyadmin and a ftp app.



 Fix .htaccess and wp-config


Fix up references to the URL in both .htaccess and wp-config


Add in new database connection in wp-config


.htaccess for modern WordPress should resemble



RewriteEngine On
RewriteBase /
RewriteRule ^index.php$ - [L]

# add a trailing slash to /wp-admin
RewriteRule ^([_0-9a-zA-Z-]+/)?wp-admin$ $1wp-admin/ [R=301,L]

RewriteCond %{REQUEST_FILENAME} -f [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^ - [L]
RewriteRule ^([_0-9a-zA-Z-]+/)?(wp-(content|admin|includes).*) $2 [L]
RewriteRule ^([_0-9a-zA-Z-]+/)?(.*.php)$ $2 [L]
RewriteRule . index.php [L]

 wp-config should include



/* Multisite */
define( 'WP_ALLOW_MULTISITE', false );
define('MULTISITE', true);
define('SUBDOMAIN_INSTALL', false);
define('DOMAIN_CURRENT_SITE', 'mynewdomain.com');
define('PATH_CURRENT_SITE', '/');
define('SITE_ID_CURRENT_SITE', 1);
define('BLOG_ID_CURRENT_SITE', 1);
/* That's all, stop editing! Happy bloggin

Make sure and define ‘DOMAIN_CURRENT_SITE’ with the new URL.


That’s it.

Migrate WordPress Site from Local Development to Live Server and fix serialized data issues

There are a number of ways to migrate a local development WordPress site from a test to a live server, you can to this manually or via a plugin.



Migrating WordPress Manually


The manual way to migrate a WordPress site to a live server is in a few steps:




  • Copy the all the contents of the WordPress installation in your development webroot to your live production webroot in your server via FTP, cPanel or other transfer method.

  • Export/Dump your development MySQL database

  • Create a new MySQL database for your live environment

  • Import the development database into the blank live environment database

  • Change the URLs in the database from old to new

  • Update /wp-config.php file for new database, database username and password


If you have created your test site with a different URL then the finishing live site which is more than likely, (unless you are using Virtual Hosts and are swapping between local and remote IP addresses) you may run into issues with some data not being migrated to the live site.


This data may include WordPress widgets not showing, certain plugins or theme data is missing. The reason this data may not have made the migration trip is because it is lost as the data has been serialized in the database with the old URL and then can’t be unserialized as there is a new URL.



Serialized Data


So what is Serialized data?  Well it is data that has been written as an array in PHP and stored as a string in one field in a database as appose to numerous fields in a mysql database. This allows a developer an easy, quick and efficient set up but comes at the price of serializing and unserializing that data.


WordPress uses its own serialization functions, however the disadvantage of this approach is that the data is now not as easily transported to say a new URL as the URL is used as part of the serialization.



Temporarily Fixing Serialized Data Issues


To get you out of an immediate bind and show any missing data after a migration you can edit a function in a core WordPress file:


/public_html/wp-includes/functions.php


Comment out the function maybe_unserialize() on lines 230-234 and replace with:



function maybe_unserialize( $original ) {
if ( is_serialized( $original ) ) {
$fixed = preg_replace_callback(
'!(?<=^|;)s:(d+)(?=:"(.*?)";(?:}|a:|s:|b:|i:|o:|N;))!s',
'serialize_fix_callback',
$original );
return @unserialize( $fixed );
}
return $original;
}
function serialize_fix_callback($match) { return 's:' . strlen($match[2]); }

Obviously you will lose that when WordPress is upgraded and is not considered best practice so a permanent solution is required.



Permanent Fix for Serialized Data Issues


The better way to go out this is exporting a database which already references the new URL of the live environment and this is where WP-Migrate DB sorts out the problem. Install the plugin in your development site and export the database with the new URL and file path stated.


migrate-wordpress-database


Once this is done just import the exported database into the new live server.


Also is a script from /Search-Replace-DB which is a wizard you can use which changes the domain name whilst keeping serialized data intact.





WordPress Migration Plugins


migrate wordpress siteA couple of WordPress plugins that can deal with migrating a site from development to live server and deal with any data serialization issues are Duplicator and BackUp Buddy the former is free and the latter is a commercial plugin. One of the key benefits of BackUp Buddy is that it can migrate WordPress Multi-Site installations.


I use mostly Duplicator which I have found to be faultless in many site migrations and the plugin has been around for a while now.


You add the plugin to the the site you are migrating from and create a package from the plugin which includes all the WordPress content and the database, you upload that package to your new server and you need to have a blank MySQL database ready.


From there you need the credentials of the new database; name, username and password – after the package is uploaded go to the config file via a browser, fill in the blanks and you have a migrated site.


Here are the full Duplicator docs.

Change and Update WordPress URLS in Database When Site is Moved to new Host

After migrating a WordPress site to a new URL either live or to a production or development server, the new URL strings in the mysql database need to be changed and updated in the various mysql database tables.


This method just uses the whole mysql database rather than a WordPress export/import from within, and is best suited for a straight swap. So you would copy all the WordPress files/folders to the new destination, set the correct ownership to those files = then do the database switcheroo.



WordPress Database Switcheroo


Do a mysql database export of the old database on the old server, create a new blank database on the new server, import the old data either in phpmyadmin or mysql directly in the command line.


Make sure you have the new database selected, then run some sql updates and replacement commands on the tables notably, wp_options, wp_posts, wp_postmeta.


Use the code as below and swap in your old and new URLs, no trailing slashes. Also if necessary change the table prefix values where applicable (ie wp_ )



UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');



mysql-updates-wordpress
mysql-updates-wordpress

or via command line:



username@[~/Desktop]: mysql -u root -p databasename
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 892
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');
Query OK, 0 rows affected (0.05 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');g
Query OK, 0 rows affected (0.01 sec)
Rows matched: 686 Changed: 0 Warnings: 0

Finally update your WordPress config file to reflect the new database, “wp-config.php” should be in your web document root – change, databasename, username, password and host values:



define('DB_NAME', 'databasename');

/** MySQL database username */
define('DB_USER', 'username');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'localhost');

Now everything should link up perfectly.


djave has created a nice and easy script that takes the old and new URLs and hands you the sql code for the WordPress swap, nice!



Serialised Data


Sometimes issues may arise with a problem called serialized data which is when an array of PHP data is somewhat like encyrpted with the actual URL, so if the URL is changed the data is gone.


There are 2 brilliant tools that can handle serialized data and do a search and replace on the old and new databases for the URL and leave serialized data intact.




interconnectit


First up is a script you run via uploading it and browsing to it after migrating and importing your old database into the new – this will then make those necessary changes. Get it from here.




WP Migrate Pro


Second up is a plugin which you install on your original site and run from their doing a find replace on URL string and webroot, a new database dump is exported and thats the one you import into the new URL hosted database.

Find and replace text across a whole WordPress web site using phpMyAdmin

You can do a find and replace for text or html code on a post or page across a whole WordPress site by using the mysql database that the site stores all its data in.


First up you need to connect to the database, if you have a cPanel/Plesk style hosting typically you will have an option to connect to the database via phpmyadmin.



Connect to your WebHost


connect-to-phpmyadmin
Launch phpMyAdmin


 Select Your WordPress Database


select-wordpress-database
Select your WordPress database

If you have multiple databases and don’t know which one, check the database name in the wp-config.php file which is stored in your webroot; htdocs or public_html



Select the wp_posts table


select-wp-posts-table
Select the wp_posts table

To Search For a Text Query Only


Select SQL Tab and add in the Search Query SQL Code


input-search-string



SELECT * FROM wp_posts WHERE (post_content LIKE '%TEXT-TO-FIND-GOES-HERE%');

Click ‘Go’


search-for-text-mysql


To edit the post, click on edit and do what needs to be done – if you edit the text it will be updated in your WordPress post.



To Search and Replace For a Text String


Select SQL Tab and add in the Search and Replace SQL Code


find-and-replace-text-in-wordpress
Add in the find and replace SQL code

So here switch to the ‘SQL’ tab add in the code below and click on the ‘Go’ button.



UPDATE wp_posts SET post_content = REPLACE (  post_content,  'text to find here',  'text to replace here');

So with the SQL code you put your text between the single quotes for what you are finding and what you are replacing these are separated by the comma.



See the Results


phpmyadmin-shows-how-many-rows-are-affected
Shows you how many instances the find/replace occurred

Doing it via Command Line


This can also be easily done via the command line, you just need to start a mysql session:



mysql -u username -ppassword
use mywordpress_table;
UPDATE wp_posts SET post_content = REPLACE (  post_content,  'text to find here',  'text to replace here');

 

Doing it with a Plugin


phpMyAdmin and Command Line – fill you with dread? – never fear a plugin is always handy….