Updating WordPress database URLs - part 2

Created:03 Jan 2017 13:03:49 , in  Web development

Updating WordPress database URLs, part 2 - a database-wide approach. All tables get scanned and old URLs updated to new ones. Also serialized data is properly dealt with.

The script

The script is a general purpose filter, it takes INPUTDB, which is name of the database (as given by mysql) to change URLs in, OUTPUT which is name of a file filtered input will be saved to, URL to find ( FIND ) and URL to replace FIND with ( REPLACE ). Configure these to fit your needs.

The script does not change the original database, it saves updated database to whatever you assign to OUTPUT variable. Hence there is no backup this time. You are responsible for making it by yourself. Because things do go wrong with computers, make sure you have it before uploading updated database to your MySQL server.

#!/usr/bin/env bash

####################################################
# wordpress_db_find_and_replace.sh
# Athor : Sylwester Wojnowski
# Website : wojnowski.net.pl
####################################################

# 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=
# outpu file, updated database will be written to e.g /tmp/my_db
OUTPUT= #output;
# mysql input database e.g. wordpress_local
INPUT_DB= 

######### private
RE_PATTERN='s:([0-9]+):\\"('"$FIND"'[^"]*)'

[[ -f "$OUTPUT" ]] && {
  truncate -s 0 "$OUTPUT" 
}
while read -r; do
  indicator=
  # deal with serialized data
  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
      echo -n "$indicator"
  done

  [[ "$indicator" = ''  ]] && {
    echo -n '.'
  }
  
  echo ${REPLY//${FIND}/${REPLACE}} >> "$OUTPUT"
  
done < <(mysqldump "$INPUT_DB")

Running the script

The script doesn't check configuration for you. read comments to each variable, make sure you understand them before you introuduce changes to the head of the script.

FIND and REPLACE variables can hold not only URLs but also other strings. Makes sure your values are single quoted.

Save the script to a file (let's call it db_url_find_and_replace.sh) , then

chmod 755 db_url_find_and_replace.sh

and execute it using the command line:

./db_url_find_and_replace.sh 

Once you have output file, you can import its contents back into mysql server using mysql utility.
Make sure you have a backup of the old database before doing that.

mysql INPUT_DB < OUTPUT

Watch out for your locale settings

One reason the script may not work for you is locale mismatch. Make sure your locale settings for both BASH and database you are trying to modify are set right.

locale command gives you information about locale BASH is using.

Changing locales to, let's say pl_PL.UTF-8 on the command line can be done like this:

LANG=pl_PL.UTF-8; export LANG

If you need to find out what locale your database is working with, use mysql command line utility as follows:

USE db_name;
SELECT @@character_set_database, @@collation_database;

This post was updated on 06 Oct 2021 20:51:37

Tags:  BASH ,  wordpress