MySQL Bugs: #8368: mysqldump needs --slave-data option

Bug #8368	mysqldump needs --slave-data option
Submitted:	8 Feb 2005 14:32	Modified:	6 Mar 2010 18:43
Reporter:	Mikael Fridh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	mysql-4.1	OS:	Any (ALL)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2006_11_02

Description:
mysqldump has been bothering me for a while.
The --master-data is a good option. But unless you are daisy-chaining slaves it's only useful if you can actually have your master locked up for a while which I can't.

Many times I've been doing the manual:
SHOW SLAVE STATUS - record the position
mysqldump...
import to new slave
CHANGE MASTER TO - positions from SHOW SLAVE STATUS.

Which is more or less a cloning of the slave.

How to repeat:
run mysqldump --help and feel disappointed that the nice --slave-data option is missing.

Suggested fix:
add --slave-data option to mysqldump which clones the slave you dump from instead of adding a link to the chain.

The idea is in the patch I'm attaching which is only tested on 4.0.23a and the patch would not work on for example 4.1 because I'm using hard coded positions in the slave data array.

(I just duplicated and modified the --master-data stuff)
Very useful so far!

All I need to do is set up new mysqld, install the privilege tables and run the dump+import in one operation from the old slave to the new slave.

I think it's an S3 "minor loss of service"

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/internals/22782

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/internals/23343

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/internals/24974

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/internals/25010

I will take a look at this patch and see what Monty would like to have completed.

I have applied the last version of the patch, but am having some 'memory not freed' messages. In non-debug mode, the test gives me:

Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

TEST                            RESULT
-------------------------------------------------------
rpl_mysqldump                  [ pass ]   
-------------------------------------------------------

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished
All 1 tests were successful.
WARNING: Got errors/warnings while running tests. Please examine
/home/patg/mysql-build/mysql-5.0/mysql-test/var/log/warnings for details.

When I run it in debug, I get:

TEST                            RESULT
-------------------------------------------------------
rpl_mysqldump                  [ fail ]

Errors are (from /home/patg/mysql-build/mysql-5.0/mysql-test/var/log/mysqltest-time) :
Warning: Not freed memory segments: 2
Warning: Memory that was not free'ed (472 bytes):
            16 bytes at 0x00805a2f0, allocated at line  175 in 'my_error.c'
           456 bytes at 0x008059060, allocated at line  197 in 'my_alloc.c'
Maximum memory usage: 1083366 bytes (1058k)
Warning: Not freed memory segments: 2
Warning: Memory that was not free'ed (472 bytes):
            16 bytes at 0x00805a2f0, allocated at line  175 in 'my_error.c'
           456 bytes at 0x008059060, allocated at line  197 in 'my_alloc.c'
Maximum memory usage: 1083366 bytes (1058k)
Warning: Not freed memory segments: 2
Warning: Memory that was not free'ed (472 bytes):
            16 bytes at 0x00805a2f0, allocated at line  175 in 'my_error.c'
           456 bytes at 0x008059060, allocated at line  197 in 'my_alloc.c'
Maximum memory usage: 1083366 bytes (1058k)
Warning: Not freed memory segments: 2
Warning: Memory that was not free'ed (472 bytes):
            16 bytes at 0x00805a2f0, allocated at line  175 in 'my_error.c'
           456 bytes at 0x008059060, allocated at line  197 in 'my_alloc.c'
Maximum memory usage: 1083366 bytes (1058k)
mysqltest: Result length mismatch
Maximum memory usage: 591521 bytes (578k)
(the last lines may be the most important ones)
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/rpl_mysqldump.result      2005-10-14 22:29:47.000000000 +0300
--- r/rpl_mysqldump.reject      2005-10-15 00:57:24.000000000 +0300
***************
*** 126,131 ****
--- 126,135 ----
  
  ### Error to supply both --master-data and --slave-data ###
  mysqldump: You can't use both --slave-data and --master-data at the same time.
+ Warning: Not freed memory segments: 1
+ Warning: Memory that was not free'ed (456 bytes):
+          456 bytes at 0x008059060, allocated at line  197 in 'my_alloc.c'
+ Maximum memory usage: 457 bytes (1k)
  STOP SLAVE;
  GRANT REPLICATION SLAVE ON *.* TO replssl@'%' REQUIRE SSL;
  CHANGE MASTER TO MASTER_SSL=1, 
-------------------------------------------------------
Please follow the instructions outlined at
http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html
to find the reason to this problem and how to report this.

Aborting: rpl_mysqldump failed in default mode. To continue, re-run with '--force'.

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished

I cannot find what is the problem. In the trace file, I just see:

| | | safe: sf_malloc_count: 2
| | | safe: Memory that was not free'ed (472 bytes):
| | | safe:     16 bytes at 0x00805a2f0, allocated at line  175 in 'my_error.c'
| | | safe:    456 bytes at 0x008059060, allocated at line  197 in 'my_alloc.c'
| | | safe: Maximum memory usage: 1083366 bytes (1058k)

This memory was allocated in:

>dbConnect
| >_mymalloc
| | enter: Size: 16
| | exit: ptr: 0x805a2f0
| <_mymalloc
info: mysql_connection inited 80579e0

static int dbConnect(char *host, char *user,char *passwd)
{
  char buff[20+FN_REFLEN];
  DBUG_ENTER("dbConnect");
  if (verbose)
  {
    fprintf(stderr, "-- Connecting to %s...\n", host ? host : "localhost");
  }
  mysql_init(&mysql_connection);
  DBUG_PRINT("info", ("mysql_connection inited %lx", &mysql_connection));

So, I don't see where this is coming from, or why it isn't freed. If I run this on the regular mysqldump.test, it passed, no memory issues, so I know it's the new code, but looking at the new code, I don't see anything that isn't being improperly allocated or any cases of it not being freed.

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/internals/31124

I'd love to see this in a release soon!

Wow, I forgot that I had been the one working on getting this patch applied and pushed, and became a user and realised that I needed something like this too.

I have a new fix that I'll work out getting a patch to MySQL described at:

http://capttofu.livejournal.com/4267.html

I made this change against 5.1, so I'll have to backport this to 5.0. 

I would assign this bug to me if I could.

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/34313

ChangeSet@1.2599, 2007-09-15 09:17:39-04:00, patg@my.patg.net +4 -0
  BUG #8368
  
  "mysqldump needs --slave-data option"
  
  Added this option, named as "--dump-slave". The purpose of this option is to be
  able to produce a dump from a slave used for making backups of the master. Originally,
  dumping from the main master was fine, but as more data accumulated, the dump process
  would take over 30 minutes, locking up the master database hence website for 30 minutes.
  A slave dedicated to producing backups was the answer, but I needed a dump that could be 
  used to restore a slave instantly and in order to do that, it has to have three things 
  contained in the dump:
  
  1. "STOP SLAVE;" at the beginning
  2. "CHANGE MASTER TO ...<the master - info from 'show slave status'>"
  3. "START SLAVE;" at the end
  
  These options in this changeset contain this.
  
  --stop-slave adds "STOP SLAVE" to the beginning of the dump and "STOP SLAVE" 
  to the end of the dump.
  
  --include-host gives the user the option to have the host explicitely added
  to the "CHANGE MASTER TO ..." line.
  
  --dump-slave adds the "CHANGE MASTER ..." to the dump representing not the slave's
  master binlog info, but the slave's master's info from "SHOW SLAVE STATUS"

I took Patch from Patrick and reworked it. 

Change in notes:
BUG #8368

"mysqldump needs --slave-data option"

Added this option, named as "--dump-slave". The purpose of this option is to be
able to produce a dump from a slave used for making backups of the master. Originally,
dumping from the main master was fine, but as more data accumulated, the 
dump process would take over 30 minutes, locking up the master database hence website
for 30 minutes.  A slave dedicated to producing backups was the answer, but I needed a
dump that could be used to restore a slave instantly and in order to do that, it has to
have three things contained in the dump:

1. "STOP SLAVE;" at the beginning
2. "CHANGE MASTER TO ...<the master - info from 'show slave status'>"
3. "START SLAVE;" at the end 

These options in this changeset contain this.

--apply-slave-statements adds "STOP SLAVE" to the beginning of the dump and "START SLAVE"
to the end of the dump.

--include-master-host-port gives the user the option to have the host explicitely
added to the "CHANGE MASTER TO ..." line.

--dump-slave adds the "CHANGE MASTER ..." to the dump representing not
the slave's master binlog info, but the slave's master's info from "SHOW SLAVE STATUS"

Finished review of Patrick's work. Recommented and fixed issues in strcmp(). Also reordered options, fixed test case, and removed duplicate issues around "e" value for options.

What versions did this get pushed to? My guess: 6.0.5, and not any earlier series?

Pushed into 6.0.4.

Noted in 6.0.4 changelog.

Three options were added to mysqldump make it easier to generate a
dump from a slave server. --dump-slave is similar to --master-data,
but the CHANGE MASTER statement contains binary log coordinates for 
the slave's master host, not the slave itself.
--apply-slave-statements causes STOP SLAVE and START SLAVE statements
to be added before the CHANGE MASTER statement and at the end of the
output, respectively. --include-master-host-port causes the CHANGE
MASTER statement to include MASTER_PORT and MASTER_HOST options for
the slave's master.

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/89306

2911 Magne Mahre 2009-11-04
BUG #8368 "mysqldump needs --slave-data option"

Added this option, named as "--dump-slave". The purpose of this option is to be
able to produce a dump from a slave used for making backups of the master. Originally,
dumping from the main master was fine, but as more data accumulated, the dump process
would take over 30 minutes, locking up the master database hence website for 30 minutes.
A slave dedicated to producing backups was the answer, but I needed a dump that could be

used to restore a slave instantly and in order to do that, it has to have three things
contained in the dump:

1. "STOP SLAVE;" at the beginning
2. "CHANGE MASTER TO ...<the master - info from 'show slave status'>"
3. "START SLAVE;" at the end

These options in this changeset contain this.

--stop-slave adds "STOP SLAVE" to the beginning of the dump and "STOP SLAVE"
to the end of the dump.

--include-host gives the user the option to have the host explicitely added
to the "CHANGE MASTER TO ..." line.

--dump-slave adds the "CHANGE MASTER ..." to the dump representing not the slave's
master binlog info, but the slave's master's info from "SHOW SLAVE STATUS"
@ client/client_priv.h
Added OPT_SLAVE_DATA to client_priv.h
@ client/mysqldump.c
* Added --dump-slave option (name per Brian)
* Added --stop-slave to print "STOP SLAVE;" into the dump
* Added --include-host option to include "MASTER_HOST=..." and "MASTER_PORT=..."
to the dump since unlike --master-data, the host can't be assumed to be
the local host
* Added do_start_slave and do_stop_slave to stop the slave sql thread upon
start of the dump process, and to start the slave sql upon finish of dump process -
to keep the log information frozen during this time.
* Added do_show_slave_status for obtaining slave information needed to compose
"CHANGE MASTER ..." output to the master of this slave.
* Added necessary long options and defines required for new options

Backported to 5.6.0  (6.0-codebase revid: 2476.763.6)

Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 5.6.0 changelog.

Already fixed in 6.0.x.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.

I was wondering if this new feature of the mysqldump utility will also included a method to specify the following as part of a CHANGE MASTER info:
MASTER_USER
MASTER_PASSWORD

This is something that would round the rest of CHANGE MASTER parameters...

Thanks.