How to Take an Incremental Backup in MySQL (September 2026 Guide)

An incremental backup in MySQL is a backup that captures only the data that has changed since your last backup, instead of copying the entire database every time. MySQL produces these backups by recording every write to the binary log (binlog), so a recent copy of the binlog files is enough to recover only what changed since the previous full backup. This guide walks through exactly how to take an incremental backup in MySQL on a Linux server, from enabling binary logging to restoring the chain, and includes a bash automation script and a troubleshooting section you can lean on when something breaks.

I have run this exact workflow on production MySQL 8 and MariaDB 10.x servers handling high-traffic ecommerce databases. The four-step sequence below – enable binary logging, take a full backup, flush and copy the binlog, then replay it on restore – is the same one I use weekly. By the end of this article you will have a complete incremental backup plan you can script, schedule with cron, and actually trust.

What Is an Incremental MySQL Backup and How Does It Differ From Full and Differential?

An incremental backup in MySQL only saves the changes made since the previous backup, while a full backup copies the entire database every time it runs. A differential backup sits in the middle – it captures every change since the last full backup, growing in size each time until the next full run resets it. The practical effect is that incremental backups are the smallest, fastest, and most frequent option, at the cost of a slightly more involved restore.

MySQL supports two distinct incremental backup methods. The first is the logical, binary-log method: you take a full logical backup with mysqldump, then treat each flushed segment of the binary log as an incremental. The second is the physical method, used by Percona XtraBackup and Mariabackup, which tracks InnoDB log sequence numbers (LSNs) and copies only the pages that changed on disk. Both methods produce a valid incremental backup in MySQL; the choice depends on database size and how much downtime you can tolerate.

Backup Type	What It Saves	Typical Size	Restore Complexity	Best For
Full backup	The entire database at a point in time	Equal to database size	Lowest – one file to restore	Daily or weekly baseline
Differential backup	All changes since the last full backup	Grows daily until next full	Medium – full + latest differential	Small databases, simple chains
Incremental backup	Only the changes since the last incremental (or full)	Smallest, consistent per run	Higher – full + every incremental in order	Large, busy production databases

The trade-off is clear. With incremental backups, you save storage and reduce load on a hot production server, but your restore has to replay the full backup plus every incremental in order. Skip one and the chain breaks, so good record-keeping matters.

Prerequisites for Taking an Incremental MySQL Backup

Before you run a single command, confirm the following on the server that hosts your MySQL or MariaDB instance. These prerequisites are the difference between a working incremental backup chain and one that silently breaks on day three.

• MySQL 5.7 or newer, or MariaDB 10.x. Older versions do not have all the binary log features you need.
• Root or sudo access to the server, plus a MySQL user with the RELOAD, LOCK TABLES, REPLICATION CLIENT, and PROCESS privileges.
• A dedicated backup directory on a separate filesystem or remote server – never the same disk as the live database.
• Enough free disk space for at least one full backup plus the binary logs you plan to retain between chains.
• If you want automated offsite storage, an S3-compatible bucket or rsync target ready to receive archives.

Step 1: Enable Binary Logging in MySQL

Binary logging is the foundation of the incremental backup method, and it is usually disabled by default in a fresh MySQL install. Open your MySQL configuration file (typically /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf) and add the options below under the [mysqld] section.

[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW
expire_logs_days        = 7
max_binlog_size         = 500M
sync_binlog             = 1

The server-id must be a positive integer and unique on any network where replication could happen. log_bin is what actually turns the binary log on – if it is commented out or absent, every later step in this guide fails. ROW format is recommended for incremental backups because it logs the changed rows themselves, which makes mysqlbinlog output reliable for point-in-time recovery.

Restart MySQL so the new settings take effect, then verify binary logging is on.

sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'binlog_format';"
mysql -u root -p -e "SHOW BINARY LOGS;"

SHOW BINARY LOGS should return a list with at least one mysql-bin file. If it returns an empty set or an error like “You are not using binary logging,” go back and confirm log_bin is uncommented and that the /var/log/mysql directory exists and is owned by the mysql user.

Step 2: Take Your Initial Full Backup With mysqldump

Every incremental backup chain starts with a full backup, and that full backup also rotates the binary log so the next incremental starts cleanly. The three mysqldump flags that matter here are –single-transaction, –flush-logs, and –master-data=2, and they work together to give you a consistent snapshot plus the exact binary log position to resume from.

mysqldump 
  --user=root 
  --password 
  --single-transaction 
  --flush-logs 
  --master-data=2 
  --routines 
  --triggers 
  --events 
  --hex-blob 
  --default-character-set=utf8mb4 
  --all-databases 
  | gzip > /backups/mysql/full/full_$(date +%F_%H-%M-%S).sql.gz

Here is what each flag is doing. –single-transaction wraps the whole dump in one InnoDB transaction so you get a consistent point-in-time view without locking tables. –flush-logs closes the current binary log and starts a fresh one, so the binlog file you will copy next contains only changes made after this dump finished. –master-data=2 writes the CHANGE MASTER TO comment (binary log file name and position) into the dump file, which is exactly what mysqlbinlog needs later to resume replay.

Confirm the dump finished and that the position comment is present.

gunzip -c /backups/mysql/full/full_2026-*.sql.gz | head -n 80 | grep -i "CHANGE MASTER"

You should see a line like CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000002′, MASTER_LOG_POS=154. Write that file name and position down – your restore depends on them.

Step 3: Take an Incremental Backup Using the Binary Log

Once the binary log is enabled and a fresh full backup exists, taking an incremental backup in MySQL is a two-step operation. First, flush the binary log so MySQL starts a new file, then copy the just-closed file to your backup directory. That single file is the incremental.

mysql -u root -p -e "FLUSH BINARY LOGS;"
ls -lh /var/log/mysql/

After FLUSH BINARY LOGS, MySQL rolls over to a new mysql-bin.NNNNNN file. The file that was just closed – the one with the highest number before the rollover – contains every change made since the previous full backup or the last incremental. Copy it somewhere safe.

cp /var/log/mysql/mysql-bin.000003 /backups/mysql/incremental/
gzip /backups/mysql/incremental/mysql-bin.000003

The gzipped mysql-bin file is your incremental backup for that window. Repeat the FLUSH BINARY LOGS and copy step on whatever schedule you choose – hourly, every four hours, nightly – and each cycle produces a new incremental that chains onto the previous one. The practical limit is how many files you want to manage before taking a fresh full backup and resetting the chain.

If you prefer to keep the binlog files on the server rather than copying them, the –delete-master-logs option on mysqldump tells MySQL to remove binlogs the server has already shipped. Most teams skip that option on the full backup and rely on expire_logs_days to clean up, because deleting a binlog you have not yet archived is the number one cause of broken incremental chains.

Step 4: Restore MySQL From a Full + Incremental Backup

The restore is the part most guides hand-wave, so let me walk through it end to end. You will replay the full backup, then pipe every incremental binlog file through mysqlbinlog in the order they were created. If any file is skipped or out of order, the restore stops being consistent.

Step one is to stop MySQL and move any live data aside so a clean restore is possible.

sudo systemctl stop mysql
sudo mv /var/lib/mysql /var/lib/mysql.old
sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql

Step two is to load the most recent full backup. Unzip and pipe directly into mysqld while initializing the data directory.

gunzip -c /backups/mysql/full/full_2026-09-01_02-00-00.sql.gz 
  | mysql -u root -p

Step three is to apply each incremental, in order, starting from the binlog position recorded in the full dump. Use mysqlbinlog to convert the binary file into SQL and replay it.

cd /backups/mysql/incremental
gunzip -c mysql-bin.000003.gz | mysqlbinlog | mysql -u root -p
gunzip -c mysql-bin.000004.gz | mysqlbinlog | mysql -u root -p
gunzip -c mysql-bin.000005.gz | mysqlbinlog | mysql -u root -p

Step four is to start MySQL and verify the data is intact.

sudo systemctl start mysql
mysql -u root -p -e "SHOW DATABASES;"
mysql -u root -p -e "SELECT COUNT(*) FROM your_app.orders;"

If you only want to recover to a specific point in time rather than to “everything since the last incremental,” pass the –start-datetime and –stop-datetime flags to mysqlbinlog. That is the point-in-time recovery (PITR) workflow, and it is what saves you when an erroneous DELETE or DROP TABLE fires at 14:23 and you need to roll forward to 14:22:59.

Choosing the Right Incremental Approach for Your Setup

The binary-log method is not always the right tool. It is excellent for small to mid-size databases – think anything under roughly 50 GB that you can comfortably lock with a single mysqldump transaction – because the tooling ships with MySQL itself and the restore is easy to reason about. For larger databases, the dump itself takes too long, locks too much, or produces files you cannot easily move. That is when teams switch to Percona XtraBackup or Mariabackup, which copy only the InnoDB pages that changed since the previous backup’s LSN.

Factor	mysqldump + Binary Log	Mariabackup / Percona XtraBackup
Database size sweet spot	Under ~50 GB	50 GB to multi-TB
Backup duration	Scales with DB size	Scales with amount of change
Locking on the live server	Minimal with –single-transaction	Brief at start and end only
Restore complexity	mysqlbinlog replay	–apply-log-only then –copy-back
Built-in to MySQL	Yes	No – separate install
Best for point-in-time recovery	Yes – native	Yes, with extra binlog replay

A practical rule of thumb from the forums: if your full mysqldump takes longer than the window you can afford to be slow, move to XtraBackup or Mariabackup. If it takes less than 30 minutes on a quiet night, stick with the binary-log method – it is simpler and the tooling is universal. You can also mix the two: weekly physical full backup with Mariabackup, daily incrementals on the same LSN chain, and binary logs in between for fine-grained point-in-time recovery.

If you do choose XtraBackup, the incremental command pattern looks like this, where –incremental-basedir points at the previous backup’s directory so the tool can read its xtrabackup_checkpoints file.

xtrabackup --backup 
  --target-dir=/backups/mysql/inc/2026-09-05 
  --incremental-basedir=/backups/mysql/full/2026-09-04

xtrabackup --prepare 
  --apply-log-only 
  --target-dir=/backups/mysql/inc/2026-09-05

Automating Incremental Backups With a Bash Script and Cron

Manually running FLUSH BINARY LOGS every day gets old fast, and it is exactly the kind of task you forget on the worst possible week. The bash script below wraps a full weekly backup plus daily incrementals, archives them with a timestamp, and prints a clear status line for your monitoring system. Drop it in /usr/local/bin and schedule it with cron.

#!/usr/bin/env bash
set -euo pipefail

BACKUP_ROOT="/backups/mysql"
FULL_DIR="$BACKUP_ROOT/full"
INC_DIR="$BACKUP_ROOT/incremental"
LOG_DIR="$BACKUP_ROOT/logs"
BINLOG_DIR="/var/log/mysql"
RETENTION_DAYS=14

mkdir -p "$FULL_DIR" "$INC_DIR" "$LOG_DIR"

log() { echo "[$(date '+%F %T')] $*" | tee -a "$LOG_DIR/backup.log"; }

if [ "$(date +%u)" -eq 1 ]; then
  log "Starting weekly full backup"
  mysqldump --user=root --password="$MYSQL_PWD" 
    --single-transaction --flush-logs --master-data=2 
    --routines --triggers --events --hex-blob 
    --all-databases 
    | gzip > "$FULL_DIR/full_$(date +%F_%H-%M-%S).sql.gz"
  log "Full backup complete"
else
  log "Starting incremental backup"
  mysql -u root -p"$MYSQL_PWD" -e "FLUSH BINARY LOGS;"
  LATEST=$(ls -1 "$BINLOG_DIR"/mysql-bin.* | sort | tail -n 2 | head -n 1)
  cp "$LATEST" "$INC_DIR/"
  gzip "$INC_DIR/$(basename "$LATEST")"
  log "Incremental backup complete: $(basename "$LATEST")"
fi

find "$FULL_DIR" -type f -mtime +$RETENTION_DAYS -delete
find "$INC_DIR" -type f -mtime +$RETENTION_DAYS -delete
log "Pruned backups older than $RETENTION_DAYS days"

Save the script as /usr/local/bin/mysql-incremental.sh, mark it executable with chmod +x, and add a cron line that runs it at 02:00 every day. The Monday branch takes the full backup; every other day runs the incremental.

0 2 * * * /usr/local/bin/mysql-incremental.sh >> /var/log/mysql-incremental.log 2>&1

For offsite storage, append an aws s3 cp command against your bucket, or use rsync to a remote server, after the backup block. The crucial rule is that the offsite transfer must succeed before you let expire_logs_days prune the binlog files – otherwise a slow network or a failed sync silently breaks the chain.

Troubleshooting Common Incremental Backup Errors

Even a well-configured incremental backup in MySQL breaks in predictable ways. These are the failures I see most often on real servers, and the exact fix for each one.

Error: “You are not using binary logging.” This is the most common issue, and it means log_bin is not set or the server was not restarted. Edit my.cnf, set log_bin, restart MySQL, then run SHOW VARIABLES LIKE ‘log_bin’ to confirm.

Error: “File ‘mysql-bin.NNNNNN’ not found” during restore. The binary log you are trying to replay has already been purged by expire_logs_days or by an over-eager –delete-master-logs. The fix is to back up binlog files off the live server before the retention policy runs, and to ensure your cron transfer to offsite storage completes successfully.

Error: incremental chain appears to skip changes. You probably missed a binlog file when copying, or you applied them in the wrong order. The CHANGE MASTER TO MASTER_LOG_POS value recorded at full-backup time tells you the exact starting file and position; everything after that must be applied in sequence with no gaps.

Error: “Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege.” The MySQL user running mysqldump or mysqlbinlog lacks the privileges needed for –master-data. Grant REPLICATION CLIENT and RELOAD, or use the root user for system-level backups.

Error: disk fills up with binary logs. expire_logs_days is unset (default 0, never purge) or max_binlog_size is large and your incremental schedule is frequent. Set a sane retention window and ensure your cron script uploads binlogs off the live server before they expire.

If a single database was lost inside a multi-database server, the full + incremental approach still works: replay everything, then drop the unwanted databases, or filter mysqlbinlog by database name with –database=your_db to recover just the rows for the affected schema. That last option is the one most forum threads end up asking about, and it is the cleanest path.

Frequently Asked Questions

Conclusion

Taking an incremental backup in MySQL comes down to four repeatable steps: enable the binary log, take a full mysqldump baseline, flush and copy each mysql-bin file as the incremental, and replay the chain in order on restore. That same workflow scales from a 5 GB WordPress site to a multi-TB SaaS database, with Mariabackup or Percona XtraBackup as the swap-in tool when the database outgrows mysqldump.

Set up the cron script, verify your first restore on a throwaway server, and only then trust the chain on production. If you want a deeper dive into the physical backup path or into point-in-time recovery windows, those are the natural next articles in this cluster.