Bug #36429 Filter replicated SQL statements by statement type
Submitted: 30 Apr 2008 15:14
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.0, any OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: replication filter

[30 Apr 2008 15:14] Hartmut Holzgraefe
Description:
Feature request: 

allow configuration of commands that should *not* be executed by the replications SQL thread, e.g: "do not execute DROP TABLE or DROP DATABASE", this would allow to use the slave for quick disaster recovery if tables or databases are dropped by accident. 

"delayed replication" (as requested as a feature elsewhere) would also sort of help with this situation but would require more manual effort to restore the last known state of the table(s) and won't help if the mistake is only detected after the configured replication delay interval ...

How to repeat:
Do a DROP TABLE on the master, then discover that it was the wrong table or that it shouldn't have been dropped in the first place -> chances that the slave(s) have already replicated the erroneous DROP are high ...

Suggested fix:
Allow to optionally exclude statements like DROP TABLE, DROP DATABASE, maybe also TRUNCATE and others, from execution in the replication SQL thread
[30 Apr 2008 15:29] Andrei Elkin
A possible solution for that is in implementation the replication trigger feature.
A trigger could be defined having several on-phase actions, like:
 
 on receive (from the master)
 on execute (upon reading from the relay log) pre- and post- time
 on log     (when replication event has been processed and is about binlogging)