Have you ever needed to string replace your entire SQL database, but not been able to figure out how to do it without dumping the whole database, string replacing the file and reimporting it? Well I did. But today I nutted out a solution for this, which you can see in the bash script below 🙂 Just replace the database name, user, password, and the search/replace text and run it via bash string-replace-db.sh. Then many minutes later (or hours if you have a huge database), it will have string replaced EVERYTHING! To state the obvious, you should backup the database before trying this 😉

string-replace-db.sh

#!/bin/bash

DATABASE='dbname'
USER='dbuser'
PASS='dbpasswd'
SEARCH='somethingtosearch'
REPLACE='whattoreplaceitwith'

# Fetch all tables
TABLES=$(mysql -u $USER -p$PASS $DATABASE -e 'show tables' | awk '{ print $1}' | grep -v '^Tables' )

for t in $TABLES
do
	# Fetch all columns
	COLUMNS=$(mysql -u $USER -p$PASS $DATABASE -e "show columns from $t" | awk '{ print $1}' | grep -v '^Field' )

	for c in $COLUMNS
	do
		mysql -u $USER -p$PASS $DATABASE -e "update $t set $c = replace($c, '$SEARCH', '$REPLACE')"
	done
done