| Bug #24733 | RESET MASTER run before dump with --delete-master-logs | ||
|---|---|---|---|
| Submitted: | 30 Nov 2006 20:41 | Modified: | 11 Jul 2007 17:02 |
| Reporter: | Mark Leith | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S2 (Serious) |
| Version: | All | OS: | Any (All) |
| Assigned to: | Damien Katz | CPU Architecture: | Any |
| Tags: | bfsm_2006_12_07 | ||
[14 Dec 2006 9:37]
Guilhem Bichot
I'll add here my comments in case the bugfixer is interested: It's not obvious to solve this problem. For example, Mark Leith wrote: delete logs after doing the dump and before releasing locks. But in "mysqldump --single-transaction --master-data" (the only free online backup we propose, if using InnoDB), the dump is done after releasing locks (it's done via a consistent read in InnoDB). So the suggestion is not possible in this case. What could be done by mysqldump is: take locks flush logs remember the name of the new log do dump (if this is a lock-based dump) release locks do dump (if this is a consistent-read-dump) --- yes, dump succeeded --- do PURGE MASTER LOGS TO up to the new log excluded. That would not be exactly like a RESET MASTER as the log number would not go down to .000001, but I can't see this as a critical problem (if replication is used, doing a RESET MASTER on the master without doing a RESET SLAVE on slave or CHANGE MASTER on slave, is bound to fail the slave anyway, while PURGE MASTER LOGS is safe, if the slave is currently connected). If I were the DBA, I would not even trust my suggestion: it's not because mysqldump succeeded that the dump is really successful. For example the script could do: mysqldump > dump.sql and there is no fsync() of dump.sql of course, so if the machine crashes right after the success of mysqldump, a piece of dump.sql may still be in OS buffers and thus be lost and thus the backup will be incomplete. Though the binlogs were deleted already. To protect against that, I could use --result-file, if mysqldump added a fsync(result_file) before calling it a success. And the same for --tab. Advanced DBAs may however want to add additional checks before calling it a success (wait for the dump to be collected by the centralized backup software managing tapes, and only after that succeeds, delete binlogs). They should not use --delete-master-logs, they can use --flush-logs and --master-data=2, this will flush the log under the lock, and print, in a commented line, the coordinates of the binlog at the moment of the flush (so they can craft a PURGE MASTER LOGS command to run at will later). I can see a reason why people implemented deletion before dump. It is for the case where no locks on tables are taken during the dump (no --lock-tables, no consistent read, just dumping tables one by one). By deleting the log before dumping, the log which remains on the machine at the end of the dump, contains possibly more than it should, but not less. I mean, a statement found in this binlog may be already reflected in the dump, which makes a recovery-by-binlog difficult but not impossible (for example, if all queries are REPLACE using absolute column values (no "SET a=a+1"), it's fine to apply queries to a database already containing their effects). While if logs are deleted after the dump, the log which remains will miss queries which are not reflected in the dump, making recovery-by-binlog impossible.
[23 May 2007 18:53]
Damien Katz
Ok, it seems to make this solid, we'll need a new server side code. The approach I want to take is basically as Guilhem Bichot and Mark Leith advocate: take locks flush logs remember the name of the new log do dump (if this is a lock-based dump) release locks do dump (if this is a consistent-read-dump) --- yes, dump succeeded --- do PURGE MASTER LOGS TO up to the new log excluded. What can happen is the logs can roll over again, between the steps of flush and getting the name of last log file. It needs to be a single operation: flush the logs, roll over to new file and return the name of last flushed log. I think this will require a new option on the FLUSH LOGS command, to return that last log file name. I'm waiting to hear from others more knowledgable to see if this is the correct approach.
[24 May 2007 20:45]
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/27314 ChangeSet@1.2497, 2007-05-24 16:44:52-04:00, dkatz@damien-katzs-computer.local +1 -0 Bug #24733 RESET MASTER run before dump with --delete-master-logs fixed by using flush logs, dumping, then doing PURGE MASTER LOGS TO 'binfile', instead of deleting the log files at the beginning. NOTE: previously the delete-master-logs would reset the log names back to filename.00001. Now the trailing number doesn't get reset. This may need to be documented.
[25 May 2007 8:16]
Guilhem Bichot
comments sent by mail
[27 May 2007 15:50]
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/27412 ChangeSet@1.2501, 2007-05-27 11:50:10-04:00, dkatz@damien-katzs-computer.local +1 -0 Bug #24733 RESET MASTER run before dump with --delete-master-logs fixed by using flush logs, dumping, then doing PURGE MASTER LOGS TO 'binfile', instead of deleting the log files at the beginning. NOTE: previously the delete-master-logs would reset the log names back to filename.00001. Now the trailing number doesn't get reset. This may need to be documented.
[6 Jun 2007 16:54]
Bugs System
Pushed into 5.1.20-beta
[6 Jun 2007 16:57]
Bugs System
Pushed into 5.0.44
[11 Jul 2007 17:02]
Paul DuBois
Noted in 5.0.44, 5.1.20 changelogs. Incompatible change: When mysqldump was run with the --delete-master-logs option, binary log files were deleted before it was known that the dump had succeeded, not after. (The method for removing log files used RESET MASTER prior to the dump. This also reset the binary log sequence numbering to .000001.) Now mysqldump flushes the logs (which creates a new binary log number with the next sequence number), performs the dump, and then uses PURGE MASTER LOGS to remove the log files older than the new one. This also preserves log numbering because the new log with the next number is generated and only the preceding logs are removed. However, this may affect applications if they rely on the log numbering sequence being reset.
Description: The --delete-master-logs option for mysqldump causes the RESET MASTER to occur *before* the dump is taken: if (opt_single_transaction && start_transaction(mysql)) goto err; if (opt_delete_master_logs && do_reset_master(mysql)) <--RESET MASTER here goto err; if (opt_lock_all_tables || opt_master_data) { if (flush_logs && mysql_refresh(mysql, REFRESH_LOG)) goto err; flush_logs= 0; /* not anymore; that would not be sensible */ } if (opt_master_data && do_show_master_status(mysql)) goto err; if (opt_single_transaction && do_unlock_tables(mysql)) /* unlock but no commit! */ goto err; if (opt_alltspcs) dump_all_tablespaces(); <-- Dump here Therefore, should the mysqldump fail whilst dumping - you would have an inconsistent backup. How to repeat: mysqldump with --delete-master-logs Suggested fix: RESET MASTER and SHOW MASTER STATUS *after* the dumpfile has been taken and before removing locks.