Bug #36429 Filter replicated SQL statements by statement type

• Submitted: 30 Apr 2008 15:14
• Reporter: Hartmut Holzgraefe
• Status: Verified
• 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)