MySQL Find and Replace Script - Blogs

As part of my job I have to occasionally roll Drupal websites from our development environment across to our live environment. This always gets me frustrated due to the dev version running from a different address and thus, I have to search and replace any occurrences to the development url with the live url.

Well, today I got fed up with doing this manually and decided that in the length of time it would take me to replace the occurrences in the latest site to be rolled out would actually be longer than it would take me to write a script.

The psuedo code 

The psuedo code for the the script is very straight forward, as it replaces what I would do using phpMyAdmin.

Connect to the database
Loop through each table
    foreach each field
        Build Search SQL
        Build Replacement SQL
    end field foreach

    While the Search SQL returns results execute the Replacement SQL

End table Loop

Simple.

The resulting script does not only work for Drupal databases, but it could be used anywhere where this task is required. However, in the wrong hands it could be quite dangerous, so make sure you back up you databases prior to execution.

Usage #1

mysql_find_raplace database search replacement

This will return a list of tables that contained the search and how many iterations it took to complete the replacement.



Usage #2

mysql_find_raplace database search

This with return a summary of all the tables and how many rows contain the search string.
This was added while testing the script and I thought I'd leave it in

Download

You can download the script by clicking here.