Bug #11124 mysqldump -delete-master-logs breaks replication
Submitted: 7 Jun 2005 6:33 Modified: 24 Aug 2005 22:23
Reporter: Olle Hallin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.1.8 OS:Linux (Mandrake Linux 10.1)
Assigned to: CPU Architecture:Any

[7 Jun 2005 6:33] Olle Hallin
Description:
Summary
--------------
mysqldump breaks replication in certain circumstances.

I have a backup scheme which each night makes an incremental backup by stepping the master binlog. Every sunday a complete backup is done by dumping all databases, and resetting the master binlog .001.

The slaves follow the nightly stepping of the binlog without problems. The weekly reset to binlog .001 breaks replication however.

Details
----------
Nightly command (monday-saturday): mysqladmin flush-logs

Weekly command (sunday): mysqldump --single-transaction --flush-logs --master-data=2 --delete-master-logs --all-databases | gzip > all-databases.sql.gz

Work-around: 
--------------------
Issue the following series of commands in the slave:

mysql> stop slave;
mysql> change master to master_log_file='<my-master>-bin.001', master_log_pos=4;
mysql> start slave;

Configuration:
--------------------
Master: mysql 4.1.8
Slave: mysql 4.1.11
Table type: InnoDB

Regards,
Olle Hallin

How to repeat:
mysqldump --single-transaction --flush-logs --master-data=2 --delete-master-logs --all-databases | gzip > all-databases.sql.gz
[2 Jul 2005 14:18] MySQL Verification Team
I wasn't able to reproduce it with latest 4.1 on the master and on the slave.
What is the error message on the slave?
[15 Jul 2005 12:17] Olle Hallin
Excerpt from the error log:
050715 14:10:59 [Note] Slave SQL thread initialized, starting replication in log 'fantomen-bin.000002' at position 4, relay log './guran-relay-bin.000001' position: 4
050715 14:10:59 [Note] Slave I/O thread: connected to master 'repl@fantomen.int.hit.se:3306',  replication started in log 'fantomen-bin.000002' at position 4
050715 14:13:17 [ERROR] Error reading packet from server: Could not open log file (server_errno=1236)
050715 14:13:17 [ERROR] Got fatal error 1236: 'Could not open log file' from master when reading data from binary log
050715 14:13:17 [ERROR] Slave I/O thread exiting, read up to log 'fantomen-bin.000007', position 5672
050715 14:13:56 [ERROR] Error reading relay log event: slave SQL thread was killed
[24 Aug 2005 22:23] Hartmut Holzgraefe
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

--delete-master-logs has the same effect as the "RESET MASTER" SQL command

slaves would be likely to miss statements and get out of sync if they would
just switch to the new files as they may not have read all content from the 
old ones before these have been deleted

maybe you are looking for the "PURGE MASTER LOGS" SQL command instead?

  http://dev.mysql.com/doc/mysql/en/purge-master-logs.html
[16 Jan 2006 16:56] Eric Martel
Since I'm living the same problem, I would like to add that it may not be a bug, but this behavior of --delete-master-logs is an annoyance at least.

The documentation nowhere says nor warns about the fact that a --delete-master-logs can break the replication: I added a note myself about that on the mysqldump man page after reading the present bug report. Could the behavior of --delete-master-logs be changed so that, for example, it won't delete the current active binary log so that the slaves can follow automatically with the rotation of logs?

Or, else, could a "--purge-master-logs" option be added to mysqldump?