MySQL Replication: struggling with replicate-rewrite-db to change the database name

Question

I have my master database with the one table in it, products (will have more later, this is just dev).

OS: CentOS 6.4

I have set it up as the master for replication using this:

In /etc/my.cnf:

server-id = 1
binlog-do-db=product_database
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

On the master I created the user with this command:

GRANT REPLICATION SLAVE ON *.* TO 'hcp_slave'@'%' IDENTIFIED BY 'password_here';

Then on the slave:

server-id = 2
master-host=HOST_IP_ADDRESS
master-connect-retry=60
master-user=hcp_slave
master-password=PASSWORD
replicate-do-db=product_database
#The below line is what causes issues
replicate-rewrite-db=product_database->product_database2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Before I added the "replicate-rewrite-db" line (and with it removed) the replication works perfectly between the servers with the same database name. However, I need it to go into a database with a different name, for testing purposes, "product_database2."

From everything I have read in the mysql manual and on a few random forums (plus stackoverflow) I think I am using the replicate command properly, but clearly, I am not, so I would greatly appreciate a little help in figuring out where my issue lies, or if I am trying to go about this the entirely wrong way!

For those curious, what I am actually trying to do, in case I have an XY problem going on here:

I am trying to create a master database to feed products to a handful of websites instead of having to update them all manually. I figured the best way to do this would be to create 1 master database and have all the sites configured as slaves to automatically pull products from it.

Edit:

I tried commenting out the line 'replicate-do-db' in the slave configuration file, and at least now it is giving some indication of knowing that product_database2 exists, but it is in the form of an error:

Last_Error: Error 'Error on rename of './product_database/asdf.frm' to     './product_database2/asdf.frm' (Errcode: 2)' on query. Default database: 'product_database2'. Query: 'RENAME TABLE `product_database`.`asdf`

Answer 1

On the slave side you need to set

replicate-do-db=product_database2

because you have rewritten db name like product_database->product_database2.

Answer 2

I faced the same problem regarding database naming. My solution:

In the my.cnf file:

replicate-rewrite-db="product_database->product_database2"
replicate-do-db="product_database2"