0

I have the following backup scheme on my server:

Every day I run "mysqladmin flush-logs".

Weekly I run "mysqldump --single-transaction --flush-logs --master-data=2 --delete-master-logs > ..."

Since recent, I've added a slave server with normal master-slave replication.

The question: may my backup scheme break the replication, if it deletes the bin-logs while not all changes have been synced out? Should I then delete bin-logs manually, after some decent time has passed?

Dmitri Pisarev
  • 1,114
  • 12
  • 31

1 Answers1

2

From this closed bug report;

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

http://dev.mysql.com/doc/refman/5.0/en//reset-master.html

RESET MASTER deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. This statement is intended to be used only when the master is started for the first time.

In other words, yes, it may (and probably will) break your replication.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Thanks! I've thought so! Then how should I delete old bin-log files and for how long should I keep it? – Dmitri Pisarev May 20 '13 at 13:09
  • Would something like this suffice?: 'find /mnt/db/mysql_storage/mysql-bin.* -mtime +14 -exec rm -f {} \;' – Dmitri Pisarev May 20 '13 at 13:20
  • 1
    @Dmitri As long as you're sure that all your slaves are up to date and have read/replicated the logfile you're removing, you should be ok (although backing your logs up is almost never wrong) 14 days would seem safe as long as a slave never gets hardware failure and goes offline for that long. – Joachim Isaksson May 20 '13 at 13:47