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
Triage: D5 (Feature request)

[8 Mar 2008 0:22] Chris Calender
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
[14 Apr 2008 21:43] Bugs System
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.
[24 Apr 2008 14:57] Chad MILLER
Queued to 5.1 and 6.0 -build trees.
[1 May 2008 10:05] Bugs System
Pushed into 5.1.25-rc
[1 May 2008 10:06] Bugs System
Pushed into 6.0.6-alpha
[14 May 2008 2:01] Paul Dubois
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?
[22 Jul 2008 15:16] Chad MILLER
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.
[22 Jul 2008 16:22] Chad MILLER
Paul, 

2) There's only one common site of "FLUSH TABLES", and this patch covers it.
[23 Jul 2008 0:56] Paul Dubois
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.
[23 Jul 2008 1:03] Paul Dubois
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.