Here’s a handy little script which re-names all the MySQL tables in a database. I wrote this because Dreamhost added a prefix to my Wordpress installation. When I wanted to load the database on my local machine, I no longer wanted the prefix.

#!/bin/bash
## A script which re-names all the tables in a specified database.
## by Owen Griffin
##
## Usage
## -----
## ./rename_tables.sh <database name> <user name> <password> <prefix>
##   - database name = MySQL database
##   - user name = MySQL username
##   - password = Password for specified username
##   - prefix = Portion of text in every table name which needs to be replaced

function usage() {
  sed -n 's/^##\([^$]*\).*/\1/p' $0
}

if [ ! $# == 4 ]; then
  usage
  exit
fi

LOCAL_DATABASE=$1
LOCAL_USER=$2
LOCAL_PASSWORD=$3
TABLE_PREFIX=$4
for FROM in $(echo "SHOW TABLES;" | mysql --column-names=FALSE -u $LOCAL_USER -p$LOCAL_PASSWORD $LOCAL_DATABASE); do
  TO=$(echo $FROM | sed -e "s/$TABLE_PREFIX//g")
  echo "Renaming $FROM to $TO"
  echo "RENAME TABLE $FROM TO $TO" | mysql --column-names=FALSE -u $LOCAL_USER -p$LOCAL_PASSWORD $LOCAL_DATABASE
done