Let's initialize some variables for ease of use:
DB_PREFIX=yourprefix_
FILE_MYSQLDUMP=/path/to/mysqldump.sql
Now get a list of existing databases (excluding tables that start with the prefix you don't mind overwriting and the information_schema and mysql tables which probably shouldn't be in mysqldump.sql anyway):
echo "SHOW DATABASES;" \
| mysql \
| egrep -v "^(Database|information\_schema|mysql|${DB_PREFIX}(.*))$" \
> /var/tmp/existing_databases
Now loop through entries in existing_databases
and replace the CREATE DATABASE statements in $FILE_MYSQLDUMP
:
while read DB_NAME;
do
ARG_SED="-i 's/CREATE DATABASE \`"${DB_NAME}"\`/CREATE DATABASE \`"${DB_PREFIX}${DB_NAME}"\`/g'";
eval "sed ${ARG_SED} ${FILE_MYSQLDUMP}";
done < <(cat /var/tmp/existing_databases)
And once more to replace the USE statements:
while read DB_NAME;
do
ARG_SED="-i 's/USE \`"${DB_NAME}"\`/USE \`"${DB_PREFIX}${DB_NAME}"\`/g'";
eval "sed ${ARG_SED} ${FILE_MYSQLDUMP}";
done < <(cat /var/tmp/existing_databases)
Finally you can import your revised database dump with mysql < ${FILE_MYSQLDUMP}
... and, providing there aren't any tricky instances of USE or CREATE DATABASE in your database dump's records (yikes) you should have overwritten databases matching yourprefix_
and created prefixed versions of the other databases in the dump file.
Planning to do this regularly? Check out the Advanced Bash-Scripting Guide - it's covers almost everything you need.
Credit + upvotes to Escape a string for sed search pattern for the eval
method for handling sed
arguments.