Networking

Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.

Applications

Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.

Data

Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.

Monitoring

Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » MySQL, Scripts

MySQL Global Search and Replace Script

Submitted by on June 25, 2009 – 3:53 am 16 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

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.

Print Friendly, PDF & Email

16 Comments »

  • Sherra says:

    great script – thank you! Worked without issues and saved me alot of time. Again, thank you. :-)

  • Newbe says:

    OK, but how do I run the script?

    • admin says:

      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.

  • Asif says:

    Worked like a charm. Good one.

  • 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 says:

    thank you so much!

  • Ev dog says:

    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 says:

    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 says:

    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 says:

    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 says:

    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 says:

    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 says:

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

  • Mc L says:

    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…

  • FRANCESC MANEL ORTIZ BECERRA says:

    I’m trying to us this script, it seems great, but I’ve got an error message when I use it…

    ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”mdl_backup_logs’ set message long = replace(‘message long’, ‘192.168.0.8’, ‘{re’ at line 1

    I’m abolutely new in mysql syntax, but I think there is a problem with quotes (singles and doubles or so) in this line:

    echo “update $db_table set ${tbl_column} = replace(${tbl_column}, ‘${search_string}’, ‘{replacement_string}’);” |
    $MYSQL $db_name

    db_user=”moodleuser”, db_pass=”moodle”, db_name=”moodleuser”, search_for”192.168.0.8″, replace_with=”192.168.0.2″

    My systems is a Ubuntu 14.04 upgraded, mysql-server 5.5.41.

    Someone can help me?
    Thanks a lot!

1 Pingbacks »

Leave a Reply

%d bloggers like this: