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!
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).
Xtrabackup is a great tool which I highly recommend. It will perform true incremental backups with minimal impact to the server.
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 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
mysqlbinlogcommand 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.