Follow Me
mysqldump diff MySQL Differential Backup Shell Script
mysqldump diff is a shell script that I wrote to take some of the pain out of differential MySQL backups.
After changing the appropriate values, this will dump your database daily and create a unified diff patch so you’ll only need to keep the original mysqldump intact and patch files describing the subsequent differences between each future dump. It is a simple form of version control for MySQL database dumps without the overhead of managed methods. Although results will differ based on on the frequency of record-modification and the interval between backups, using this method on weekly backups (over a 2-month period), my patches were each roughly a tenth of the size of the original dump and of course, the benefits are exponential as time goes on while allowing you to increase the frequency many-fold while still saving space.
mysqldump diff
Alternative MySQL Backup Methods
Generally, I’d advocate the following standardised (read: supported) methods but the clients’ databases I use this for are not interested in redundancy, clusters or cloud servers so the following aren’t ideal:
- mysqldump and gzip would have me running out of storage space and/or bandwidth quickly
- MySQL binary log is less flexible and also recording more than needed
- MySQL Replication (master/slave) would mean running another instance: unwanted costs and maintenance
I do so :mysqldump quote-names add-drop-table add-locks no-data routines exednted-insert -ulogin -ppass default-character-set=latin1 -f dbname > dbname_structure.sqlcopy /Y /V dbname_structure.sql D:\BackUp\dbname\dbname_structure.sqlfor /F tokens=1-4 usebackq delims=. %%1 in (`date /t`) do set mydate=%%4_%%3_%%2_%%1ren E:\BackUp\dbname\dbname_structure.sql dbname_structure%mydate%.sqlmysqldump quote-names add-locks no-create-info -ulogin -ppass default-character-set=latin1 -f dbname > dbname_data.sqlcopy /Y /V dbname_data.sql D:\BackUp\dbname\ren D:\BackUp\dbname\dbname_data.sql dbname_data%mydate%.sql
I had no idea mysqldump was available for Microsoft Windows. Your comment does not display correctly but regardless of that, it creates a full backup split into two files (structure and data). The point of my script is that it’s differential so that you do not have duplicated data: You only store the changes in data between each backup. For example:
With a database of 50MiB, the first dump would be 50MiB then every subsequent day might only add 1MiB of data whereas with daily, full dumps: Day 1 would be 50MiB, day 2 would be 51MiB et cetera. That will get unmanagable (and costly) very quickly.
Which distribution of diff do you use? I have gnu diffutils and the “-q” option you specify causes the .sql.diff to only have contents such as “Files dbdump-2013-03-20.sql and dbdump-2013-03-21.sql differ”.
I’m also using GNU diffutils 3.2 but the -q option is for quiet reporting i.e. if there is a difference, not returning the actual differences. I can’t believe I posted this with that still in there…
Thanks, Jason!