Search and Replace text in whole MySQL database

I had a problem recently where I had spent ages building a new CMS driven site which had loads of content – all interlinking with keyword rich anchor text etc, a WordPress blog, an email marketing system built in and a few other gismos to boot. With everything just about finished, the site SEO’d (well on-page SEO anyway) and uploaded to the final server for final tests I hear the imortal phrase “We’ve decided to change the site name and domain”.

I suppose you can guess my thoughts – 1000’s of database records across over 60+ different tables – any of them containing a reference to the name or the domain name. Oh please oh please let there be an easy way to change all these!!

In come comes the Google searches:

“replace text in database”
“replace text in whole database”
“find and replace text mysql”
etc etc

The results were coming in thick and fast but most of them just seemed to deal with individual columns or tables or you have to export all your data to text files and use find and replace functions in things like notepad – oh and of course you get the REALLY unhelpful people that just tell you how you should have done it in the first place. None of them really did what I wanted (although the export to file would have but I wanted another way!).

So – as usual I was left to figure it out myself. I thought there must be some kind person that has written a PHP script which you can put in search text and a replacement, point it to a database and it just loops through all the available tables, columns and records – then outputs a nice report to say where it had made the updates.

Could I find it? ….. nope.

So I wrote it myself. If you want it then here it is: http://mj7.co.uk/am03

Please let me know if you find it useful or if you can make it better I’d love to have it streamlined – tweet me @mjdigital or comment below.

UPDATE: It’s been made better!
Eric Amundson has taken this script and added an easy to use front end – together we have released it under the GPL in the hope it may help more people.

See Eric’s revisions here: http://sewmyheadon.com/2009/mysql-search-replace-tool/

Or contribute yourself as it is now available on LaunchPad: https://launchpad.net/mysql-search-replace

Thanks Eric!

REQUEST:

I have decided to rebuild this script from scratch to allow use of regular expression replacements (wild characters etc), option to preg_quote search strings, exclude certain tables from the search and correctly handle serialised data – I am also wanting to get the script to tell you what it will replace BEFORE it replaces it and allows you to tweak each replacement as needed. This means I need a UI as well! Obviously this is quite a big task but I feel it is worth doing.

I would really appreciate to hear of any other feature requests which I could build in to make it more user friendly/useful, so if you have any ideas please comment below or tweet me @mjdigital – and yes I still plan to give it away for free on GPL.

Although I would appreciate sensible ideas – the moon on a stick is not a viable option!

Ta!