Replace table name prefixes in a mysqldump file

prefix-tables.sh

#!/bin/bash
#
# Replace table name prefixes in a mysqldump file. Test first!


dumpfile=~/my-database.sql
backup=$dumpfile.$(date +%F_%T).orig
oldprefix=
newprefix=staging_

echo "Backing up $dumpfile to $backup..."
cp $dumpfile $backup

echo "Applying new prefix '$newprefix'..."
sed -i "
s/DROP TABLE IF EXISTS \`${oldprefix}/DROP TABLE IF EXISTS \`${newprefix}/
s/CREATE TABLE \`${oldprefix}/CREATE TABLE \`${newprefix}/
s/ALTER TABLE \`${oldprefix}/ALTER TABLE \`${newprefix}/
s/LOCK TABLES \`${oldprefix}/LOCK TABLES \`${newprefix}/
s/INSERT INTO \`${oldprefix}/INSERT INTO \`${newprefix}/
s/-- Table structure for table \`${oldprefix}/-- Table structure for table \`${newprefix}/
s/-- Dumping data for table \`${oldprefix}/-- Dumping data for table \`${newprefix}/
" $dumpfile

echo "Done."