How to Take an Incremental Backup in MySQL Step by Step?

For any business, a database backup is an essential component. But when the MySQL database grows, so do its backups. Running a backup operation every time may pile up and begin to require more storage space than is necessary. That’s where incremental backup helps. To take an incremental backup in MySQL, there are two ways: one is the manual approach, and the other is a MySQL backup tool.

Only the data that has changed since the last backup is included in incremental backups, not the entire database. But remember, you will need the whole chain of backups in this situation to restore the data.

Incremental backups are quicker, smaller, and have little to no load on the database.

Which Method to Use for Creating Backups?

Key Points	Manual Approach	MySQL Backup Tool
Full backup	Required	Supported
Incremental changes	Needs binary logs	Built-in incremental backup option
Scheduling	Requires separate setup	Built-in schedules feature
Storage	You manage the destination	Local, NAS, and supported cloud/remote storage
Retention	Requires log-management policies	Built-in retention
Monitoring Backups	Requires checking logs/jobs	Backup history and status features
Compression	Requires additional configuration/tools	Already Supported
Encryption	Needs additional configuration/tools	AES-256-GCM Encryption
Email alerts	Requires additional setup	Supported Fully
Restore	Full backup + binary logs	Simple restore workflow
Multiple databases	Managed through MySQL tools	Supported through the application

 

Manually Creating MySQL Incremental Backup

MySQL supports incremental data recovery through its binary log. The binary log records changes made to the database, and those changes can later be applied after restoring a full backup. This is the basis of MySQL point-in-time recovery.

Follow this workflow:

1. Check Whether Binary Logging Is Enabled: First, connect to your MySQL server, then check the binary-log setting. You can verify the MySQL binary log directory path
2. Check the current binary log position; run:

SHOW BINARY LOG STATUS\G

3. The binary-log file and position identify a point in the server’s change history. MySQL provides SHOW BINARY LOG STATUS for obtaining the current binary-log status.
4. You don’t necessarily need to manually record these coordinates if you use mysqldump —source-data, because the dump can include the binary-log information needed for recovery.
5. Create the initial full backup. Before you can use the subsequent binary logs as an incremental recovery source, you need a full backup by following this command.

mysqldump -uroot -p --all-databases --single-transaction --flush-logs --master-data=2 > full_backup.sql

6. After doing so, the flush-logs option closes the current log file (typically named “mysql-bin.000001”) and makes a new log file (usually named “mysql-bin.000002”). So, now you need to confirm the creation of the new MySQL binary log file.
7. Find the binary log created after the full backup. To check the available binary logs:

SHOW BINARY LOGS;

You may now see something like:

mysql-bin. 000001 | 1842

mysql-bin. 000002 | 9271

mysql-bin. 000003 | 4215

mysql-bin. 000004 | 128

8. The new binary log file starts recording changes after the log rotation. So, as your MySQL database continues to receive changes, MySQL writes those changes to the binary log. Eventually, it becomes the source for incremental recovery.
9. Then, preserve the relevant binary log files after the full backup. So, when you need MySQL database recovery, restore the full backup first.
10. After doing the full backup operation, apply the required binary log changes to bring the database to the desired point in time.

How to Take an Incremental Backup in MySQL via a Tool?

Instead of managing binary logs and separate scheduling processes yourself, you can configure the backup task through the automated solution.

Another method to create an incremental backup in MySQL is by using the dedicated Prapl SQL Backup Tool. Unlike other online solutions, this tool is installed directly on the Windows desktop, and the MySQL server database can be connected. It allows you to create an incremental backup operation and set what to back up, how, and where. Also, it never saves your database information on its server.

It supports sending backups to a own storage, Amazon S3, FTP Server, Google Drive, OneDrive, Dropbox, and multiple locations. When the backup operation fails, it sends email alerts to its users. Moreover, it allows its users to compress MySQL backup files, encrypt them for more data protection, set retention, and schedule backup frequency.

Steps to Use the Software for MySQL Incremental Backup

An incremental backup in MySQL can be created in a few steps. Follow these steps:

Run the application and choose MySQL Server from the list, hit Continue.

Then, set the connection by adding the necessary MySQL details. Select either a single database or multiple databases that need to be backed up.

In the open window, select the storage destination to send the backup file.

The last of the settings is scheduling the backup job for a consistent incremental backup job. Then, press the Save and Run Now button to run the incremental backup.

Verify the Incremental MySQL Backup Jobs

Regardless of which method you use, verify the backup after you are done with the process.

1. Check the Backup History: First, if you’re using the automated software, review the backup history and confirm that the incremental backup completed successfully.
2. Check the Backup Destination: Open the configured destination and verify that all the backup files are available. For a manual binary-log backup, check that the required binary-log files were successfully copied to the backup destination path.
3. Check the Recovery Chain: While following the manual approach, make sure you have a full backup, then binary log 1, binary log 2, binary log 3. Also, we suggest you not delete an earlier file simply because a newer incremental file exists. The recovery chain needs to remain intact for the MySQL recovery point you want.
4. Test a Restore: Also, make sure to restore the full backup in a test environment and apply the required binary logs.

Conclusion

Creating incremental backups for MySQL has numerous benefits, like they are quicker to manage and take a little storage space. Simply put, one can easily run them more often as it takes less time to get the job done. It also ensures point-in-time recovery of MySQL data whenever necessary.

However, you can perform the manual method; it’s quite challenging to create backups that way. Therefore, you can avoid being in a tragic situation just by using the Prapl SQL backup software to create an incremental backup in MySQL.