Updating WordPress database URLs - part 1

Updating WordPress database URLs - part 1

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

Either when developing a new WordPress website or when dealing with one that already exists but is in need of a move to another host, you're certain to come across an issue related to updating old domain name with a new one.

A typical WordPress website ( by typical I mean, one that uses no plugins which add their own tables and save domain name in them ) stores most of stuff with domain name in it in wp_posts, wp_options and wp_postmeta database table. Hence if you know mySQL or at least can use phpMyAdmin you can change these values manually.

On the internet there are resources on how to updated quick. Some people have even written plugins that help with the change. The trouble with some of these solutions is, they do not take into account that sometimes your old domain is in a serialized PHP array or object. You update your string using SQL or maybe PHP and you lose some data because new domain is not of the same length as the old one.

Since I'm not a fan of tons of plugins and I'm even less of a fan doing things manually, I use a BASH script (actually two of them, the other script, that can deal with database-wide changes and serialized PHP data will be presented in the second part of this series) for the updates.

If you value your time, you might consider doing the same before the job becomes a daily chore. It real can if you move websites from host to host often enough, especially when developing or updating.

The script

The script below will check if the domain you need to replace exists somewhere in wp_options table as part of a serialized PHP structure. It will exit making no updated it this is the case.


####################################################
# 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
}

The script above is a rather simple. It is effective in most of the standard cases, excluding when your domain is a part of serialized structure. It runs from the command line and uses mysql utility to introduce required changes.

You input old, new domain name and database name it will work on, before running it (these values are stored in URL_FROM,URL_TO and WP_DB variables respectively, update them to fit your requirement). Also, before the script starts changing database table data the script makes the database backup in the same directory it is run from. The backup file is suffixed with backup.sql.

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 make sure you have user and password configured in .my.cnf file. The script does not check for that.

Running

To run the script, you save update_wordpress_db.sh file in a directory, then:


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

The script is readily extensible and can be changed for example to become a part of a large update or install process which is how I use it in most instances.

Part two of Updating WordPress database URLs

If you have a database that looks anything but what you're used to seeing after a fresh WordPress install, see the second part of this series. In it I'm going to post a script that carries out search and replace on any table it finds and deals with as well as deal with serialized data correctly.

Let me know if the script worked, or perhaps not, for you.

This post was updated on 25 May 2017 16:11:57

Tags:  BASH ,  mysql ,  wordpress 


Author, Copyright and citation

Author

Sylwester Wojnowski

Author of the above article, Sylwester Wojnowski, is sWWW admin and owner.He enjoys doing Maths and studying algorithms, writing code in scripting and command languages, Thrash Metal music and playing electric guitar.

Copyrights

©Copyright, 2017 Sylwester Wojnowski. This article may not be reproduced or published as a whole or in parts without permission from the author. If you share it, please give author credit and do not remove embedded links.

Computer code, if present in the article, is excluded from the above and licensed under GPLv3.

Citation

Cite this article as:

Wojnowski, Sylwester. "Updating WordPress database URLs - part 1." From sWWW - Code For The Web . https://wojnowski.net.pl//main/index/updating-wordpress-database-urls-part-1