MySQL Bugs: #5366: incremental backup

Bug #5366	incremental backup
Submitted:	2 Sep 2004 9:30	Modified:	30 Sep 2008 15:41
Reporter:	Mickael Besson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S4 (Feature request)
Version:	6.0	OS:	Linux (any OS)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
It will be usefull to have possibility to back up only data which has been modified/inserted/(deleted) since the last backup.

So, the backup will lock less DB and will run more quickly.
example : 4 backup/day instead of 1 big permit to restore DB more precisely in time (crash at 13h -> restore DB state of 12h whereas with only one backup/day : restore DB of the day before (:-|)).

-> for MyIsam tables
-> for InnoDB tables if possible !

How to repeat:
 

Suggested fix:
You've written for v4.1.4 :
"
 * Added --start-datetime, --stop-datetime, --start-position, and
   --stop-position options to mysqlbinlog. These make point-in-time
   recovery easier.
"

Does it seem that incremental backup run ?

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Bonjour Mickael,
Yes we have incremental backups. But I just found the manual does not mention it clearly!
Instead of backing up the table, you backup the changes which happened to the table since last backup. Such changes are stored in the binary log http://dev.mysql.com/doc/mysql/en/Binary_log.html.
So, what you have to do is run FLUSH LOGS when you do the full backup (mysqldump and mysqlhotcopy have options to do that), and then your backup will correspond to, say, binlog named something-bin.036. Imagine 6 hours later you want to do an incremental backup: run FLUSH LOGS, that will create something-bin.037, and then you simply have copy something-bin.036 to a safe place. something-bin.036 is your incremental backup which contains the 6 hours of changes. Later when you want to restore, you'll first restore the full backup, then "re-play" the incremental backups on it, using mysqlbinlog
http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html. 6 hours later you want to do another incremental backup, so you FLUSH LOGS and save something-bin.037, etc.
I'll add the same explanation to our manual.
Merci de nous avoir signalé le problème!

Thank you for your explanation.

Now, what about integrate this functionnality into your tool 'MySQL Administrator' which is very simple and practical ?! It will avoid DBA to use command lines and to manipulate, manually, a lot of files ... which make user lost (!).

So, this feature request is adressed to MySQL Administrator development.
Thank's

Ok, I'm assigning this to the MySQL Administrator development team, for consideration for their TODO.
It's true that in the future, more and more things will be integrated into our GUI programs.