MySQL Bugs: #35157: mysqldump should use FLUSH TABLES NO_WRITE_TO

Bug #35157	mysqldump should use FLUSH TABLES NO_WRITE_TO_BINLOG when --master-data is used
Submitted:	8 Mar 2008 0:22	Modified:	23 Jul 2008 0:56
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	all	OS:	Any
Assigned to:	Chad MILLER	CPU Architecture:	Any
Tags:	--master-data, flush tables, master-data, mysqldump, NO_WRITE_TO_BINLOG

Description:
mysqldump should use FLUSH TABLES NO_WRITE_TO_BINLOG when --master-data is used.

When using the --master-data option with mysqldump, mysqldump still uses a FLUSH TABLES command.  However, this statement gets replicated to the slave(s), which causes the slave(s) to block while the FLUSH tables command completes.

It is not necessary for the slaves to issue the FLUSH TABLES, so it would be nice if when --master-data is specified, mysqldump issued a FLUSH TABLES NO_WRITE_TO_BINLOG, instead of just a FLUSH TABLES.

How to repeat:
N/A

Suggested fix:
N/A

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45384

ChangeSet@1.2562, 2008-04-14 17:43:08-04:00, cmiller@zippy.cornsilk.net +2 -0
  Bug#35157: mysqldump should use FLUSH TABLES NO_WRITE_TO_BINLOG \
  	when --master-data is used
  
  When using the --master-data option with mysqldump, mysqldump uses 
  a FLUSH TABLES command.  However, this statement got replicated to 
  the slave(s), which caused the slave(s) to block unnecessarily while
  the FLUSH tables command completed.
  
  Now, if the master-data option is set to one of the two "on" modes,
  then use the "LOCAL" qualifier to ensure that it's not replicated.

Queued to 5.1 and 6.0 -build trees.

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha

Two questions.

1) re:

"
Now, if the master-data option is set to one of the two "on" modes,
  then use the "LOCAL" qualifier to ensure that it's not replicated.
"

What do you consider the "off" mode?

2) In MySQL 6.0, mysqldump has a --dump-slave option that is
similar to --master-data, and it too causes a FLUSH TABLES
statement to be sent to the server.  Should LOCAL also apply
for that option as well?

Paul,

1) It depends on the value of option 'master_data', which may be unset, or "commented sql" or "effective sql".  

   "This causes the binary log position and filename to be appended to the "
   "output. If equal to 1, will print it as a CHANGE MASTER command; if equal"
   " to 2, that command will be prefixed with a comment symbol. "

The default, zero, is off.

2) I'll check.

Paul, 

2) There's only one common site of "FLUSH TABLES", and this patch covers it.

Noted in 5.1.25, 6.0.6 changelogs.

mysqldump now adds the LOCAL qualifier to the FLUSH TABLES statement
that is sent to the server when the --master-data option is enabled.
This prevents the FLUSH TABLES statement from replicating to slaves,
which is disadvantageous because it would cause slaves to block while
the statement executes.

LOCAL needs to be added not only when --master-data is enabled, but also when --dump-slave is enabled. I have created Bug#38303 to report this.