Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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` (
share|improve this question
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
add comment

1 Answer

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.

share|improve this answer
    
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
    
You should accept your own answer. –  RolandoMySQLDBA May 31 '12 at 23:41
    
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 1 '12 at 12:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.