1

This is a pictorial view of what I have:

Master 1<----->Master 2
|                     |
|                     |
V                     V
Slave 1         Slave 2

The masters are never written to at the same time, rather Master 2 is a standby server. Both Masters have log-slave-updates turned on in my.cnf.

If a table is created in a replicated DB on Master 1, it is present on Master 1, Master 2 and Slave 1. It is NOT present on Slave 2.

In the same way, if a table is created in a replicated DB on Master 2, it is present on Master 1, Master 2 and Slave 2. It is NOT present on Slave 1.

Why is this the case? Is it a limitation of MySQL in how it stops master/master replicated servers from creating replication loops if both have log-slave-updates turned on?

Is there a way this can work?

Improve this question
4
  • 1
    There is no such limitation in MySQL. Replication propagates changes through practically infinite amount of tiers. Did you check the server-ids? Do you have any replication or binlog filter?
    – Károly Nagy
    Commented May 25, 2016 at 7:26
  • server-id values are all different. I do have a binlog_do_db containing only one database. That database is the one the table is being created in.
    – phil-lavin
    Commented May 25, 2016 at 7:29
  • 1
    That filter could easily be the problem. binlog_do_db has some limitations of what it can recognise (dev.mysql.com/doc/refman/5.7/en/…). Try to remove that and see if it works the way you want it. If you do need some filtering use replication-do-db on the slave instead. It's more advantageous from many perspective.
    – Károly Nagy
    Commented May 25, 2016 at 7:38
  • I changed binlog_format from MIXED to STATEMENT and that seems to have solved the problem. Going to do some more research...
    – phil-lavin
    Commented May 25, 2016 at 7:41

2 Answers 2

Reset to default
1

To answer my own question, the "fix" is to change binlog_format to STATEMENT. It was previously MIXED.

Not entirely sure of the reason behind this - MySQL should be intelligent when using MIXED mode about what is replicated as statement and what is replicated as row.

Improve this answer
1

Since you have binlog/replicate filtering, this is probably what happened:

USE non_replicated_db;
CREATE TABLE replicated_db.table_name ...;

The filtering works on the USE, not on the db qualification in the statement.

(PS, you are using what is probably the best layout for standard replication: dual-master-single-writer, plus slaves hanging off both. If the Masters are in different physical locations, it is even better.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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