Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a mysql dump in a .sql file. There are many databases in it with DROP DATABASE and USE commands before each database.

On the running server are databases with the same names. I don't want to overwrite them. How can I import all these databases with a prefix and without dropping any existing databases/tables? But existing databases with the prefix should be overwritten.

share|improve this question
up vote 2 down vote accepted

Let's initialize some variables for ease of use:


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):

| 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;
  eval "sed ${ARG_SED} ${FILE_MYSQLDUMP}";
done < <(cat /var/tmp/existing_databases)

And once more to replace the USE statements:

while read DB_NAME;
  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.

share|improve this answer
wow thank you for this complete answer! – Pascal Rosin Feb 1 '13 at 17:03

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.