5

Been "slaving" on this issue all day.

I have a master/slave setup that works, but when I try to replicate to a different database name on the slave it fails. This is what my.cnf looks like:

server-id=101
report-host=Slave01

replicate_do_db='DB1'
replicate-rewrite-db=DB1->DB2

replicate-ignore-table=DB2.SOME_TABLE_NAME

If I take out the replicate-rewrite-db line, it works perfectly and replicates the DB1 database from the master to the slave. The master log position I am using on the slave is after the CREATE DATABASE DB1 call on the master. After that log position, the tables are created and data imported on the master. I don't configure and start the slave until after I create the DB2 database.

Any ideas?

Update: Saw this in the mysql error log:

120531 15:48:19 [Note] Slave I/O thread: connected to master 'slave_user@master_server:3306',  replication started in log 'mysql-bin.000001' at position 107
120531 15:48:20 [ERROR] Slave: Error 'Unknown database 'DB1'' on query. Default database: 'DB1'. Query: 'CREATE TABLE `ANOTHER_TABLE` (
Ed Manet
  • 522
  • 1
  • 5
  • 16
  • 1
    +1 on the question as well because I am sure you are not the first one to have this situation based on mixing replication filters. – RolandoMySQLDBA May 31 '12 at 22:58

2 Answers2

7

I received a hint in a comment but the user has since deleted it.

The trick is to not use replicate-do-db and only use replicate-rewrite-db:

#replicate_do_db='DB1'
replicate-rewrite-db=DB1->DB2

Commenting out the replicate_do_db line in my.cnf fixed it for me.

Thanks to the mystery user who pointed me in the right direction, whoever you are.

Ed Manet
  • 522
  • 1
  • 5
  • 16
  • I may just be guessing on this one, but if database DB1 did not exist on the Slave, that's probably why you got that error. You could have ran `CREATE DATABASE db1;` on the Slave and restarted replication . That would probably have solved it as well, but your solution is actually simpler and does not require a dummy database. +1 !!! – RolandoMySQLDBA May 31 '12 at 22:57
  • The only problem I'm seeing now is that the tables from the master.mysql are replicating into slave.DB2. I'm pretty sure I can filter them out though. – Ed Manet Jun 01 '12 at 12:26
0

An important note: You must to add the name of rewrited database. Add this line in your my.cnf:

replicate-do-db=DB2

So your config file must be like this finally:

server-id=101
report-host=Slave01

replicate_do_db       =  DB2
replicate-rewrite-db  =  DB1->DB2

replicate-ignore-table=DB2.SOME_TABLE_NAME

I tested it and it works

Omidreza Bagheri
  • 101
  • 2