MySQL Global Search and Replace Script

Submitted by Igor on June 25, 2009 – 3:53 am15 Comments

A day will eventually come when your need to find and replace a string of text in your database. You don’t know which row, or which column, or which table. Heck, you may not even know which database. Your options are: spend the rest of the summer hunting down the elusive table cells, or use the weapon of mass replacement described below. Naturally and as usual, you absolutely must back up your database (or databases) before attempting any far-reaching scripted mumbo jumbo.

#!/bin/bash
echo -n "Enter username: " ; read db_user
echo -n "Enter $db_user password: " ; stty -echo ; read db_passwd ; stty echo ; echo ""
echo -n "Enter database name: " ; read db_name
echo -n "Enter search string: " ; read search_string
echo -n "Enter replacement string: " ; read replacement_string

MYSQL="/usr/bin/mysql --skip-column-names -u${db_user} -p${db_passwd}"

echo "SHOW TABLES;" | $MYSQL $db_name | while read db_table
do
	echo "SHOW COLUMNS FROM $db_table;" | $MYSQL $db_name| \
	awk -F'\t' '{print $1}' | while read tbl_column
	do
		echo "update $db_table set ${tbl_column} = replace(${tbl_column}, '${search_string}', '{replacement_string}');" |\
		$MYSQL $db_name
	done
done

#!/bin/bash

echo -n "Enter username: " ; read db_user

echo -n "Enter $db_user password: " ; stty -echo ; read db_passwd ; stty echo ; echo ""

echo -n "Enter database name: " ; read db_name

echo -n "Enter search string: " ; read search_string

echo -n "Enter replacement string: " ; read replacement_string

MYSQL="/usr/bin/mysql --skip-column-names -u${db_user} -p${db_passwd}"

echo "SHOW TABLES;" | $MYSQL $db_name | while read db_table

echo "SHOW COLUMNS FROM $db_table;" | $MYSQL $db_name| \

awk -F'\t' '{print $1}' | while read tbl_column

echo "update $db_table set ${tbl_column} = replace(${tbl_column}, '${search_string}', '{replacement_string}');" |\

$MYSQL $db_name

done

The script will prompt you for username, password, database name, search string, and replacement string. If will then go through every column of every table in search of your text string. And it will replace it with the new string you specified, potentially saving your hours of work and dozens of typos.

Sherra

great script – thank you! Worked without issues and saved me alot of time. Again, thank you.
Pingback: Find, Search, Replace, and Delete In A WordPress Database - WordCast
Newbe

OK, but how do I run the script?
http://www.krazyworks.com admin

Since you are asking, I presume you are running Windows. This script will only work on Linux/Unix computers. You save it to a file like “search_replace.sh”; make it executable by running “chmod 755 search_replace.sh”; and then you run it by typing “./search_replace.sh”. But not on Windows, sorry.
http://druplicity.com Robin van Emden

Works fine, thank you!
Asif

Worked like a charm. Good one.
http://www.hoverfusion.com HoverFusion

This was very helpfull. Worked like a charm. I used this to change entries in my Drupal based web-site, to remove some hard-coded links.
You miss some instructions for newbies, though.

IOne can create a file on linux machine typing
vi search_replace.sh
vi (linux text editor present on many linux boxes) will create a search_replace.sh.
Then you can copy paste the code. Vi commands are here
hans

thank you so much!
Ev dog

Hey guys,
I am working on making an admin panel for a script I created a few months back. One of the things I want to do in the admin panel is allow the users to customize the many different email messages the script sends out. An example of an email message is the following:

$customrequest = “Hello “.$username.”, Your custom request for a “.$req.” has been approved and “.$cost.” “.$currency.” have been removed from your account.”
$customrequestsubject = $title.” – Custom Request Approved!”

MailUpdateRequest($customrequest, $username, $customrequestsubject);

the mail function looks like this:

function MailUpdateRequest($message, $username, $sub){
global $requestemail, $contactemail, $title, $currency;
$request = mysql_query(“SELECT * FROM members WHERE username =’”.$username.”‘”);
$email=mysql_result($request,0,”email”);
// multiple recipients
$to = ”.$email.”;

// subject
$subject = $sub;

// headers
$headers = ‘MIME-Version: 1.0′ . “rn”;
$headers .= ‘Content-type: text/html; charset=iso-8859-1′ . “rn”;
$headers .= ‘To: ‘.$username.’ ‘. “rn”;
$headers .= ‘From: ‘.$title.’ Request Department ‘ . “rn”;

// Mail it
mail($email, $subject, $message, $headers);
}

Essentially, I want to put a text field in the admin panel so any non-programmer could customize this message. Below the text field I will explain the different php variables they can use and how to use them… only issue is I can’t just directly read the mysql fields into the variables or else the php variables get read in as text.

Help is very very much appreciated, financial compensation available if necessary.
Alex

I was just tuning my guitar using harmonics and the D string snapped. I have no idea how to replace it, but I do have a pack of replacement strings. Can anyone please tell me how to replace it? Thanks! And I will choose a best answer if you’re wondering.
Taylor G

I recently purchsed a G-310 and as was playing, one of my strings broke. Now, I’m looking to purchase a replacement set of strings that are the same gauge as the stock strings. So what gauge are they?
PillowMan1234

My saddle is too low and i am looking for a new one, i went to guitarcenter but they didn’t have pre-made saddles, i would have had to shape it myself and i really dont want to do that, so where can i find a replacement?
Agent 47

My shoes from vans had shoe strings with a color specific to them,
And when tying them basically fell apart.
Is it possible to contact the vans company about getting replacement shoe strings for them?
gail C

I have a hunter ceiling fan that is remote controlled but we lost the remote about a month ago. The fan has pull strings but the one for the fan will not work. So what can I do to make the pull string for the fan to work.

Thanks
The wires i see coming from the fan are black, white, green, and a black wire with white stripes.
Milk84

My high E broke on my acoustic and I only have electric replacement strings
Mc L

Nothing pisses me off more than when something breaks even though its brand new.

I bough a whole set of replacement strings for my guitar. Since the 4th one broke. A week later the 4th string breaks again…

I checked the local store, they only had whole sets…