Database Search and Replace Script in PHP

Search Replace DB version 3.1.0 allows you to carry out database wide search/replace actions that don't damage PHP serialized strings or objects with a user friendly interface and experience.

Installation & Use

search-replace-mobile-screenshot
Now acts like a web app!

Please read all instructions and familiarise yourself with the risks of using this tool first, downloading of this tool signifies your acceptance of the risks and problems associated with the script.

To use the script, download the zip file from below, extract the folder called search-replace-db-master (assuming you are using the 3.1.0 version), renaming it to something secret of your choosing, upload it via FTP, SFTP or SCP to your web server’s public facing directory, then navigate to that folder in your browser. The script will automatically attempt to find and populate the database field but you must check that the details are correct and that it is for the database you wish to carry out a search/replace operation on.

A typical WP install with this script would have the folders as follows:

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

IMPORTANT: Do not place the contents of the script’s folder into your root directory! It isn’t designed or supposed to run from there and, if you use the auto-delete functionality, it will delete everything in that folder, which could wipe out your site. I can’t stress this enough. If this isn’t clear for you it would be a good idea to seek expert help – hire a developer for an hour or two to come and do this for you.

To see how you can use this tool to aid migrations, check out our article on WordPress migrations or visit the WP Tuts+ article that mentions this script.

If you are in any doubt whatsoever about how to use this standalone script, then please consider getting an expert in. It’s a really powerful bit of code that if used badly can damage a WP install beyond repair. If you want help, get in somebody like us, for example, or any of the other great guys listed over at CodePoet.

IMPORTANT: This code is supplied with no warranty or support implied. You use it entirely at your own risk. The code is licensed under the GPL V3 and you should be aware of its limitations and how they affect you and your use of this code. Please also read the instructions and warnings provided and always take a verified backup of your database before starting.

Download v 3.1.0

Older server? Try v 2.1.0

Ad

Please beware of adverts that offer a download button!
//

//

Why is there an ad here? Because the small amount of revenue this advert might bring us helps to go towards the not inconsiderable cost of running the search/replace project. It’s in the order of thousands of pounds, believe it or not, and we’re working to a new version with better compatibility and this will help.

Changelog:

Test 20160504

  • Version 3.1.0 (Web UI and CLI versions) tested against PHP 7.0.6 and functioned correctly.
  • Version 2.1.0 confirmed to not work with PHP 7.0.6 but is kept for use on older servers.

Version 3.1.0:

  • Safety checks to prevent deletion when installed incorrectly. However, you should still take care when dealing with files on your server.
  • JavaScript popup confirmation on ‘Delete Me’.
  • Port number option in both the GUI and CLI. Use –port nnn to set a non-default MySQL port.
  • Fixed Drupal bootstrap behaviour. Start up of script uses Drupal data as guide, no longer relies on a full successful Drupal initialisation before script will allow you to proceed.
  • Driver selection improved so that PDO will be attempted first if PDO+mysql is available, with mysqli being used as a fallback. This fixes ‘driver not found’ errors
  • Removed mysql_ functions and replaced with mysqli_.
  • Improved JS preview overlay for dry runs. This means that the right pane will always show the most accurate data possible. If serialised strings are present, highlights are not displayed.

Version 3.0.0:

  • Major overhaul
  • Multibyte string replacements
  • UI completely redesigned
  • Removed all links from script until ‘delete’ has been clicked to avoid security risk from our access logs
  • Search replace functionality moved to it’s own separate class
  • Replacements done table by table to avoid timeouts
  • Convert tables to InnoDB
  • Convert tables to utf8_unicode_ci
  • Use PDO if available
  • Preview/view changes
  • Optionally use preg_replace()
  • Scripts bootstraps WordPress/Drupal to avoid issues with unknown serialised objects/classes
  • Added marketing stuff to deleted screen (sorry but we’re running a business!)

Version 2.2.0 (never formally released but patched into v3.0.0):

  • Added remove script patch from David Anderson (wordshell.net)
  • Added ability to replace strings with nothing
  • Copy changes
  • Added code to recursive_unserialize_replace to deal with objects not just arrays. This was submitted by Tina Matter.
  • ToDo: Test object handling. Not sure how it will cope with object in the db created with classes that don’t exist in anything but the base PHP.

For changes prior to v2.2.0 please refer to index.php where you will find a complete changelog. You can also browse the project on github.

To Be Done

  • Ensure UTF8 encoding is enforced (see comments). Added in v2.1.0
  • Self deletion or security system to prevent accidental security risks. Added in v3.0.0
  • Release CLI version for use on non-WP sites, or for other purposes (already supports use on any MySQL DB.) Added in v3.0.0
  • Change to GPL V3. Added in v3.0.0
  • Eliminate warnings and remove deprecated function calls. Added in v2.1.0
  • Add facility to subscribe to interconnect/it Newsletter. Added in v3.0.0
  • Confirm deletion has actually happened.
  • Add old versions for download to this page.

Contributions

We’d love to get contributions, bug reports and more on the Search Replace DB public github repository. Please come on over – you’ll be more than welcome!

Donations

We’ve been asked a lot in the comments box below about accepting donations. But you can’t believe what a headache that is from an accounting and tax perspective.

Consequently all we can say is that if you wish to you can buy a personal gift for the key developers from one of the wishlists below – especially given that it’s a spare time project. If others who have contributed wish to provide us their wishlist links then we’d be more than happy to add them.

1,568 responses to “Database Search and Replace Script in PHP

  1. Are there any issues with this and doing a find and replace in wp_postmeta? For some reason it works on all other tables, except postmeta.

    1. Should just work, but as with everything – there are exceptions. We’re rolling out some new fixes shortly and are in the process of patching in contributions and testing. May be worth trying out the new version when it comes – hopefully next week or thereabouts.

  2. Thank you for this!

  3. I absolutely love this tool. It has worked very well for me, so thank you!

    I didn’t see you mention the WordPress GUID database column anywhere though – the GUID table (inside wp_users) contains the URL when the post was published but is only used as a unique identifier for that post. It should never be changed. Perhaps your script could ignore this column? If someone uses your script on a live site, all previous posts will be sent out to everyone’s feed readers, etc.

    From http://codex.wordpress.org/Changing_The_Site_URL : “Never, ever, change the contents of the GUID column, under any circumstances.”

    Paul

    1. Correction: It’s in the wp_posts table, not wp_users

    2. Hi Paul – good point. If you’re launching a site from a localhost install then, indeed, you want the GUIDs to change, but if you’re simply switching domains then you wouldn’t want this to change.

      It’s a fair bit of work to change and test this because of the automated way that the script works, so we won’t be able to include this on the forthcoming release, but I’ll pencil it for the release after that.

      1. That would be lovely, thanks for the reply and consideration.

  4. Wow.. This script has saved me HOURS over the last few months. I really appreciate you making this publically available.

  5. Deprecated: mysql_db_query() [function.mysql-db-query]: This function is deprecated; use mysql_query() instead
    This error is shown when I try to replace with new word.I think mysql script is not run by me.Host provides me the script.Will I tell my host provider to update their script or any other solution Can you give me a suggestion …….

    1. Hi – this problem will be fixed in the next release of the plugin. I know that’s not much help right now, of course, but it’s not far away.

  6. This is a great tool! It has been saving me a lot of time automating many of the steps that I had been doing manually to migrate my data.

    I am currently using this tool as part of my migration process when I move new releases from dev->test->prod. The process I’ve set up only migrates the database changes as opposed to the entire database. This way I am able to keep all of the existing content in each environment untouched. I find that Toad works really well for comparing the two database instances and makes it very easy to determine what records need to move. For most releases, the only table that is effected is the wp_options table.

    In any case, I was wondering if you had any experience in this area and if so I was looking wondering how you handled the autoincrementer and transient records? I typically leave those objects untouched and things seem fine, but I was curious what others were doing if anything.

    Thanks!
    -J

    1. Generally it should ‘just work’ – I like to clear transients out on a migrate, but I’ve not experienced any significant issues. Where we did, we modded the code to handle the fact that we need to recursively run through our search and replace as transients lead to the bizarre case of serialized PHP strings within, uhm, serialized PHP strings. That caused problems but is solved.

  7. David,

    Thanks for the response. I agree. It generally does just work. The only strange thing that I sometimes find is that the id numbers of the records in the wp_options table end up changing around a bit. I think that is a result of the fact that the autoincrement value on the source and target DB is not the same and new records are therefore getting added with different id numbers, but like I said, it all seems to work.

    I have considered reseting the autoincrement value on the target DB whenever i import new records, but i havent had a chance to play with it yet.

    Thanks again,
    -J

  8. Found another bug. In an utf-8 database, if a field in a serialized array contains a german “umlaut” like ÄÖÜäöü, the search-term gets replaced ok, but the length of s is not updated. WP 3.2.1, Simple Local Avatar plugin, ‘umlaut’ in filename, table: user_meta ,field meta_value, search-term: /wordpress/ replace: /

    Before:
    a:5:{s:4:”full”;s:81:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx.jpg”;i:96;s:87:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-96×96.jpg”;i:200;s:89:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-200×200.jpg”;i:32;s:87:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-32×32.jpg”;i:120;s:89:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-120×120.jpg”;}

    After:
    a:5:{s:4:”full”;s:81:”http://www.domainxxx.com/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx.jpg”;i:96;s:87:”http://www.domainxxx.com/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-96×96.jpg”;i:200;s:89:”http://www.domainxxx.com/wp-content/uploads/2011/XX/Xxxxxx-Önxxxxx-200×200.jpg”;i:32;s:87:”http://www.domainxxx.com/wp-content/uploads/2011/XX/Xxxxxx-Önxxxxx-32×32.jpg”;i:120;s:89:”http://www.domainxxx.com/wp-content/uploads/2011/XX/Xxxxxx-Önxxxxx-120×120.jpg”;}

    1. Thanks – this will be fixed in the next release.

  9. Reported a bug about standard wordpress text/html widget yesterday, not sure if it was saved on your site, did not see “Comment awaiting moderation.” message yesterday. If it was saved, ok, delete this, if not, please reply here, then I post again.

  10. Is there a way to run this tool to just find the # of occurences of a string in the db without replacing anything? If I try to set search and replace strings to the same value, the tool complains and won’t run, and if I leave replace string empty, my expectation would be that all occurrences of search string will be replaced with the null string. Just getting a count would be very useful in many situations.

    1. Good idea – because of the MySQL MyISAM setup you can’t run in a non-transaction mode easily, so the code has to be a little more careful to not run updates. But I’ll make sure to pencil this in for a future version of the plugin.

  11. Just thought I’d post my appreciation for this tool, and describe how I’ve fit it into a simple workflow to solve what I think must be a common problem for many WP developers.

    Problem: Client wants a new site in WP, but has an old site in (HTML | Drupal | Joomla | who knows what) that must stay live while new site is developed. Additionally, the new site must eventually live under the same domain name as the old site. So the new site must be developed and populated in some temporary location, which means links in the WP db will all point to the temporary location. The problem is, once the new site is ready, how to switch over from the old site to the new?

    Solution: Develop the new site in some temporary location, either a subdomain like newSite.mydomain.com or something like 120.121.122.123/~myaccount. When the new site is ready, follow these steps:

    1) Run searchreplacedb2.php on the new site replacing whatever the old domain was with the permanent domain name

    2) Insert the following two lines into your wp-config.php file:
    define(‘WP_SITEURL’, ‘http://seviche.prioritybeta.com’);
    define(‘WP_HOME’, ‘http://seviche.prioritybeta.com’);

  12. Just thought I’d post my appreciation for this tool, and describe how I’ve fit it into a simple workflow to solve what I think must be a common problem for many WP developers.

    Problem: Client wants a new site in WP, but has an old site in (HTML | Drupal | Joomla | who knows what) that must stay live while new site is developed. Additionally, the new site must eventually live under the same domain name as the old site. So the new site must be developed and populated in some temporary location, which means links in the WP db will all point to the temporary location. The problem is, once the new site is ready, how to switch over from the old site to the new?

    Solution: Develop the new site in some temporary location, either a subdomain like newSite.mydomain.com or something like 120.121.122.123/~myaccount. When the new site is ready, follow these steps:

    1) Run searchreplacedb2.php on the new site replacing whatever the old domain was with the permanent domain name

    2) Insert the following two lines into your wp-config.php file, where the value of the constant is the permanent domain name for the site:
    define(‘WP_SITEURL’, ‘http://www.mydomain.com‘);
    define(‘WP_HOME’, ‘http://www.mydomain.com‘);

    3) If necessary, go into the control panel for your VPS or hosting provider and change the domain name for the account to be the permanent domain name.

    4) Change the DNS for the domain name. When the change finishes propagating, the new site will come up instead of the old one, and all of the links will point to the permanent domain.

  13. Guys, thx so much, you saved my life…

  14. Hi Dave

    It seems there is a typo in warning message.

    “And if you’re English is bad and you don’t fully understand the instructions then STOP. Right there. Yes. Before you do any damage.”

    The “you’re English” part should be “your English.”

    BTW, big thanks for the updated script, I hope you keep it up for as long as WP keeps using absolute URL.

  15. Dave,
    I really appreciate this tool, but I was curious when you plan on releasing the next version?

    Thanks,

    James

    1. Hi James – as you’ll see, we’ve now released version 2.1.0 – it fixes a lot of things and now makes a really sweet general purpose search/replace tool for PHP developers who like serialized strings!

  16. Thanks Dave,

    This proved very helpful for migrating a development environment from MAMP Pro on OS X to a staging server.

  17. Just wanted to say how much I love this script. Huge time saver for me. I use it to help clone my WordPress sites. I used to do a search and replace on the database in Notepad++ but of course that never got everything and I’d lose widgets and some other things. But with your script it works like a charm and I can clone a site to a new domain in a few minutes. Brilliant!

  18. Your script has saved my life yet again 🙂 Thanks!

  19. Your code just saved my ass!

    I spent ALL DAY trying to get my new site running like the old one. I thought it was my upload speed screwing the database transfer until I came across his little script. I’d been using text edit with find/replace until now. It had been working fine, but I think the latest plugins I added to the site caused big problems. Your little plugin solved them. Can’t thank you enough. I want to marry you and have your babies!

    :O)

  20. Thanks a lot! It took me a while, but eventually I figured that the problem was with the serialized database entries.

    Your tool saved me tons of time. This should really be part of the standard WordPress installation.

  21. Didn’t work for me – I get 403 forbidden on step 5, i.e. when I press “Submit Search string” on step 4.
    (I’m trying to update from temp url to domain, i.e. from http://someip/~accountname to http://mydomain.com

    And you messed up version numbers – at the top of the header it’s 2.0.1, release history does include 2.1.0 and the page (gui) says 2.0.0

    1. I’m not sure why you’d get a 403 error on that – should just work, and indeed does on every web server we’ve tested it on.

      Could you post some details about your webserver, perhaps? Along with PHP version, mySQL version, etc.

  22. forgot – it’s wordpress (3.2.1)

  23. Hakan Küçükyılmaz

    Hi David,

    your latest update to 2.1.0 fixed my problem moving cforms related settings. Is it possible to donate you guys some money for the excellent work?

    Also, for our deployment scripts we would need a command line version of this script. Do you have any plans to develop one? The databases connection settings and the strings to be replaced could be placed into a simple configuration file.

    1. Hi Hakan – thanks!

      It’s not possible to donate through the interconnect/it site at the moment as we’re trying to work out the tax and accounting implications for the company (it gets complex!) so the only thing I suggest is that you can send a personal gift to any of the developers involved using their Amazon Wish Lists:

      James Whitehead’s: http://www.amazon.co.uk/registry/wishlist/1CWUCTVKCZSFP
      Robert O’Rourke’s: http://www.amazon.co.uk/registry/wishlist/1EWBGC8Z5LCYF
      David Coveney’s: http://www.amazon.co.uk/registry/wishlist/1I2TQ9F0U0MG0

      All in various states of maintenance, but we’re encouraging James to put things on his!

      We’ll sort out some kind of contributor’s page in due course so anybody can be thanked.

  24. I am a big fan of this tool which constantly saves me lots of time. I LOVE it but please allow me a few minor critiques. I was hoping with the new 2.1 version that the multiple annoying ‘are you sure’ questions were removed (I use the tool _every day_), I don’t find the new features particularly useful (for me), and how come the first page still says v2.0. Looking forward to a more compact interface of this amazing script (a separate readme with all the warnings would be plenty). Thank you so much!

  25. Hi Dave & Team,

    Excellent piece of script here. You’ll have really saved me a lot of time and headache. I came across your page only after i realized that the widgets were not getting properly imported across domains and googled the net for solutions.

    Wish i had come across your site earlier.

    Thanks big time

    Regards
    Prashanth D’Silva

  26. Bro, your software worked like a champ!

  27. Wow. This is an incredible script. Helped with with my WordPress site. Any place for me to send a small donation?

    Thank you!

    1. Hi Ted, thanks for the good words!

      We don’t really have a way of accepting donations for this code at the moment, I’m afraid. You could always buy one of the devs something from the list shown in the message before, but it’s really not necessary 🙂

  28. I’m finding that this script does not work with BLOB fields. Any ideas on what I can do to get it to work with them? Drupal 7 has a lot of BLOB fields that contain serialized data.

    Thanks bunches.

  29. Actually, after looking at this a bit more, it looks like it works fine for BLOBS, just not for serialized data that contains Objects. Any way this can be incorporated into a newer version? Does anyone have a solution that I can use for this?

    Thanks bunches.

  30. Thanks so much for this!

  31. Dude!!! You guys are the most awesome of the awesome! I’ve been trying for 2 weeks to get my WPMU site up from local to live server. I tinkered with your instructions for 20 minutes and my life is complete!

    I just got a new job and was trying to finish up a client site before I leave. With your help, I’ll be getting paid. Looks like you guys have another satisfied user who would love to make a contribution to your development efforts.

    Thanks a million.

  32. amazing stuff, much appreciated!

    searched in the comments but haven’t found anything about “line breaks”

    i mean, i tried to search a block of Html lines… and no matches are found

    tried to change the input text with a textarea (srch and rplc), but nothing…
    any hints?

    1. Thanks Rudolf. With respect to line breaks – I take it you don’t mean
      tags but line breaks within longtext fields? If that’s the case I’m afraid we haven’t tried – the tool was always designed as a migration tool rather than an absolute search and replace tool so there’s likely to be some limitations. Try searching for \r or \n and see what happens and let us know.

      1. hi David, thank you, again, for such useful tool

        Yes, I’m referring to longtext strings.
        And just tried to search for the exact string that appears in a mysql dump (single line, with \r\n as line breaks), but it doesn’t work.

        Feature request: I miss the option “Search again” (reusing DB Details and selected tables)

        1. To do the search again you can simply click the back button on your browser – the values should remain selected. Or is this not the case for you?

  33. there are some depreciated functions but just remove notification errors. Works beautifully! Thanks

    1. Hi Ryan – there shouldn’t be any deprecated functions any more (we had a clean-up recently) – are you running the latest version?

  34. Hey there, I ran into an error when executing this on a large database. I believe the DB weighs in at 500MB as a text file. Can the script handle large databases?

    1. Large databases shouldn’t be an issue except with respect to time-outs. It will run through most in seconds. We regularly use the script on large scale sites with no difficulties, but we haven’t done any testing to say what does and doesn’t work. Can you give more detail about what happens?

      1. Hi David, if I remember correctly, I got an Error 500 running the script on the entire database I mentioned. It worked great when I ran it on each table individually! There is one table that it errors out on though, wp_ak_404_log, which, I hesitate to say, is worth almost 400,000 records. Thanks for your generosity in building this, it has done much good.

  35. The script did work for my Drupal website, but caused other weird problems. Some of my pages went blank, and links on the homepage were missing.

    I would say that this script is generally not applicable to Drupal-based websites.

    Just sharing my experience for the benefit of other Drupal fans.

    1. Hi Jaspal – Drupal also stores things as Blobs, which need further decoding. A patch has been submitted and we’ll soon release a Beta version 2.2 which should work fine on Drupal installs. The problem is we’re a WordPress shop so we’ll need partners to help test the Drupal changes. I might be in touch!

      1. If you can try out the master version on github at https://github.com/interconnectit/Search-Replace-DB it would be hugely appreciated – this should hopefully fix the Drupal issue you’ve had.

  36. Thank you – this script was very useful as I was trying to migrate a WordPress site – “normal” search and replace wasn’t working for me and this script helped fix the problem (and saved me from having to manually dig through a lengthy file).

    Note – Paul is correct -there is a typo in your warning message. 🙂

    “And if you’re English is bad and you don’t fully understand the instructions then STOP. Right there. Yes. Before you do any damage.”

    “you’re English” should be “your English.”

    1. Oops! How ironic… Pedantry points to you Stacy! Thanks for letting us know.

  37. What a life saver. I was going crazy trying to update my new URL structure in thousands of entries with my text editors find/replace. I finally realized I was using serialized arrays for most of my WordPress post meta and was destroying my data by only replacing the string and not updating the length. A quick Google search and this script saved me a major headache and a lot of time.

  38. Thanks for hard work and share it!

  39. this is good work!!

  40. Hey man. thanks for your work on this script..
    wordpress needs to add this link to change server FAQ’s

    very very useful

    thanks again

  41. You just saved my life.

  42. Thank you for this script, fixed all my problems!!!

  43. Thank You! I will add this to the WordPress codex (if it’s not already in there).

    1. Thanks Kees! I’m assuming Kees is indeed your first name…

      Really appreciated.

      1. Yes it is. I added it here : http://codex.wordpress.org/Moving_WordPress#Moving_WordPress_Within_Your_Site
        Let’s hope they will let it in there and will look for more places to add it.

        When you skip the guid column it also skips the guid for attachments (WordPress). Shouldn’t the attachement guids be replaced when migrating live sites? Maybe a new option for this?

        1. Thanks for adding it!

          Ah, you make a good point about the attachment guids – skipping those could, in some cases, cause a problem. It’s very rare for us to move a site between URLs and all the aggregation work we do is curated so it doesn’t cause issues there BUT for some it’s important, so we’ll look to adding it to the project on GIT at https://github.com/interconnectit/Search-Replace-DB – even better if somebody wants to contribute the change – it’ll be a moderate but not extreme amount of work.

  44. Thanks a million! I think this corrected the last of my 404s. I migated from Blogger self hosted to WP self hosted and this was just the trick for some images that didn’t import.

  45. Works as advertised. Thank you!

  46. rookie question – a little new to php programming.
    Where exactly do I put the strings I need to find and replace with and any other parameter i need to modify?
    I took a look at the script and it said “To use, simply configure the settings below and off you go”. where exactly is below in the code?

    I know its a rookie question, but please help me.
    Sincerely,
    Hagai Hadad

    1. You don’t need to look at the code to use the script. Once you have placed the script on your server point your web browser to the file itself eg: http://example.com/searchreplacedb.php and you will see an HTML page with instructions. Follow those and you will be prompted with fields to enter the text to text to find and what to replace it with.

      Hope that helps…

Comments are closed.