Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to start doing regular backups of my databases, and I'd like them to be incremental; that is, only backing up the tables that have been changed. I use MySQL Workbench, and I've used the export function in the past, which works well enough, but I can't seem to find a way to make it incremental and not re-backup the entirety of every database every time. I've got about 90 schemas in total. Any help at all is much appreciated. Thanks!

share|improve this question

2 Answers 2

Not having worked much in MySQL Workbench, your question prompted me to downloaded it to look around.

It looks to me that the export panel is just a GUI to issue a mysqldump command. mysqldump is a program that can only be used to get a snapshot of the current state of the database. Aka, there is no comparison feature to get 'latest changes since the last export'.

That being said, MySQL can do point-in-time backups by turning on the binary_log. Basic gist for taking backup is:

  • mysql> FLUSH LOGS;
  • Copy the binlogs from the filesystem for your incremental changes.
  • To recover, replay using mysqlbinlog command line utility as described in article above.

I have heard of a GUI tool that is used to manage backups, but YMMV on it (never used it myself). The tool is Zmanda and from the page it supposedly:

Schedule full and incremental backups of your MySQL database.

Hope this helps.

share|improve this answer
1  
Binary logs aren't really an incremental backup. They're a way to do a point-in-time recovery with replay or roll-forward. But a true incremental backup is a different thing. The tools that can actually do this are Percona XtraBackup and MySQL Enterprise Backup (formerly InnoDB Hot Backup). –  Baron Schwartz Dec 6 '11 at 22:59
    
@Baron Thanks for clarifying the difference, I've adjusted the answer to be 'point-in-time' instead of incremental using binlog. –  Derek Downey Dec 7 '11 at 14:02

To be frank, if you are using a GUI tool as your backup strategy, you are doing it wrong. Backups need to be scripted, automated, and regularly tested or else you might as well not have them. If your database is very small (a few gigabytes), there is no point in doing incremental backups: backup the entire database with mysqldump and use binary logs in statement format for point in time recovery, or using a slave, shut down the slave an archive the binary files (cold backup).

If you are using InnoDB and really need incrementals, there is Xtrabackup from Percona or innodbhotbackup.

Xtrabackup is a great tool which I highly recommend. It will perform true incremental backups with minimal impact to the server.

share|improve this answer
1  
" if you are using a GUI tool as your backup strategy, you are doing it wrong." I would disagree with this. If the GUI allows you to schedule your backups, then it meets the requirements of 'automated'. Using a GUI doesn't prevent you from testing either. –  Derek Downey Dec 7 '11 at 13:52
    
Does MySQL Workbench allow you to schedule regular backups without leaving MySQL Workbench running on some client computer? –  Aaron Brown Dec 10 '11 at 18:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.