mysqldump is a relic, and Percona XtraBackup is the only way to do it right.
Let’s see Percona XtraBackup in action. Imagine we have a production MySQL server with a database named production_db. We want to back up this database.
First, install Percona XtraBackup. On Debian/Ubuntu:
sudo apt update
sudo apt install percona-xtrabackup-80
On RHEL/CentOS:
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install percona-xtrabackup-80
Now, let’s perform a full backup. We’ll need a user with sufficient privileges, let’s say backup_user.
xtrabackup --backup --user=backup_user --password=your_backup_password --target-dir=/var/backups/xtrabackup/full
This command initiates a full physical backup. --backup tells it to perform a backup operation. --user and --password specify the MySQL credentials. --target-dir is where the backup files will be stored. Percona XtraBackup copies the data files directly from the MySQL data directory. It’s not a logical dump like mysqldump; it’s a block-level copy.
After the backup completes, you’ll find a directory structure in /var/backups/xtrabackup/full containing .ibd files, log files (xtrabackup_*.log), and a xtrabackup_info file.
This full backup is consistent, meaning it represents a point in time. However, to achieve point-in-time recovery (PITR), we need transaction logs. Percona XtraBackup automatically streams the MySQL binary logs (or InnoDB transaction logs if configured) during the backup process.
Let’s prepare the backup. This step makes the backup consistent and ready for restoration.
xtrabackup --prepare --target-dir=/var/backups/xtrabackup/full
The --prepare phase applies the transaction logs to the copied data files, bringing them to a consistent state.
Now, imagine a disaster strikes. Your production server is down, and you need to restore. First, shut down your MySQL server.
sudo systemctl stop mysql
Then, clean out the old data directory (or ensure it’s empty if you’re restoring to a new location).
sudo rm -rf /var/lib/mysql/*
Finally, restore the backup. We’ll need to ensure the ownership and permissions are correct for the MySQL user.
xtrabackup --copy-back --target-dir=/var/backups/xtrabackup/full --datadir=/var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql
--copy-back copies the prepared backup files to the specified --datadir. The chown command is crucial for MySQL to be able to read its data files.
This is a full restore. For PITR, you’d apply incremental backups (which are significantly faster and smaller than full backups) and then the binary logs.
The most surprising true thing about mysqldump is that it’s often used for backups when it’s fundamentally unsuitable for anything beyond small, non-critical databases or for specific, limited use cases like migrating schema. It locks tables for extended periods, causing significant downtime and data inconsistency for transactional workloads. Percona XtraBackup, on the other hand, performs hot backups with minimal locking, ensuring your production system remains available.
Percona XtraBackup’s --prepare step is not just about applying logs; it’s also about de-compressing and de-encrypting data if those options were used during the backup. The xtrabackup_info file contains metadata about the backup, including the SCN (System Change Number) at which the backup was taken, which is vital for applying incremental backups and binary logs for point-in-time recovery.
The real power of Percona XtraBackup lies in its incremental backup capabilities. Once you have a full backup, subsequent incremental backups capture only the blocks that have changed since the last backup (either full or incremental). This drastically reduces backup time and storage requirements. To restore a full backup with incremental and binary log recovery, you’d first restore the full backup, prepare it, then apply each incremental backup in sequence using --prepare, and finally, apply the binary logs up to your desired point in time.
What most people don’t realize is the efficiency of Percona XtraBackup’s parallel processing. You can specify the number of threads to use for backup and prepare operations using --parallel-threads. For example, --parallel-threads=4 can significantly speed up both the backup and prepare phases on multi-core systems by processing data pages concurrently.
The next concept you’ll want to master is Percona XtraBackup’s incremental backup workflow and how to perform a point-in-time recovery using binary logs.