Mysql replace domain Wordpress

wp_replace_domain.md

Mysql replace domain Wordpress

SQL to replace domain name in Wordpress MySQL

Usage

• Step 1: Updating serialised array in mysql
• Step 2: Updating not serialised in mysql

wp_replace_domain.sql

SET @search = 'domain_old.com';
SET @replace = 'domain_new.com';

-- Replace domain is serialised array in mysql
UPDATE wp_options SET option_value = replace(option_value, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));
UPDATE wp_posts SET guid = replace(guid, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));
UPDATE wp_posts SET post_content = replace(post_content, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));
UPDATE wp_links SET link_url = replace(link_url, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));
UPDATE wp_links SET link_image = replace(link_image, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));
UPDATE wp_postmeta SET meta_value = replace(meta_value, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));
UPDATE wp_usermeta SET meta_value = replace(meta_value, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"'));

-- Replace the rest of the domain
UPDATE wp_options SET option_value = replace(option_value, @search, @replace);
UPDATE wp_posts SET guid = replace(guid, @search, @replace);
UPDATE wp_posts SET post_content = replace(post_content, @search, @replace);
UPDATE wp_links SET link_url = replace(link_url, @search, @replace);
UPDATE wp_links SET link_image = replace(link_image, @search, @replace);
UPDATE wp_postmeta SET meta_value = replace(meta_value, @search, @replace);
UPDATE wp_usermeta SET meta_value = replace(meta_value, @search, @replace);