MySQL Bugs: #18073: selective enabling/disabling of binary log

Bug #18073	selective enabling/disabling of binary log
Submitted:	8 Mar 2006 15:11	Modified:	23 May 2006 10:02
Reporter:	Rob Blick	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I suggest adding a mechanism to selectively enable and disable binary logging on a per-database or per-table basis without shutting down the database.  Oracle supports this on a per-table basis (e.g, ALTER TABLE tablename NOLOGGING).  This is especially important when doing bulk loads.  Loading 20GB of data into a table generates a ton of binary log, which is often unnecessary since the table can be reloaded from the original dataset, if needed.  Sure, one can use the binlog_ignore_db and binlog_do_db options, but this has the major disadvantage of require that the database be shutdown and restarted.

How to repeat:
N/A - feature request

Suggested fix:
Ideally, a runtime ALTER TABLE command should be implemented (similar to Oracle).  But that's difficult, so parhaps a session variable could be added that temporarily shuts off binary logging on a per-table basis.  At a minimum, allow one to enable binlog_ignore_db without having to bounce the database.  I realize this isn't a trivial request, particularly since the binlog is used for replication, in addition to offering point-in-time recovery.

Thank you for a reasonable feature request.

Is quite doable in case of row-based replication. Nearly impossible to do for statement-based.

This is basically a request for making the --binlog-do-db and friends available as server variables. This is quite doable, but might require some latches to ensure that no conflicts occur when updating the list.

There is at least one worklog for that WL#2516.

See also bug#39993 bug#48079