MySQL Master <=(Slave,Master)=> Slave configuration

Question

Sorry for the wonky title if it got you confused.

I want to know if a server can be a slave and master at the same time. Our problem is that we have lots of mobile units that need to be synced to the master but they only need 6 out of the 100s of tables on the master. All the extra tables serve no purpose on the slave except for delaying synchronization and adding data costs.

Desired Configuration:

Server A: Master to Server B.

Server B: Slave to Server A, Master to Server C.

Server C(n): Slave to Server B.

OR

Server A: masterSchema, slaveSchema.

slaveSchema is a sync'd subset of masterSchema.

Server B(n): Slave to Server A.slaveSchema

If the answer is no would anyone have any alternate solutions to propose. We're trying to avoid having to sync the different schemas/databases manually as that can get real ugly real fast. We have a lot of critical live data flying around so both performance and integrity are of concern.

Answer 1

Actually both configurations are possible.

Remarks to your ideas

For configuration 1, just set up the first Master->Slave replication. See that you should activate --log-slave-updates on the slave so that the slave creates an appropriate binary log for the propagation of the next slave in the chain. Then set up the new slave with the previous slave as a master. This should be straight forward.

For configuration 2 you can use --replicate-do-table (or --replicate-wild-do-table) or --replicate-ignore-table options on the slave. This alters the behaviour of the slave executing received binary logs. Another way is to use --binlog-do-db or --binlog-ignore-db on the master. This alters the logged binary statements on the master directly. There are some pitfalls thought when using. You might want to read the manual yourself (basically it is unsafe to use the --replicate...--db or --binlog...--db options, because they may have unintended side effects).

These problems are all mentioned in this blog post of Percona too. Since there are no --binlog-do-table options, one does not have the chance to safely restrict the written statements on the master. In this setting the whole binary log would need to be transferred to all n slaves.

Another solution

They do propose a different solution for cases when there should become n slaves propagated, but only with a partial binary log. Setting up a replication chain like you did in configuration 1, but setting the storage engine to all tables on the first slave to BLACKHOLE. BLACKHOLE is just /dev/null, so all written data will be immediately lost. This allows to replicate everything from the first master to the first slave. Then the replication rules from configuration 2 (--replicate-ignore-table) are applied on the slave. All other clients are slaves from this "filter-slave". So the first slave is just responsible for filtering the binary log and propagating this log to (maybe) a lot of slaves. Thus the full binary log must be transferred only once, and not to all of the n slaves.

Maybe the last configuration is something you want to take into account.

Answer 2

A slave can of course also act as a master. All you have to do is enable Binary Logging on the desired "Master-Slave" Server and connect additional slave servers to it instead of the "Master-Master" server.

You can set the --replicate-wild-do-table=db_name.tbl_name option on the slaves to specify specific tables to be replicated to the slave.

More info: RTM and RTM