Quick Review: Boxee Box
December 27, 2011 – 12:22 am | 3 Comments

Some of the technical issues with Boxee Box could have been fixed if the dev team was paying more attention to addressing the bugs rather than adding “features” of dubious value. In the final analysis, for the price and ease of use, Boxee Box is the best in its class and price range. You just need to be mindful of its limitations and buy it in hope of future improvements to its usability.

Read the full story »
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 am6 Comments
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.

Popularity: 4% [?]

Related posts:

  1. Passing MySQL Commands from Shell Script
  2. Useful SysAdmin Scripts: Search-and-Replace
  3. Find and Replace in MySQL
  4. A simple text string search script
  5. Exporting spreadsheet data to MySQL
  6. Creating table indexes in MySQL
  7. Using GNUPlot to chart MySQL data
  8. MySQL LOAD DATA Syntax
  9. The correct MySQL GRANT syntax
  10. Generating complex SQL queries with shell scripts

6 Comments »

Leave a comment!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.