Mysql – Renaming multiple databases on a MySQL slave

master-slave-replicationMySQLrenamereplication

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:

replicate-rewrite-db='db1->db1_prod'

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:

s/`db1`/`db1_prod`/g

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?

Edit:

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.

Best Answer

  1. MySQL Slave is used as Development Server! Better not to.

  2. Otherwise, let the replication happen with the same db / table names. And let the db used for development be named differently - which you can handle in the program code itself (one dbname for dev and another for production). You can have an environment section which names the db and several other required too for dev and production.

  3. If at all you prefer this...assuming that the dbname changes and not table names...create individual mysqldump per db and then mysql insert each dump separately into your preferred db.

Related Question