If you catch yourself looking into available backup solutions for MySQL and your MySQL data remains unchanged over time, it might be time to look into differential backups.
Differential Backups Explained
- Differential backups require less storage space than full backups;
- Making a differential backup is often faster since only the data that has changed is backed up;
- Only two backups (a full backup and the most recent differential backup) are needed for recovery.
With that being said, differential backups also have disadvantages:
- Differential backups require a full backup to be recovered;
- Restoring a differential backup is slower than restoring a full backup since both the full backup and the last differential backup is required for data recovery;
- Recovery can fail if one or more of the data sets is incomplete or corrupted.
Differential Backups in MySQL
To take a differential backup, simply take a full backup and then take backups of the changes to the data since the last full backup was performed. To restore the backup taken up to a certain time, simply restore the full backup and restore the differential backup on top of it.
To take a full backup using mysqlbackup (the tool is covered in-depth in this blog: see MySQL Enterprise Backup), run:
$ ./mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/backup.mbi --backup-dir=/home/backup-tmp backup-to-image
--userdefines your database user;
--passworddefines the prompt for the password associated with the MySQL user;
--hostdefines the host of your database (usually 127.0.0.1);
--backup-imagedefines the location and the filename of the single-file backup;
--backup-dirdefines the location of an empty folder where some temporary files will be stored;
backup-to-imagedefines a wish to produce a single-file backup.
After taking a full backup, take a differential backup – make a backup of the changes to the data since the last backup was performed. To see an available list of mysqlbackup commands, see the MySQL documentation.
To restore a differential backup, know that MySQL Enterprise Backup treats differential backups as special cases of incremental backups. In its view, a differential backup is an incremental backup that has a full backup as its base. To restore a differential backup, combine all of your incremental backups with the original full backup and you should be good to go! Do note that full backups should be taken again after a specified period of time – after taking a new full backup, older incremental backup data can be thrown away.
Differential backups in MySQL are special cases of incremental backups that have a full backup as its base – the main advantages of differential backups are that they require less storage space compared to full backups and the creation of differential backups is often faster due to the fact that only data that has changed is being backed up. To create a differential backup, create an incremental backup specifying a full backup as the base of your incremental backup. If you encounter any issues, take a look at our older blog post about MySQL Enterprise Backup.