Changing the Domain Name on WordPress Multi-site

I created a WordPress site for a client who needed to support both English and Español versions of their content, which involved using a plugin called MultilingualPress that creates relationships between sites for each language.

I developed the site locally on my server, and then after they created some content, migrated it to their hosting service.

For some reason, WordPress stores information about the site in various places throughout the database, so moving from one site to another is not just a simple matter of moving the content and database to the new server.

First I made a backup of the database and the content (and did an export of the content via the WordPress site for an extra level of backup). Then I imported the database to the new site, and content copied to the right place on the new server. I updated the wp_config.php file with the new database credentials, but at this point nothing would work because everything still had my original domain name on it.

So the next thing was to add a setting in the wp_config.php file for Muti-Site to use the new server name.

/* Multisite */
define( 'WP_ALLOW_MULTISITE', true );

define('MULTISITE', true);
define('SUBDOMAIN_INSTALL', false);
define('DOMAIN_CURRENT_SITE', 'www.hostname.org');
define('PATH_CURRENT_SITE', '/');
define('SITE_ID_CURRENT_SITE', 1);
define('BLOG_ID_CURRENT_SITE', 1);

define('CUSTOM_USER_TABLE', 'wp_users');
define('CUSTOM_USERMETA_TABLE', 'wp_usermeta');

Changing the ‘DOMAIN_CURRENT_SITE’ to the name of the server, gives the multisite the information it needs.

Next to complete, we have to tweak a few things in the database.

First is the table named ‘wp_site’, which needs the same information as the wp_config.php file:

-- Site
UPDATE  `newdb`.`wp_site` 
   SET  `domain` =  'www.hostname.org' 
 WHERE  `newdb`.`id` =1;

Next is the table ‘wp_blogs’, which are a list of the sites created for the multisite. This table has rows for each site, so in this case there were two that were updated:

-- Blogs
UPDATE  `newdb`.`wp_blogs` 
   SET  `domain` =  'www.hostname.org' 
 WHERE  `newdb`.`blog_id` =1;
UPDATE  `newdb`.`wp_blogs` 
   SET  `domain` =  'www.hostname.org' 
 WHERE  `wp_blogs`.`blog_id` =2;

Then each site has its own options in ‘wp_options’  which were also updated with the new hostname (both the siteurl and home options):

-- English
UPDATE  `newdb`.`wp_options` 
   SET  `option_value` =  'http://www.hostname.org/' 
 WHERE  `wp_options`.`option_name` IN ('siteurl', 'home');

-- Espanol
UPDATE  `newdb`.`wp_2_options` 
   SET  `option_value` =  'http://www.hostname.org/es/' 
 WHERE  `wp_2_options`.`option_name` IN ('siteurl', 'home');

In some cases, there are options for themes to be updated, which gets a bit more tricky. If you find an option that has the old URL for a theme/plugin. This value is stored under a key specific to the theme/plugin in the options table . For instance the All-in-One Calendar plugin stores the value for it’s theme under an option named ‘ai1ec_current_theme’.

That value is data format,  stored as type/length/value pairs. It resembles a JSON object, (formatted below to make it easier to read):

a:5: {
        s:9:"theme_dir";
        s:99:"/home/web_hostr/public_html/wp-content/plugins/all-in-one-event-calendar/public/themes-ai1ec/vortex";
        s:10:"theme_root";
        s:92:"/home/webhostr/public_html/wp-content/plugins/all-in-one-event-calendar/public/themes-ai1ec";
        s:9:"theme_url";
        s:96:"http://www.old.org/site1/wp-content/plugins/all-in-one-event-calendar/public/themes-ai1ec/vortex";
        s:10:"stylesheet";
        s:6:"vortex";
        s:6:"legacy";
        b:0;
    }

The only trick to this is  make sure you change the length to match, so the update in this case would change from “old” to “hostname”, adding 5 characters, making the update be (this is per site in the multisite):

-- Note theme options are the trickiest, requiring updating the JSON with the value and making the new length match the value
-- In this case, changing from 96 to 101 for the change to 'hostname'
UPDATE  `newdb`.`wp_3_options` 
   SET  `option_value` = 'a:5:{s:9:"theme_dir";s:99:"/home/webhostr/public_html/wp-content/plugins/all-in-one-event-calendar/public/themes-ai1ec/vortex";s:10:"theme_root";s:92:"/home/webhostr/public_html/wp-content/plugins/all-in-one-event-calendar/public/themes-ai1ec";s:9:"theme_url";s:112:"http://www.hostname.org/site1/wp-content/plugins/all-in-one-event-calendar/public/themes-ai1ec/vortex";s:10:"stylesheet";s:6:"vortex";s:6:"legacy";b:0;}' 
 WHERE  `wp_3_options`.`option_name` = 'ai1ec_current_theme';

After updating the data  (and committed to the DB), it’s a good idea to run a final query to see if the old name exists on any of the options for each site.

It is possible that other content will remain that is still pointing at the old host, since there are other tables that reference content in a non-relative way, but this will be pretty close to what you need.