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 – 100’s of database records across over60+ different tables 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!
MJ7: aka Mark Jackson @ MJDIGITAL. Digital Consultant - primarily digital marketing, web app design and development, multimedia and SEO techniques. But will happily consult on most IT matters.