Updating WordPress database URLs - part 1

Created:01 Jan 2017 15:27:35 , in  Web development

Update URLs in WordPress database.

A word of caution

Make sure you don't introduce changes on a live website and that you keep the original backup file till you're sure that new setup works as required.

Before you run the script given belowmake sure you have user and password configured in .my.cnf file. The script does not check for that.

The script

####################################################
# update_wordpress_db.sh
# Athor : Sylwester Wojnowski
# Website : wojnowski.net.pl
# This script updates wordpress database WP_DB tables by replacing URL_FROM in
# wp_options, wp_posts, and wp_postmeta tables to URL_TO.
# Tables created by plugins will not be updated.
# It makes old databse backup before introducing changes to WP_DB tables
# This script DOES NOT update serialized data, it will tell you if you have any in wp_options
# Make sure you have MySQL user and password in .my.cnf, before starting the script. 

# database name to conduct the update on
WP_DB="your_mysq_db"
# URL to be replaced (notice no trailing slash)
URL_TO="http://replace.me"
# new url (notice no trailing slash)
URL_FROM="http://replacement.i.am"

######## Private - don't edit ########

# check for serialized data in wp_options
has_serialized=$(mysql -e "use $WP_DB;select * from wp_options where option_value REGEXP 's:[0-9]+:\"${URL_FROM}';")

# check for serialized $URL_FROM 
[[ "$has_serialized" = '' ]] || {
  echo "Serialized $URL_FROM exists in wp_options table. This script doesn't update serialized data. Exiting ..."
  exit 1;
}

# create backup
mysqldump "$WP_DB" > "${WP_DB}.backup.sql"

# check for backup existance
if [[ ! "$?" = "0" ]];then
  echo "DB backup could not be done. Exiting ..."
  exit 1; 
fi

# run update
mysql -e "
  USE ${WP_DB};
  UPDATE wp_options 
    SET option_value = REPLACE(option_value,'$URL_FROM','$URL_TO') 
    WHERE option_value 
    LIKE '%${URL_FROM}%';
  
  UPDATE wp_posts 
    SET guid = REPLACE(guid, '$URL_FROM','$URL_TO');
  UPDATE wp_posts 
    SET post_content = REPLACE(post_content, '$URL_FROM', '$URL_TO');
  UPDATE wp_postmeta 
    SET meta_value = REPLACE(meta_value,'$URL_FROM','$URL_TO');
"

######## EOPrivate ######## 

# For not output, remove the 5 following lines
[[ "$?" = "0" ]] && {
  echo "$WP_DB updated!"
} || {
  echo "$WP_DB could not be updated! I hope you have a backup ;))"
  exit 1
}

Running

chmod 755 ./update_wordpress_db.sh
./update_wordpress_db.sh
 

This post was updated on 06 Oct 2021 20:47:21

Tags:  BASH ,  mysql ,  wordpress