I'm replicating a prod MySQL database to an analytics one. I need to drop several tables with sensitive data from the analytics and also redact several columns of several remaining tables, replacing NULLable fields with NULLs. How can I set up triggers on the slave to achieve this?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Triggers do not fire on the slave in row-based replication, even if they are defined, because the rows in the other tables affected by the triggers on the master are replicated as row events, and if the triggers were allowed to fire on the slave, that would not work.
If you have entire tables that need to be eliminated on the slave, you can alter those tables to use the
If you are in a position to rearrange column order on tables, MySQL supports the slave having fewer columns than the master in row-based mode, as long as the columns they do have are all identical by definition and ordinal position in the table, as I discussed here. If moving the sensitive columns to the right side of the table is practical, you can then
But this also requires attention down the road.
Probably the simplest solution is to use permissions on views that you essentially create for the purpose of enforcing the access rights to the underlying tables. Views, by default, are executed with the permissions of the user who defined them, not the user who invokes them... so a view can allow a user to access columns in a table that aren't otherwise accessible to that user, if the table and columns are accessible to the
If you create a view with
If none of these are suitable alternatives, is one more alternative approach that can be used if part of the problem is that you can't even allow the sensitive data to be sent to or stored on the other server, because of its physical location or other security concerns... but it requires a third MySQL server in the middle (between existing master and slave) and is somewhat fragile because every