I have 4 mysql databases that I'm replicating from a master to a slave. In the past, I have renamed one of them because the slave is also a dev server where it has a dev version of that schema with the same name as prod, to make things easier for devs for various reasons. Replication currently goes like this:
db1 => db1_prod db2 => db2 db3 => db3 db4 => db4
(mind you, these aren't the actual names).
This has worked for us in the past because db1 is actually a very unique name & we took care of the renaming by piping the dump through sed, such as:
mysqldump --master-data --databases db1 db2 db3 db4 | sed 's/db1/db1_prod/g;' | pv | pbzip2 -p2 > mysqldump.sql.bz2
Then on the slave we have the rename directives in my.cnf:
This has always worked due to db1's actual unique name. However, my testing has shown that it will not work for the other DB names, because some of the strings actually exist in tables or rows. What we want to do is rename them all to _prod:
db1 => db1_prod db2 => db2_prod db3 => db3_prod db4 => db4_prod
I've also tried:
But when I grep'd the resulting file, it still got more hits than I expected, though I can't say exactly what because the output is a bunch of very long rows, but I know it's more than just the DB name.
Best thing I can think of right now is to dump one at a time without the
--databases flag in mysqldump, then specify manually which DB schema to restore it to, but I'm not sure how to do that while keeping replication running if I'm having to reset the binlog position on the slave for each DB restore.
I did search around a bit & saw this post: Restore mysql database with different name, but my use case is a little different since I have multiple DBs to consider.
Is there a better/easier way to do what I'm trying to do?
I guess I could open the dump file up & edit the 3 lines for each DB each time I need to restore:
-- Current Database: `db1` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `db1`;
and edit those 3 lines for each DB.