Quick tip: How to update url of all subsites in a WordPress Multisite with a little help from excel

Moving a WordPress Network (or multisite) is a bit more complicated than moving a single install. Part of it is because WordPress insists on saving the urls in various database tables.

We just upgraded a large multisite for a client and needed a test environment before touching the live site. Therefore I created a copy on my localhost.

After importing the database I needed to change all the urls to match my localhost instead of the production url. The network has over thousand subsites so editing manually isn’t an option so i decided to go for some kind of loop.

Looping in MySQL is much more cumbersome than a standard php loop and I’ve never bothered to learn the syntax since the whole thing just smells bad habit. Just look at it below.

That’s why i decided to use a little cheat I’d like to share with you; I’m generating the update statements with excel.

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END;

Steps to change url of all subsites in WordPress Network with sql:

1. Fetching a list of all the blog_ids from wp_blogs and copy the results.

SELECT blog_id FROM wp_blogs;

mysql-copy-rows

2. Paste in to excel and create a SQL statement for updating the table by concatenating the id with the statement

="UPDATE wp_"&A1&"_options SET option_value = replace(option_value, 'oldurl.com', 'newurl.com') WHERE option_name in ('siteurl', 'home'); "

3. Paste  statements in phpMyAdmin or MySQL Workbench

sql-workbench-rename-multisite

4. Remove the first statement refering to the main blog itself. It’s name is wp_options, not wp_1_options. (And the other steps in changing the site url is covered by Changing The Site Url in WordPress Codex and you’ve probably already read it if you found this post.)

ignore-first

5. Execute all statements and you’re all done!

results

Note: About the IGNORE in UPDATE IGNORE: This isn’t something i would advice on using in your code but when doing things manually it doesn’t matter. Have a look in the logs if some statement isn’t getting executed and fix it.

 

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>