SQL Script to handle changing the Wordpress MySQL DB when you need to change your Multisite primary domain

change_multisite_url.sql

-- Run as:
-- mysql -uusername -p < change_multisite_url.sql

-- Set the db name, old site domain, and new site domain

use 'db_name';

SET @old_site = 'old_domain.com';
SET @site = 'new_domain.com';

-- DON'T EDIT AFTER THIS LINE

-- We use stored procedures instead of functions in this script because we
-- rely on Dynamic SQL, which isn't allowed in functions.

DELIMITER $$

-- Procedure to dynamically run a SQL string that's passed in as a param
DROP PROCEDURE IF EXISTS run_command $$
create PROCEDURE run_command (IN cmd VARCHAR(255))
BEGIN
  set @command = cmd;
  PREPARE stmt FROM @command;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$

-- Procedure to spin through all tables matching wp_x_options and change the
-- necessary fields
DROP PROCEDURE IF EXISTS edit_sub_tables $$
CREATE PROCEDURE edit_sub_tables ()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE current_table CHAR(32);

  DECLARE sub_site_tables CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'wp_%_options';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN sub_site_tables;

  read_loop: LOOP
    FETCH sub_site_tables INTO current_table;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- UPDATE siteurl FIELD
    SET @sql_text = concat('select @old_siteurl := option_value from ', current_table,' where option_name = "siteurl"');
    CALL run_command(@sql_text);

    select @new_siteurl := replace(@old_siteurl, @old_site, @site);

    SET @sql_text = concat('update ', current_table,' set option_value = @new_siteurl where option_name = "siteurl"');
    CALL run_command(@sql_text);

    -- UPDATE home FIELD
    SET @sql_text = concat('select @old_home := option_value from ', current_table,' where option_name = "home"');
    CALL run_command(@sql_text);

    select @new_home := replace(@old_home, @old_site, @site);

    SET @sql_text = concat('update ', current_table,' set option_value = @new_home where option_name = "home"');
    CALL run_command(@sql_text);
  END LOOP;
  CLOSE sub_site_tables;
END $$

DELIMITER ;

START TRANSACTION;
  -- http and NO slash
  update wp_options set option_value = CONCAT('http://', @site) where option_name IN ('siteurl', 'home');

  -- http and slash
  update wp_sitemeta set meta_value = CONCAT('http://', @site, '/') where meta_key = 'siteurl';

  -- NO http and NO slash
  update wp_site set domain = @site;

  -- Update multiple records here. NO http and NO slash
  update wp_blogs set domain = replace(domain, @old_site, @site);

  -- Update all of the children site fields
  CALL edit_sub_tables();
COMMIT;