I would like to replicate all the databases from a MySQL master to a slave used for archiving data older than 30 days. It isn't common for this data to be accessed so I would like it removed from the master. I only need to replicate the database table structure without any content. But only two tables (tablea and tableb) out of the 10 tables need to be created in the database structure.
The table content will be imported using an archive script that is using pt-archiver which is why I don't need the content replicated to the slave. However when the main database is removed from the master it should also remove the database from this archive slave. Is this possible using replication?
Best Answer
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.
FEDERATED
tables that connect back to the master. One of these points to information_schema.tables and the other points to information_schema.schemata.