Quick start: Zero-downtime MySQL backup in 5 minutes
Running mysqldump on a 200 GB database during peak hours is a massive risk. The FLUSH TABLES WITH READ LOCK command locks write operations tight. The result: web requests pile up, connection pools overflow, and your system crashes in seconds.
Percona XtraBackup was built to solve this exact problem. It reads raw data blocks directly from disk while tracking the redo log in real time. This allows you to back up freely while your application continues processing thousands of INSERT and UPDATE queries per second (non-blocking hot backup).
Quick steps to set up a full backup on Ubuntu 22.04 / Debian 12:
1. Install Percona XtraBackup 8.0
# Add the official Percona repository
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release enable-only tools release
sudo apt-get update
# Install XtraBackup specifically for MySQL 8.0
sudo apt-get install -y percona-xtrabackup-80
2. Create a dedicated backup user in MySQL
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'MatKhauSieuKho123!';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;
3. Run the full backup command
# Create the backup directory
sudo mkdir -p /data/backups/base
# Launch the hot backup
xtrabackup --backup \
--user=bkpuser \
--password='MatKhauSieuKho123!' \
--target-dir=/data/backups/base
Once the terminal displays completed OK!, the process is finished. Your entire database is safely backed up without dropping a single query on your website.
How does XtraBackup work, and how does it differ from mysqldump?
Most of us started with mysqldump because it comes pre-installed with the MySQL client. However, once a database exceeds 50 GB, this logical backup approach struggles significantly. It runs single-threaded, forces MySQL into full-table scans, and generates massive .sql files. Restoring a 200 GB file often takes anywhere from 6 to 10 hours.
Percona XtraBackup takes a different approach: physical backup (binary backup). Its performance is bounded by disk I/O, cutting the backup time for 200 GB down to just 15–20 minutes. Here are the three core components behind its mechanism:
- Physical page copying (.ibd): XtraBackup reads raw 16 KB InnoDB pages directly from disk. It doesn’t need to select rows individually or worry about SQL syntax.
- Parallel Redo Log capturing: New transactions continue to be written while data files are being copied. To maintain consistency, XtraBackup runs a dedicated background thread that monitors and records all changes in the redo log from the start to the end of the backup session.
- The Prepare phase: The newly copied raw data cannot be used immediately because it contains uncommitted transactions. Running the
--preparecommand applies the recorded redo log (roll forward) and rolls back uncommitted transactions. This turns the backup directory into a consistent database at a clean point-in-time state.
Setting up incremental backups
Running a full backup every day on a half-terabyte system will quickly exhaust your storage capacity. The optimal approach is tiered scheduling: Full backup early Sunday morning, and incremental backups on weekdays to capture only the pages that have changed since the previous checkpoint.
1. Run the first incremental backup (Monday)
# Base comparison against Sunday's full backup
xtrabackup --backup \
--user=bkpuser \
--password='MatKhauSieuKho123!' \
--target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base
2. Run the next incremental backup (Tuesday)
# Point comparison to Monday's backup (inc1)
xtrabackup --backup \
--user=bkpuser \
--password='MatKhauSieuKho123!' \
--target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1
3. Restoring an incremental backup chain
This is where sysadmins make mistakes most often. You must merge each incremental backup into the base directory in strict chronological order. Always retain the --apply-log-only flag during intermediate steps to prevent the database from triggering premature rollbacks:
# Step 1: Prepare the base backup (keep it open to receive subsequent data)
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
# Step 2: Merge inc1 into the base backup
xtrabackup --prepare --apply-log-only \
--target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1
# Step 3: Merge the final incremental backup inc2 (OMIT --apply-log-only to complete rollback)
xtrabackup --prepare \
--target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2
# Step 4: Restore to the MySQL datadir
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
sudo xtrabackup --copy-back --target-dir=/data/backups/base
# Reassign ownership to the mysql user and restart the service
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql
Battle-tested production best practices
- Enable on-the-fly Zstandard compression: Don’t generate uncompressed backups only to compress them externally with
gzip, as this consumes double the disk space. Add--compress=zstd --compress-threads=4directly to your backup command. This reduces storage by 60–70% with only a modest 10–15% bump in CPU usage. - Throttle IOPS using the –throttle flag: When your database shares disks with applications, aggressive copying can easily cause I/O bottlenecks. Add
--throttle=400(limiting throughput to 400 I/O operations per second) to preserve resources for user transactions. - Stream directly to a backup server: Not enough disk space on the database host for two copies of data simultaneously? Stream the backup directly to a remote NAS or backup server over the local network:
xtrabackup --backup --stream=xbstream --user=bkpuser --password='...' | \ ssh [email protected] "xbstream -x -C /backup/storage/base" - Verify to_lsn and from_lsn checkpoints: Every backup directory contains an
xtrabackup_checkpointsfile. Before merging incremental backups, verify that thefrom_lsnof the subsequent backup matches theto_lsnof the previous one exactly. A mismatch will break the entire restore chain. - Run regular disaster recovery drills: A successful backup log does not guarantee that your data is recoverable. Write an automated cron job to restore the latest backup to a staging VPS or container every week. Only when the staging MySQL instance boots up cleanly and returns valid SELECT queries can you truly sleep soundly at night.