MySQL Bugs: #2222: mysqldump -A -F creates new binlogs for each db dumped

Bug #2222	mysqldump -A -F creates new binlogs for each db dumped
Submitted:	27 Dec 2003 9:54	Modified:	3 Feb 2004 10:52
Reporter:	Stefan Hinz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	Windows (Windows 2000)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
Two comments on http://www.mysql.com/doc/en/mysqldump.html#comments
refered to "the -A -F problem". The commenters were complaining about
the fact that for each database dumped, the logs are flushed, and thus
a new binlog is written.

I'd regard that as a feature, and added a note to the -F option
description. I also removed those comments :-)

I asked Paul about this; here's his opinion:

I'd regard it as a feature, too, because it helps make restore
operations that require the binlogs more reliable.  The different
binlogs serve essentially as checkpoints.  At least, that's how I'd
understand it.

Serg, however, stated this:

I personally would take it as a bug.
At least mysqldump --help says:
...
  -F, --flush-logs    Flush logs file in server before starting dump.
...

And I see no reason for the curremt behaviour.

How to repeat:
Issue mysqldump -A -F and look at the binlogs.

Suggested fix:
Make mysqldump -A -F _not_ flush logs for each db dumped.

I updated mysqldump --help to reflect the actual usage.

The reason for flushing logs between databases is that mysqldump locks one database at time, so it has to flush logs per database to get a consistent snapshot.

And it locks one database in time because
1. we don't want to make ALL tables read-only for the duration of the WHOLE backup
2. we don't want to open ALL tables at once (and LOCK TABLE opens it) because mysqld can simply get short of file descriptors and it's slow.