Replace String within Serialized data

Question

I am moving my forum from one domain to another and need to replace all instances of the previous domain with the new one. In looking at the mysql data, many of the matches are going to be in the middle of a serialized string, is there a quick way to replace all matches?

Answer 1

You're probably not still looking for an answer, but in case anyone else stumbles across this question.

This is an issue in WordPress development all the time. There's a tool built specifically for it by Interconnectit - https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

I've never tried it for anything other than WordPress, but it connects directly to the database using the standard user/pw info, so you should be able to connect it to any database. I think the only thing WordPress specfic is it auto-populates the database info.

It's quick and easy to use, and it deals with serialized strings so they don't become a hassle.

Answer 2

Also, if you have shell access, wp-cli search-replace will handle serialization. From the directory where your WordPress files reside, you can do something like this:

wp search-replace 'https://old-domain' 'https://new-domain' --skip-tables=wp_users --skip-columns=guid --dry-run

Remove --dry-run once you're sure nothing will be replaced that should not be.

Another good option is to replace --dry-run with the --export=<filename> option (eg.--export=database_updated_url.sql) to export the revised database, leaving the original intact.

Here's the docs. There are options for using regex, skipping specific tables, and creating log files.

Notes:

• You cannot do both a --dry-run and an --export=<filename> in the same command.
• I kept getting Errors when using --skip-tables=wp_users
  UNTIL I listed that parameter in front of all other parameters. I found no documentation or any blog stating that it needed to be listed "first", but that was my experience.
  Error: Parameter errors: unknown --skip-tables parameter
• WordPress recommends that you NEVER update the url in the guid column.

Fun Fact:
In the event that your old_string has the exact same number of characters as your new_string, serialization will not be affected, since character counts will not change.
In that case, you can use any method, including MySQL, or a code editor, to replace the strings.

Answer 3

This was actually answered in a previous question here.

The most interesting answer is the highest rated- put the schema in a text editor and search and replace the prior domain string with the new one.

If the replace needs to be more targeted, say to certain columns or tables, the REPLACE() function will operate whether on serialized strings or otherwise.

REPLACE(columname, 'oldDomain', 'newDomain')