MySQL Backup with mysqldump and Percona XtraBackup
Database backups are the last line of defense against data loss. Whether it is hardware failure, human error, or ransomware attacks, having reliable backups can mean the difference between a minor inconvenience and a business-ending catastrophe.
🎯 Understanding Backup Types
Before diving into tools, it is essential to understand the fundamental types of database backups:
| Backup Type | Description | Use Case |
|---|---|---|
| Logical Backup | SQL statements to recreate data | Small databases, cross-version migration |
| Physical Backup | Raw data file copies | Large databases, fast recovery |
| Incremental | Only changed data since last backup | Reducing backup window and storage |
| Differential | Changes since last full backup | Balance between full and incremental |
💡 Pro Tip: A robust backup strategy typically combines multiple backup types for optimal Recovery Point Objective (RPO) and Recovery Time Objective (RTO).
📦 Part 1: mysqldump - Logical Backups
What is mysqldump?
mysqldump is MySQL's native logical backup utility that creates SQL files containing the statements needed to recreate your database structure and data.
Basic Usage
# Backup a single database
mysqldump -u root -p database_name > backup.sql
# Backup multiple databases
mysqldump -u root -p --databases db1 db2 db3 > multiple_dbs.sql
# Backup all databases
mysqldump -u root -p --all-databases > full_backup.sql
# Backup only structure (no data)
mysqldump -u root -p --no-data database_name > schema_only.sql
# Backup only data (no structure)
mysqldump -u root -p --no-create-info database_name > data_only.sql
Production-Ready mysqldump Script
#!/bin/bash
# production_backup.sh - Enterprise mysqldump backup script
# ============================================
# Configuration
# ============================================
BACKUP_DIR="/backup/mysql"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="SecureBackupPassword123!"
MYSQL_HOST="localhost"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"
# ============================================
# Functions
# ============================================
log_message() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
check_disk_space() {
AVAILABLE=$(df -BG "$BACKUP_DIR" | tail -1 | awk '{print $4}' | sed 's/G//')
if [ "$AVAILABLE" -lt 50 ]; then
log_message "⚠️ WARNING: Less than 50GB available on backup drive"
exit 1
fi
}
# ============================================
# Main Backup Process
# ============================================
log_message "🚀 Starting MySQL backup process..."
# Create backup directory
mkdir -p "$BACKUP_DIR/$DATE"
check_disk_space
# Get list of databases
DATABASES=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" \
-e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")
# Backup each database
for DB in $DATABASES; do
log_message "📦 Backing up database: $DB"
mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" \
--single-transaction \
--routines \
--triggers \
--events \
--set-gtid-purged=OFF \
--quick \
--lock-tables=false \
"$DB" | gzip > "$BACKUP_DIR/$DATE/${DB}.sql.gz"
if [ $? -eq 0 ]; then
log_message "✅ Successfully backed up: $DB"
else
log_message "❌ Failed to backup: $DB"
fi
done
# Backup global objects (users, grants)
log_message "📦 Backing up users and privileges..."
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" \
-N -e "SELECT CONCAT('SHOW CREATE USER ''', user, '''@''', host, ''';')
FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema')" \
| mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" -N \
| sed 's/$/;/' > "$BACKUP_DIR/$DATE/users.sql"
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" \
-N -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';')
FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema')" \
| mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" -N \
| sed 's/$/;/' >> "$BACKUP_DIR/$DATE/users.sql"
# ============================================
# Cleanup Old Backups
# ============================================
log_message "🧹 Cleaning up backups older than $RETENTION_DAYS days..."
find "$BACKUP_DIR" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \; 2>/dev/null
# ============================================
# Verify Backup
# ============================================
BACKUP_SIZE=$(du -sh "$BACKUP_DIR/$DATE" | cut -f1)
log_message "✅ Backup completed. Total size: $BACKUP_SIZE"
# Send notification (optional)
# mail -s "MySQL Backup Completed - $DATE" [email protected] < "$LOG_FILE"
Essential mysqldump Options
# Production backup with all recommended options
mysqldump \
--user=backup_user \
--password='SecurePassword123!' \
--host=localhost \
--port=3306 \
--single-transaction \ # Consistent backup without locking (InnoDB)
--routines \ # Include stored procedures and functions
--triggers \ # Include triggers
--events \ # Include scheduled events
--set-gtid-purged=OFF \ # For non-replication scenarios
--quick \ # Retrieve rows one at a time (large tables)
--lock-tables=false \ # Don't lock tables (use with single-transaction)
--max_allowed_packet=512M \ # For large BLOBs
--net_buffer_length=32768 \ # Network buffer optimization
--compress \ # Compress client/server protocol
--hex-blob \ # Dump binary columns as hex
--complete-insert \ # Include column names in INSERT
--extended-insert \ # Use multi-row INSERT syntax
--order-by-primary \ # Sort each table by primary key
--skip-lock-tables \ # Skip LOCK TABLES for MyISAM
database_name > backup.sql
Parallel mysqldump with mydumper
For faster backups, use mydumper:
# Install mydumper
apt-get install mydumper
# Parallel backup with 8 threads
mydumper \
--user=backup_user \
--password='SecurePassword123!' \
--host=localhost \
--database=production_db \
--outputdir=/backup/mydumper \
--threads=8 \
--compress \
--verbose=3 \
--long-query-guard=300 \
--kill-long-queries
# Parallel restore with myloader
myloader \
--user=root \
--password='SecurePassword123!' \
--host=localhost \
--database=production_db \
--directory=/backup/mydumper \
--threads=8 \
--verbose=3
🚀 Part 2: Percona XtraBackup - Physical Backups
Why Percona XtraBackup?
Percona XtraBackup provides hot, non-blocking backups for InnoDB tables, making it ideal for large production databases where downtime is not an option.
| Feature | mysqldump | XtraBackup |
|---|---|---|
| Backup Speed | Slower (logical) | 10-50x faster (physical) |
| Locking | May require locks | No locking for InnoDB |
| Restore Speed | Slower (re-execute SQL) | Very fast (file copy) |
| Incremental | Not native | Native support |
| Point-in-Time | Via binlogs | Via binlogs |
| Compression | Via gzip pipe | Native compression |
Installing Percona XtraBackup
# Ubuntu/Debian
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
apt-get update
apt-get install percona-xtrabackup-80
# CentOS/RHEL
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools release
yum install percona-xtrabackup-80
# Verify installation
xtrabackup --version
Full Backup with XtraBackup
# Create full backup
xtrabackup \
--backup \
--user=backup_user \
--password='SecurePassword123!' \
--target-dir=/backup/xtrabackup/full \
--parallel=4
# Prepare the backup (apply redo logs)
xtrabackup \
--prepare \
--target-dir=/backup/xtrabackup/full
# The backup is now consistent and ready for restore
Incremental Backups
# Day 1: Full backup (Sunday)
xtrabackup --backup \
--user=backup_user \
--password='SecurePassword123!' \
--target-dir=/backup/xtrabackup/full
# Day 2: First incremental (Monday)
xtrabackup --backup \
--user=backup_user \
--password='SecurePassword123!' \
--target-dir=/backup/xtrabackup/inc1 \
--incremental-basedir=/backup/xtrabackup/full
# Day 3: Second incremental (Tuesday)
xtrabackup --backup \
--user=backup_user \
--password='SecurePassword123!' \
--target-dir=/backup/xtrabackup/inc2 \
--incremental-basedir=/backup/xtrabackup/inc1
# Prepare for restore (apply all incrementals)
# Step 1: Prepare full backup with --apply-log-only
xtrabackup --prepare --apply-log-only \
--target-dir=/backup/xtrabackup/full
# Step 2: Apply first incremental
xtrabackup --prepare --apply-log-only \
--target-dir=/backup/xtrabackup/full \
--incremental-dir=/backup/xtrabackup/inc1
# Step 3: Apply second incremental (no --apply-log-only on last one)
xtrabackup --prepare \
--target-dir=/backup/xtrabackup/full \
--incremental-dir=/backup/xtrabackup/inc2
Production XtraBackup Script
#!/bin/bash
# xtrabackup_production.sh - Enterprise XtraBackup script
# ============================================
# Configuration
# ============================================
BACKUP_BASE="/backup/xtrabackup"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="SecurePassword123!"
MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
THREADS=4
RETENTION_FULL=4 # Keep 4 full backups
RETENTION_INC=7 # Keep 7 days of incrementals
DAY_OF_WEEK=$(date +%u) # 1=Monday, 7=Sunday
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/xtrabackup.log"
# ============================================
# Functions
# ============================================
log_message() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
send_alert() {
# Integrate with your alerting system
echo "$1" | mail -s "XtraBackup Alert" [email protected]
}
verify_backup() {
if xtrabackup --validate --target-dir="$1" 2>/dev/null; then
return 0
else
return 1
fi
}
# ============================================
# Determine Backup Type
# ============================================
if [ "$DAY_OF_WEEK" -eq 7 ]; then
# Sunday: Full backup
BACKUP_TYPE="full"
BACKUP_DIR="$BACKUP_BASE/full_$DATE"
log_message "🚀 Starting FULL backup to $BACKUP_DIR"
xtrabackup --backup \
--user="$MYSQL_USER" \
--password="$MYSQL_PASSWORD" \
--socket="$MYSQL_SOCKET" \
--target-dir="$BACKUP_DIR" \
--parallel="$THREADS" \
--compress \
--compress-threads="$THREADS" 2>> "$LOG_FILE"
if [ $? -eq 0 ]; then
# Create symlink to latest full backup
rm -f "$BACKUP_BASE/latest_full"
ln -s "$BACKUP_DIR" "$BACKUP_BASE/latest_full"
log_message "✅ Full backup completed successfully"
else
log_message "❌ Full backup FAILED"
send_alert "XtraBackup FULL backup failed on $(hostname)"
exit 1
fi
else
# Weekdays: Incremental backup
BACKUP_TYPE="incremental"
# Find the base for incremental
if [ -L "$BACKUP_BASE/latest_inc" ]; then
BASE_DIR=$(readlink -f "$BACKUP_BASE/latest_inc")
else
BASE_DIR=$(readlink -f "$BACKUP_BASE/latest_full")
fi
BACKUP_DIR="$BACKUP_BASE/inc_$DATE"
log_message "🚀 Starting INCREMENTAL backup to $BACKUP_DIR (base: $BASE_DIR)"
xtrabackup --backup \
--user="$MYSQL_USER" \
--password="$MYSQL_PASSWORD" \
--socket="$MYSQL_SOCKET" \
--target-dir="$BACKUP_DIR" \
--incremental-basedir="$BASE_DIR" \
--parallel="$THREADS" \
--compress \
--compress-threads="$THREADS" 2>> "$LOG_FILE"
if [ $? -eq 0 ]; then
rm -f "$BACKUP_BASE/latest_inc"
ln -s "$BACKUP_DIR" "$BACKUP_BASE/latest_inc"
log_message "✅ Incremental backup completed successfully"
else
log_message "❌ Incremental backup FAILED"
send_alert "XtraBackup INCREMENTAL backup failed on $(hostname)"
exit 1
fi
fi
# ============================================
# Record Backup Metadata
# ============================================
cat > "$BACKUP_DIR/backup_info.txt" << EOF
Backup Type: $BACKUP_TYPE
Backup Date: $(date)
Hostname: $(hostname)
MySQL Version: $(mysql --version)
XtraBackup Version: $(xtrabackup --version 2>&1 | head -1)
Backup Size: $(du -sh "$BACKUP_DIR" | cut -f1)
EOF
# ============================================
# Cleanup Old Backups
# ============================================
log_message "🧹 Cleaning up old backups..."
# Keep only last N full backups
FULL_BACKUPS=$(ls -dt "$BACKUP_BASE"/full_* 2>/dev/null | tail -n +$((RETENTION_FULL + 1)))
for OLD_BACKUP in $FULL_BACKUPS; do
log_message "Removing old full backup: $OLD_BACKUP"
rm -rf "$OLD_BACKUP"
done
# Remove incrementals older than retention period
find "$BACKUP_BASE" -maxdepth 1 -name "inc_*" -type d -mtime +$RETENTION_INC -exec rm -rf {} \;
log_message "✅ Backup process completed"
🔄 Restore Procedures
Restoring from mysqldump
# Basic restore
mysql -u root -p database_name < backup.sql
# Restore compressed backup
gunzip < backup.sql.gz | mysql -u root -p database_name
# Restore with progress indicator (using pv)
pv backup.sql.gz | gunzip | mysql -u root -p database_name
# Restore specific tables
mysql -u root -p database_name < table1_backup.sql
Restoring from XtraBackup
# Step 1: Stop MySQL
systemctl stop mysql
# Step 2: Backup current data directory (safety measure)
mv /var/lib/mysql /var/lib/mysql_old
# Step 3: Copy backup to data directory
xtrabackup --copy-back --target-dir=/backup/xtrabackup/full
# Step 4: Fix permissions
chown -R mysql:mysql /var/lib/mysql
# Step 5: Start MySQL
systemctl start mysql
# Step 6: Verify
mysql -u root -p -e "SHOW DATABASES;"
Point-in-Time Recovery
# Restore XtraBackup to a specific point
# Step 1: Identify the binlog position from xtrabackup_binlog_info
cat /backup/xtrabackup/full/xtrabackup_binlog_info
# Output: mysql-bin.000042 156789234
# Step 2: Restore the backup normally
# (follow XtraBackup restore steps above)
# Step 3: Apply binlogs up to the desired point
mysqlbinlog \
--start-position=156789234 \
--stop-datetime="2025-01-15 14:30:00" \
/var/lib/mysql/mysql-bin.000042 \
/var/lib/mysql/mysql-bin.000043 | mysql -u root -p
📊 Backup Monitoring and Verification
Automated Backup Verification
-- Create backup verification table
CREATE TABLE backup_verification (
id INT AUTO_INCREMENT PRIMARY KEY,
backup_date DATETIME,
backup_type ENUM('full', 'incremental', 'logical'),
backup_tool VARCHAR(50),
backup_size_gb DECIMAL(10,2),
duration_seconds INT,
verification_status ENUM('passed', 'failed', 'pending'),
verification_date DATETIME,
notes TEXT,
INDEX idx_date (backup_date),
INDEX idx_status (verification_status)
);
-- Insert backup record
INSERT INTO backup_verification
(backup_date, backup_type, backup_tool, backup_size_gb, duration_seconds, verification_status)
VALUES
(NOW(), 'full', 'xtrabackup', 45.6, 1234, 'pending');
Backup Verification Script
#!/bin/bash
# verify_backup.sh - Backup verification script
BACKUP_DIR="$1"
VERIFY_DB="verify_test_$(date +%s)"
log_message() {
echo "[$(date)] $1"
}
# Method 1: XtraBackup validation
if [ -f "$BACKUP_DIR/xtrabackup_checkpoints" ]; then
log_message "🔍 Validating XtraBackup files..."
xtrabackup --validate --target-dir="$BACKUP_DIR"
if [ $? -eq 0 ]; then
log_message "✅ XtraBackup validation passed"
else
log_message "❌ XtraBackup validation FAILED"
exit 1
fi
fi
# Method 2: Test restore to temporary instance
log_message "🔍 Performing test restore..."
# Create temporary MySQL instance
mkdir -p /tmp/mysql_verify
xtrabackup --copy-back \
--target-dir="$BACKUP_DIR" \
--datadir=/tmp/mysql_verify
# Start temporary instance on different port
mysqld_safe \
--datadir=/tmp/mysql_verify \
--port=3307 \
--socket=/tmp/mysql_verify.sock &
sleep 30
# Run verification queries
mysql -u root -S /tmp/mysql_verify.sock -e "
SELECT COUNT(*) FROM information_schema.tables;
SHOW STATUS LIKE 'Uptime';
CHECK TABLE important_db.critical_table;
"
if [ $? -eq 0 ]; then
log_message "✅ Test restore verification passed"
else
log_message "❌ Test restore verification FAILED"
fi
# Cleanup
mysqladmin -u root -S /tmp/mysql_verify.sock shutdown
rm -rf /tmp/mysql_verify*
log_message "✅ Backup verification completed"
✅ Best Practices Checklist
Backup Strategy
- [ ] Implement 3-2-1 backup rule (3 copies, 2 media types, 1 offsite)
- [ ] Schedule full backups weekly, incrementals daily
- [ ] Test restores monthly
- [ ] Document Recovery Time Objective (RTO) and Recovery Point Objective (RPO)
- [ ] Encrypt backups at rest and in transit
Security
# Encrypt backup with GPG
mysqldump database_name | gzip | gpg --encrypt --recipient [email protected] > backup.sql.gz.gpg
# Create dedicated backup user with minimal privileges
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'SecurePassword123!';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT,
EVENT, TRIGGER, LOCK TABLES, PROCESS ON *.* TO 'backup_user'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
Monitoring
-- Check binary log position for PITR
SHOW MASTER STATUS;
-- Verify backup can be used for replication
SHOW SLAVE STATUS\G
-- Monitor disk space for backups
SELECT
table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Size (GB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
🚨 Disaster Recovery Scenarios
Scenario 1: Complete Server Failure
# 1. Provision new server
# 2. Install MySQL same version
# 3. Restore from latest backup
xtrabackup --copy-back --target-dir=/backup/xtrabackup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql
# 4. Apply any remaining binary logs for PITR
mysqlbinlog /backup/binlogs/mysql-bin.* | mysql -u root -p
Scenario 2: Accidental Table Drop
# Use PITR to restore to point before DROP
# 1. Find the exact time of DROP in binary logs
mysqlbinlog --verbose /var/lib/mysql/mysql-bin.* | grep -B5 "DROP TABLE"
# 2. Restore backup and replay to just before the DROP
mysqlbinlog \
--stop-datetime="2025-01-15 14:29:59" \
/var/lib/mysql/mysql-bin.* | mysql -u root -p
🎓 Conclusion
A robust backup strategy combining mysqldump for flexibility and Percona XtraBackup for speed provides comprehensive protection for your MySQL databases. Regular testing and verification ensure your backups will work when you need them most.
⚠️ Critical Reminder: Untested backups are not backups. Schedule regular restore tests and document the process.
Key Takeaways
- Use XtraBackup for large production databases
- Use mysqldump for portability and cross-version compatibility
- Implement incremental backups to reduce backup windows
- Test restores regularly - at least monthly
- Encrypt and secure all backup files
About the Author
Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.
Related Articles
Need help with MySQL backup strategy? Contact us for expert database consulting.