Full + incremental physical backup and restore with XtraBackup

Command

# Full backup
xtrabackup --backup --host=127.0.0.1 --user=bkp --password='Bkp#2024' --target-dir=/backup/full --parallel=4
# Incremental (based on the previous backup)
xtrabackup --backup --host=127.0.0.1 --user=bkp --password='Bkp#2024' --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# Prepare: full and intermediate incrementals with --apply-log-only, final step without it
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1
xtrabackup --prepare --target-dir=/backup/full

# Restore: mysqld stopped and datadir empty
systemctl stop mysqld
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
cat /backup/full/xtrabackup_binlog_info

What it does

Takes a hot physical backup by copying InnoDB files and the redo log while the server runs; an incremental backup contains only pages changed since the previous backup. The prepare step applies redo and makes the backup consistent.

When to use it

For large databases that need fast restores (many times faster than logical), for seeding replicas, and for daily full + hourly incremental strategies.

Caution

The XtraBackup version must match the server release series (8.0 server → XtraBackup 8.0, 8.4 → 8.4); if the server uses a newer redo / data dictionary format than the XtraBackup build, the backup is refused. An unprepared backup is inconsistent. Forgetting --apply-log-only on intermediate steps makes later incrementals unusable. The datadir must be empty for restore; --move-back saves disk but consumes the backup. Backup user: BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT, and SELECT on performance_schema.log_status, keyring_component_status, replication_group_members.