Wordpress Database search and replace domain including serialized data.

wp-db-find-and-replace.sh

#!/bin/bash
#wp-db-find-and-replace.sh
#
#Script to search and replace on wordpress db in .sql format e.g. to change domain url
#Works with serialized data as it recognises the php serialized format
#writes a file output

#Based on version of:
#http://wojnowski.net.pl/main/index/updating-wordpress-database-urls-part-2
#(this doesn't work as-is, but the principles are good once it is fixed)

#usage and exit
usage_exit() {
cat <<EOF 1>&2
usage $0 options input-file

Wordpress db find and replace (e.g. url change).

OPTIONS:
-h          Show this message
-o  fname   Output file
-f  string  String to find
-r  string  Relacement string
-q          Quiet
EOF
exit $1
}

#Variables
# search string, e.g 'http://replace.me', in single quotes and with no 
#trailing slash
FIND=
# replacement string, e.g 'http://replacement.i.am', in single quotes and with 
# no trailing slash 
REPLACE=
# output file, updated database will be written to e.g /tmp/my_db
OUTPUT=
#no indication of progress
QUIET=

#check no params
[[ $# -eq 0 ]] && {
    usage_exit 0
}

while getopts ":o:f:r:qh" opt; do
    case ${opt} in
        o ) OUTPUT=$OPTARG ;;
        f ) FIND=$OPTARG ;;
        r ) REPLACE=$OPTARG ;;
        q ) QUIET=1 ;;
        h ) usage_exit 0 ;;
        \? ) echo "Invalid option: $OPTARG" 1>&2; usage_exit 1 ;;
        : ) echo "Invalid option: $OPTARG requires an argument" 1>&2; usage_exit 1 ;;
    esac
done
shift $((OPTIND -1))

#test for required options
[[ -z $OUTPUT ]] && { echo "Missing required option -o" 1>&2 ; usage_exit 1 ; }
[[ -z $FIND ]] && { echo "Missing required option -f" 1>&2 ; usage_exit 1 ; }
[[ -z $REPLACE ]] && { echo "Missing required option -r" 1>&2 ; usage_exit 1 ; }

#if remaining param is a file then take it as the std input
[[ -f "$1" ]] && exec < "$1"

######### Main program
#serialised data regex pattern (2 capture groups - length of string and actual string)
#the double quotes are literal as they are found in the serialized data.
RE_PATTERN='s:([0-9]+):"('"$FIND"'[^"]*)'

#empty output file
[[ -f "$OUTPUT" ]] && {
  truncate -s 0 "$OUTPUT" 
}

while read -r; do
    indicator=''
    #deal with serialized data. replace and update the serialized string format
    #convert line in place one serialized item at a time
    while [[ $REPLY =~ $RE_PATTERN ]]; do
    
        OLD='s:'"${BASH_REMATCH[1]}"':"'"${BASH_REMATCH[2]}"'"'

        UPD_DOMAIN=${BASH_REMATCH[2]/${FIND}/${REPLACE}}

        UPD_STR='s:'"${#UPD_DOMAIN}"':"'"${UPD_DOMAIN}"'"'

        REPLY=${REPLY/${OLD}/${UPD_STR}}

        indicator='s'
        [[ -z $QUIET ]] && echo -n "$indicator"
    done

    [[ "$indicator" = ''  ]] && {
        [[ -z $QUIET ]] && echo -n '.'
    }

    #deal with standard data and append to output file
    #(also writes the serialized data as it won't match/replace)
    echo ${REPLY//${FIND}/${REPLACE}} >> "$OUTPUT"
  
done
#end