This was a request yesterday on a mailing list about how to I block DROP statements from running on a slave. There were responses including MySQL could not do this, or try other replication technologies however I thought I would share how you can easily do this on a slave.
You simply deny access on the slave. It is as simple as changing the MySQL privileges for the user(s) in question to remove DROP or ALTER capabilities.
Of course this will break replication rather then simply ignore the statement however I suspect that is the intended goal anyway. The first statement that uses the table will fail regardless. With appropriate monitoring of replication you should know in about 15 seconds. (Hint: If you don’t monitor replication or monitor it frequently, DO SO NOW!)
These leads to the question, what if the statements are legitimate? There are several solutions. Enable the specific privileges on the slave when the DBA …[Read more]