How to search and replace php-serialized value inside MySQL database

How to search and replace php-serialized value inside MySQL database

For the last couple of months two of my customers asked me to clone the sites I made for them. By «clone» I mean that they liked their sites so much that they decided to setup some other sites using the same set of themes and plugins and just change content a bit.

So they asked me to create exact copy of their sites but under different domain names.

«Easy!» you might say, «Just copy all the files and folders, export database, import database, and change domain name settings…»

And here comes the problem. We have to change domain name inside database. The easiest way (or so it seems) is to search-and-replace domain name in the sql dumpfile. But if you’ll perform dumb «replace all» you can get inoperable database. And the reason is that some plugins and themes store their data in a serialized form and a domain name can be a part of such serialized data. For instance:

{s:4:"home";s:31:"http://ru.magnacartacollege.org";s:4:"link";s:107:"http://blogsearch.google.com/blogsearch?scoring=d&partner=wordpress&q=link:http://ru.magnacartacollege.org/";s:3:"url";s:141:"http://blogsearch.google.com/blogsearch_feeds?scoring=d&ie=utf-8&num=10&output=rss&partner=wordpress&q=link:http://ru2.magnacartacollege.org/";s:5:"items";i:10;s:9:"show_date";b:0;}

If you’ll take a closer look, you’ll notice that string value length (s:31) is stored here and if you’ll just replace domain name the serialized date will become inconsistent:

s:31:"http://ru.magnacartacollege.org"

To solve that problem you can write your own script that:

  • reads all the rows from all the database tables
  • finds cells that contain designated value
  • detects whether data is serialized, replace if not
  • unserializes data, replaces value, serializes again and stores updated data

Or you can use nice and handy script that is already written and recommended by WordPress Codex (that’s how I learned about it)

Search-and-replace

All you need to do is upload this script to your server, setup mysql credentials and specify «search» and «replace» values. And regular expressions work too! Keep in mind that you have two options:

  • Dry run – perform replace operation without storing the results
  • Live run – perform replace operation and store results (cannot be undone).

I insist you’d better always begin with «dry run».

And here’s a little bonus. If you are working with WordPress database, you can put search-and-replace script folder into WordPress root folder like that:

/your-secret-search-replace-folder
/wp-admin
/wp-content
/wp-includes

… and it’ll find and use your wordpress credentials.

Keep in mind mind to remove the script itself when you are done.

SSH aproach

Alternatively you can install utility as executable binary and use it via SSH without exposing dangerous UI via HTTP.

To do that clone github repositiry:

git clone https://github.com/interconnectit/Search-Replace-DB.git /usr/local/lib/Search-Replace-DB
sudo ln -sf /usr/local/lib/Search-Replace-DB/srdb.cli.php /usr/local/bin/srdb

To run search and replace:

srdb -h localhost \
-n _database_ -u _db_user_ -p _password_ \
-s old.domain.com -r new.domain.com

Add -z to dry run, to get detailed info on usage:

srdb --help
{{"Show older comments..."|nls}}{{comments.length}} {{"of"|nls}} {{total}} {{"shown" | nls}}

{{'Comment'|nls}}:

{{(dialog.replies.length > 1?'Replies':'Reply')|nls}}: