What are incremental backups?
Incremental backup is a backup that only contain data that has changed since the previous backup, not including all the data in the database.
Why are incremental backups needed?
Incremental backups allow performing backups much more frequently, as they are much smaller in size. However, to restore from an incremental backup, not only the incremental backup file is required, but also the entire preceding chain of backups.
How does SqlBak perform incremental backups?
In order to create an incremental backup, SqlBak performs binary log file backups.
What are binary logs?
Binary logs are files into which MySQL Server writes all data change transactions.
Typically, these files are stored in /var/lib/mysql/ on Linux or C:\ProgramData\MySQL\MySQL Server 5.6 Data on Windows. The list of all MySQL Server binlog files is stored in a special binlog.index file.
The transactions are recorded in the last binlog file for all databases at once in binary format. However, they can be converted to SQL statements. Re-executing these SQL statements will restore the database to any point in time.
How to enable binary logs
If the binlogs are not enabled, you can enable them using the following instructions:
How to Enable Binary Log on Linux
How to Enable Binary Logging on Windows
How does SqlBak backup binary logs?
- SqlBak performs a
FLUSH BINARY LOGS;operation; this leads to a new binlog file creation - SqlBak remembers the name of the newly created binlog file, this file will be the beginning of the next incremental backup
- SqlBak reads the list of available binlog files from binlog.index file
- SqlBak copies all binlog files that have appeared since the previous incremental backup to an archive
We recommend creating one backup job for all databases. This is due to the fact that all transactions for all databases are written in one file. Parallel launch of incremental backups in different jobs will lead to duplication of data in backups and increase usage of free space in the destinations.
SqlBak must have access to the directory where the binlog files are stored. That means that the SqlBak app must be installed on the same server where MySQL Server is installed.
When does SqlBak perform a full backup instead of an incremental one?
- After adding incremental backups to a backup plan. To restore data from incremental backups, a full backup created with special options is required. By default, SqlBak does not use them. Therefore, the first full backup will be executed with additional parameters.
- When data schema (tables or columns) have been changed. Transactions that change the data schema are also written to binlog files. These transactions are written at the DBMS level, and they cannot be correctly retrieved to restore a database under a different name. To understand that the data schema has been changed, SqlBak performs a data schema backup and remembers its checksum. If the checksum changes, then SqlBak runs a full backup.
- If a previous run of a backup job failed. If a backup job fails, it leads to a backup chain violation, so SqlBak creates a full backup to ensure recovery without missing data.
- After restoring any database via SqlBak. All SQL statements that are involved in a database restore process get in binlog files, which greatly increases its size. Thus, to save space, SqlBak performs a full backup