Bug #22072 configurable delayed replication
Submitted: 7 Sep 2006 8:36 Modified: 8 Sep 2006 10:22
Reporter: Anders Henke Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:n/a OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[7 Sep 2006 8:36] Anders Henke
Description:
Hi,

a "true" enterprise-class feature for replication would be a configurable delay:
if something is being broken on the master (e.g. UPDATE/DELETE without any where-statement), that issue normally would replicate to the whole replication
setup, recreating the master from backups can be a very annoyingly hard thing to do (especially if the master purged those binlogs just after that critical update/delete/truncate/drop).

How to repeat:
Sometimes it's also very nice being able to look on how some specific record did look last week - before you've made those critical changes yesterday, which might (or might not) relate to some issue being reported to you. And of course also without reloading that large database from backups.

As mysql replication works asynchronously, this isn't such a problem at all:
we've been running a delayed replication setup for a few years now and 
I've just been stumbling that this feature ought to be solved in the
server, not in some script "around" the server.

We do solve this thing by letting a slave-side script poll the SHOW PROCESSLIST output and run "START/STOP SLAVE SQL_THREAD" according to the offset in the "Time" column for certain state output messages.

Suggested fix:
From MySQL 4.1 on, mysqld knows a "Seconds_Behind_Master'-variable in "SHOW SLAVE STATUS" (which is unluckily "NULL" on a SQL-thread-stopped slave), so the same effect might be even more easily solved directly within the mysql server.

I'd be very glad if this feature could be implemented within the 5.0 branch.
[8 Sep 2006 10:22] Valeriy Kravchuk
Thank you for a reasonable feature request. It looks like a duplucate of bug #21639, already verified.