MySQL Global Search and Replace Script
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.
16 Comments »
1 Pingbacks »
-
[…] KrazyWorks – MySQL Global Search and Replace Script […]
![wyryr1sn52gntzn65v4fpz5lc6v[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/wyryr1sn52gntzn65v4fpz5lc6v1-80x80.jpg "Plundering Facebook Photo Albums")
![clfud46mxf2t5zph2l04zlpgl1d[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/clfud46mxf2t5zph2l04zlpgl1d1-80x80.jpg "Scraping a Web Page in Bash")
![DSCF5361[1]](https://i1.wp.com/www.krazyworks.com/wp-content/uploads/2020/10/DSCF53611-80x80.jpg "Processing Videos with ffmpeg and Lightroom")
![Cat-Devouring-A-Bird-Pablo-Picasso[1]](https://i0.wp.com/www.krazyworks.com/wp-content/uploads/2020/10/Cat-Devouring-A-Bird-Pablo-Picasso1-80x80.jpg "Searching Twitter")
![91ebozy1e3b138waujy28agrngy[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/91ebozy1e3b138waujy28agrngy1-80x80.jpg "Generating Random Text Files for Testing")
![iwoau71okwxqh4jv3k4ook32qps[1]](https://i2.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/iwoau71okwxqh4jv3k4ook32qps1-80x80.jpg "Finding Cron Jobs")
![5gtysva4wkc8mzc1k3e66vaycxx[1]](https://i1.wp.com/www.krazyworks.com/wp-content/uploads/2021/08/5gtysva4wkc8mzc1k3e66vaycxx1-80x80.jpg "Understanding Memory Utilization in Linux")
![sy2zeb1vp2h8itlxfbbylw0jrm0[1]](https://i0.wp.com/www.krazyworks.com/wp-content/uploads/2019/12/sy2zeb1vp2h8itlxfbbylw0jrm01-80x80.jpg "Validating HTTPS Cache Peers for Squid")
![Photo-2019-10-07-14-39-03_0266[1]](https://i1.wp.com/www.krazyworks.com/wp-content/uploads/2019/11/Photo-2019-10-07-14-39-03_02661-80x80.jpg "Verifying SNMP Connectivity on Multiple Hosts")
great script – thank you! Worked without issues and saved me alot of time. Again, thank you. :-)
OK, but how do I run the script?
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.
Works fine, thank you!
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.shvi (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
thank you so much!
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.
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.
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?
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?
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?
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.
My high E broke on my acoustic and I only have electric replacement strings
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…
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!