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