Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

1 Answer 1

up vote 2 down vote accepted

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.

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

Your Answer

 
discard

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.