I am attempting to setup a stand by database using replication. I have a table "abc"
on the master server that I wish to replicate to the slave server as "abclive". In
other words, I want the updates to be written into a database of a different name on
the slave.
I thought this would be easy, and I tried using the following config options in the
slave my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
master-host=10.0.0.1
master-user=repl
master-password=somepass
master-port=3306
server-id=2
replicate-wild-do-table=abc.%
replicate-rewrite-db=abc->abclive
I grabbed a snapshot of the abc database and copied to to the datadir, renaming it to
abclive. I started the master/slave and it didn't work- updates were not being
propogated into abclive and the error log reported no problem. I also tried the
following options:
replicate-do-db=abc
replicate-rewrite-db=abc->abclive
but it didn't work and then I read a note which said that if you use inserts like
"insert into abc.field ..." where you named the database in the insert (which we do)
then you needed to use the replicate-wild-do-table command. But neither seemed to
have the desired effect. Is there an issue with using inserts with database names AND
replication with the rewrite-db ???
I went back and took out the rewrite line in the first config set above and moved the
slave database back to "abc", and replication worked fine. I just can't get it to
replicate with a new name, abclive.
Can someone please tell me how to accomplish what I'm trying to do? Our standby
database will be on a machine which contains daemons that are ready to go. To set
these up, I need to test them and they use the original "abc" database name. So
during the setup, I need to have a writable database to test the setup and must have
the replicated one go to a different name. Once the machine is ready to go live, the
test database just needs dropped and the replicated standby can be renamed. If I
can't solve the rewrite problem, then I'll be forced to setup 2 instances of MySQL
simply for this reason.
Any hints are appreciated...
Byron Guernsey
V3 Networks, Inc.
