The Problem

The server option log_slave_updates needs to be enabled on both m1 and m2

The Solution

Please add the following in /etc/my.cnf on both m1 and m2

[mysqld]
log_slave_updates

then run this on both m1 and m2

service mysql restart

The Reason

Let's use your example above

• m1 is Master of m2
• m1 is Master of s3
• m2 is Master of m1

Here is what happens when you run INSERT INTO mydb.mytable ... on m1

• m2 executes INSERT
• m2 records INSERT into the current binary log along with server_id=23
• m1 retrieves INSERT from m2 via I/O Thread
• m1 records INSERT via I/O Thread into lastest relay log
• m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
• m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
• m1 executes INSERT

OK, all well and good. But, why doesn't s3 get to receive anything? Because log_slave_updates is not enabled, m1 does not save the INSERT and the original server_id posting the INSERT into its own binary logs.

If log_slave_updates was enabled on m1, now follow along your example and let's see what should happen:

• m2 executes INSERT
• m2 records INSERT into the current binary log along with server_id=23
• m1 retrieves INSERT from m2 via I/O Thread
• m1 records INSERT via I/O Thread into lastest relay log
• m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
• m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
• m1 executes INSERT
• m1 records INSERT into the current binary log along with server_id=23
• s3 retrieves INSERT from m1 via I/O Thread
• s3 records INSERT via I/O Thread into lastest relay log
• s3 retrieves INSERT from relay log via SQL Thread and examines its server_id
• s3 sees that the server_id of the INSERT is 23 which is not the same as s3's server_id (21)
• s3 executes INSERT

This fully explains why s3 never sees anything from m2

It isn't possible with replication as has been correctly pointed out, since that's not how replication works, nor can it be configured to work that way. Replication either does or does not handle any given table (depending on configuration) but when replication does handle a given table, the data is replicated and replication depends on the rows in the tables starting out and remaining identical.

What I'm hearing you say is that you want some of the same schemas and tables on the 2nd server as exist on the master, but they will be populated via external means, and you want those tables to go away when they are dropped from the master.

Here is a high-level view of how this could be accomplished via automatic means using only things built-in to MySQL.

Obviously this is a dangerous operation if not carefully written since we're talking about the automated dropping of tables and databases, so... caveat lector.

• enable the event scheduler on the archive machine
• enable the federated storage engine on the archive machine
• within another schema you create solely for the purpose of this exercise, define 2 FEDERATED tables that connect back to the master. One of these points to information_schema.tables and the other points to information_schema.schemata.
• write a stored procedure that selects from the archive machine's information_schema.schemata table, left join the federated schemata table on catalog_name, schema_name where schema_name not in ('mysql','information_schema','performance_schema','any','other','schemas','you want not','to be deleted','for sure')
• take values from the resulting rows, which will be all of the databases that exist on the local machine but not on the master... and, using dynamic sql, drop those databases from the archive machine.
• do the same thing with the information_schema.tables table, which will identify tables you can drop.
• use the event scheduler to automate the running of this stored procedure. Don't schedule it too often, perhaps only once a day, because selecting from the information_schema.tables table can be a resource-intensive operation. MySQL gathers stats when you do this that can be costly especially in terms of I/O.