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.
Best Answer
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):
Now loop through entries in
existing_databases
and replace the CREATE DATABASE statements in$FILE_MYSQLDUMP
:And once more to replace the USE statements:
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 matchingyourprefix_
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 handlingsed
arguments.