MysqlHow-ToBeginner · 4 min read

How to Use XtraBackup for MySQL: Backup and Restore Guide

Use xtrabackup to create hot backups of MySQL databases without locking the database. Run xtrabackup --backup to take a backup, then prepare it with xtrabackup --prepare, and restore by copying files back to the MySQL data directory.

📐

Syntax

The basic xtrabackup commands include:

xtrabackup --backup --target-dir=/path/to/backup: Creates a backup in the specified directory.
xtrabackup --prepare --target-dir=/path/to/backup: Prepares the backup for restore by applying logs.
xtrabackup --copy-back --target-dir=/path/to/backup: Restores the backup by copying files back to MySQL data directory.

Each option controls a step in the backup and restore process.

bash

xtrabackup --backup --target-dir=/var/backups/mysql_backup
xtrabackup --prepare --target-dir=/var/backups/mysql_backup
xtrabackup --copy-back --target-dir=/var/backups/mysql_backup

💻

Example

This example shows how to create a backup, prepare it, and restore it safely.

bash

# Step 1: Create backup
sudo xtrabackup --backup --target-dir=/var/backups/mysql_backup

# Step 2: Prepare backup
sudo xtrabackup --prepare --target-dir=/var/backups/mysql_backup

# Step 3: Stop MySQL service
sudo systemctl stop mysql

# Step 4: Restore backup
sudo xtrabackup --copy-back --target-dir=/var/backups/mysql_backup

# Step 5: Fix permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Step 6: Start MySQL service
sudo systemctl start mysql

Output

2024-06-01 12:00:00 completed OK! 2024-06-01 12:05:00 log applied successfully MySQL service stopped Backup copied back to /var/lib/mysql Permissions fixed MySQL service started

⚠️

Common Pitfalls

Common mistakes when using xtrabackup include:

Not preparing the backup before restore, which causes MySQL to fail starting.
Restoring files without stopping MySQL, leading to data corruption.
Incorrect file permissions after restore, causing MySQL startup errors.
Backing up without enough disk space for the backup files.

Always ensure to stop MySQL before restoring and fix permissions after copying files.

bash

Wrong way:
sudo xtrabackup --copy-back --target-dir=/var/backups/mysql_backup
sudo systemctl start mysql

Right way:
sudo systemctl stop mysql
sudo xtrabackup --copy-back --target-dir=/var/backups/mysql_backup
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

📊

Quick Reference

Command	Description
xtrabackup --backup --target-dir=DIR	Create a hot backup in DIR
xtrabackup --prepare --target-dir=DIR	Prepare backup by applying logs
xtrabackup --copy-back --target-dir=DIR	Restore backup files to MySQL data directory
chown -R mysql:mysql /var/lib/mysql	Fix ownership after restore
systemctl stop mysql	Stop MySQL service before restore
systemctl start mysql	Start MySQL service after restore

✅

Key Takeaways

Always run xtrabackup with --prepare before restoring to make backup consistent.

Stop MySQL service before restoring backup files to avoid corruption.

Fix file ownership to mysql:mysql after restoring backup files.

Use --backup option to create hot backups without locking the database.

Ensure enough disk space is available for backup storage.