Skip to content

Instantly share code, notes, and snippets.

@anhkevin
Last active May 6, 2022 14:10
Show Gist options
  • Save anhkevin/3269a18fe6dec800c5729c61adfabf88 to your computer and use it in GitHub Desktop.
Save anhkevin/3269a18fe6dec800c5729c61adfabf88 to your computer and use it in GitHub Desktop.
Mysql replace domain Wordpress

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
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment