I have master with 2 slaves (4 but other 2 not relevant). One slave is full replication, ability to switch to be master if needed. Second slave was designed to replicate certain tables and leave out key data. Purpose of that slave is to use it for refreshing a development database at any time so developers have what is in production to work on, but without customer sensitive data. It is using a number of replicate-ignore-table.
For whatever reason, I usually think of that as ignoring replication of DATA to the table, not table structure changes. I'm obviously incorrect. Structure changes are also not replicated as per visual evidence and "not to replicate any statement that updates the specified table". (Maybe I'm too used to thinking "update" is table data, not table_schema).
Other than removing replicate-ignore-table lines and then running an event very often to truncate a list of tables, what other way might there be to have the table structure changes (including trigger changes to a table) be replicated but to NOT replicate the data being put in the database? Triggers for each table isn't a solution as this db needs to be as much a copy of what is in master as possible with regards structure and various customer configurations (which changes frequently).
I have everything automated from doing daily auto sanitize and dump, transfer to file storage. For refresh, just run a script to fetch latest dump, do backup of existing developer db, load prod version and all is almost good (need to update some unreleased changes to the db). And then now realize replicate-ignore-table ignores structure too.
Suggestions?
Best Answer
The blackhole engine type will work for what I need. We are using row based replication and can't change to statement based but the warnings are not being logged so that won't be an issue.
As a side note, one thing I found somewhere suggested show variables like 'have_blackhole_engine' and nothing came up (nor for show variables like '%blackhole%' or show global variables like '%blackhole%') but I decided to create a table with the engine and it showed up just fine.