Posted inMySQL

Percona XtraBackup Guide: Back up 100GB+ MySQL Databases Without System Freezes

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

The Issue: When mysqldump Becomes a Production Nightmare

Back when I first started as a DevOps engineer, I learned a hard lesson with a 150GB MySQL database. Every night at 2 AM, when the mysqldump cronjob ran, the system would start firing continuous slow query alerts. Some days, the website would even freeze completely because tables were locked for too long.

The problem is that mysqldump is a logical backup tool. It reads every row of data and converts it into SQL INSERT statements. While this works for small databases, it’s painfully slow and incredibly I/O intensive when a ‘users’ table hits 50 million rows. Even if you use --single-transaction, table locks can still occur during sensitive phases, causing immediate service interruptions.

That’s when I switched to Percona XtraBackup. This tool performs a “Hot Backup” – backing up while the database is running without causing table locks (non-blocking). Here is how I implemented it in practice for you to apply.

Why is Percona XtraBackup Faster and Safer?

Unlike mysqldump’s “row-by-row” approach, Percona XtraBackup is a physical backup tool. Think of it as directly copying .ibd and .frm files from the hard drive to another location.

Its mechanism is very practical. While copying data, if new transactions occur, XtraBackup records them in a redo log file. Afterward, it automatically applies these changes to ensure the backup is consistent.

The numbers speak for themselves:

  • Speed: In a real test with a 100GB DB, mysqldump took nearly 3 hours, while XtraBackup only needed 25-30 minutes.
  • Zero Downtime: Users can still read/write normally; the locking rate is 0%.
  • Recovery: You just copy the files back into the MySQL data directory, which is much faster than waiting for the server to re-run millions of SQL commands.

Implementing Hot Backup on Ubuntu

Before running any commands, you need to check your MySQL version. If you are using MySQL version 8.0, install XtraBackup 8.0. For MySQL 5.7 and below, version 2.4 is the standard choice.

1. Installing Percona XtraBackup

I prefer using the official Percona repository to get the latest bug fixes:

# Download and install the repo package
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

# Update and install the version for MySQL 8.0
sudo apt update
sudo apt install percona-xtrabackup-80

2. Configuring a Dedicated User

Never use the root user to run backups due to high security risks. Create a user with just enough permissions:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'SecurePassword123';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

3. Running a Full Backup

Suppose you need to save the backup to /data/backups/full_backup. Run the following command:

xtrabackup --backup --user=backup_user --password=SecurePassword123 --target-dir=/data/backups/full_backup

Even if the database is handling thousands of requests per second, XtraBackup quietly copies data at the disk level without affecting the user experience.

4. The Prepare Phase – The Make-or-Break Step

This is the most common mistake for beginners. The data cannot be used immediately after copying because it might contain incomplete transactions. We need the “Prepare” step to finalize the data:

xtrabackup --prepare --target-dir=/data/backups/full_backup

When the screen displays completed OK!, your backup is in a clean, consistent state and ready for recovery at any time.

Recovery Process in Case of Incidents

When the database goes down, stay calm and follow these steps:

  1. Stop the MySQL service immediately: sudo systemctl stop mysql
  2. Clean up the old data directory (it’s better to move it rather than delete it): sudo move /var/lib/mysql /var/lib/mysql_old
  3. Restore data from the backup:
xtrabackup --copy-back --target-dir=/data/backups/full_backup

Important Note: After running copy-back, the files will be owned by root. You must return ownership to the mysql user, otherwise the service will fail to start:

sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

Insights from Operating Large Systems

After 5 years of managing database clusters ranging from 500GB to several TBs, here is some advice:

  • Monitor Disk Capacity: Always keep at least 60-70% of your hard drive free. I once encountered a case where a backup filled the disk, causing the entire production server to hang because it couldn’t write logs.
  • Stream Data Off-site (Streaming): If the server lacks space, stream the backup directly to a backup server via SSH using pipes. This is both safe and saves resources on the source server.
  • Incremental Backup: For databases over 500GB, don’t do a full backup every day. Combine it with incremental backups to only save small daily changes, significantly reducing bandwidth load.

Conclusion

Using Percona XtraBackup is a mandatory choice if you want to manage MySQL professionally. It not only completely resolves table locking issues but also gives you more confidence when facing large-scale data recovery scenarios. If your system is growing, ditch mysqldump and switch to XtraBackup today.

Share:
Tags: