Updating WordPress database URLs - part 2

Updating WordPress database URLs - part 2

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

In this second part on the updating WordPress database URLs I'll concentrate on a database-wide approach. All tables will be scanned and old URLs updated to new ones. Also serialized data will be properly dealt with.

In this part, mysql utility will not be involved ( but mysqldump will, hence make sure you have mysql client login and password configured in ~/.my.cnf). The job will be done with a BASH script.

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 sometimes, 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. Still makes sure your values are single quoted.

As usual, 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. Remember, 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;

Using the script for other changes to your database

In short, it can be done. However, I will repeat myself here, keep your backup ready in case something goes wrong. For some values the script stops for some time, be patient with it.

Final thoughts

I hope you will find this short article and the code in it useful. Don't forget to check the first part of this short series. Information in it might also prove useful.

I am looking forward to your feedback on your updates with both of my scripts.

This post was updated on 25 May 2017 16:12:55

Tags:  BASH ,  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, 2018 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 2." From sWWW - Code For The Web . https://wojnowski.net.pl//main/index/updating-wordpress-database-urls-part-2

Post navigation

Previous:
  Updating WordPress database URLs - part 1

Next:
  Env and BASH declare