1

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.

1 Answer 1

2

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.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .