MySQL Backups: mysqldump, XtraBackup and Point-in-Time Recovery

Why Backups Are "Now," Not "Later"

A developer runs a migration on production — and the SQL script without a proper WHERE clause corrupts 40% of the orders table. A real case from our practice. Panic, the client is losing money every minute, managers are calling non-stop.

The result? 45 minutes — and 100% of data restored. Saved by an automatic snapshot created 15 minutes before the incident. Most of the time was spent not on the actual recovery, but on deploying the dump to a separate server to selectively extract corrupted rows.

This is a happy ending. But it's only possible when backups are set up correctly. Many clients who come to us have a three-month-old backup somewhere on the same server. Or a cron job that's been silently failing for six months.

This guide is about how to set up MySQL backups so you can sleep peacefully. From simple mysqldump to production-ready strategies with incremental backups and point-in-time recovery.

Logical and Physical Backups: What's the Difference

Before choosing a tool — you need to understand what it actually does. MySQL backups come in two types, and each has its strengths.

Logical Backups

This is an SQL dump: a set of CREATE TABLE, INSERT INTO commands that recreate the database structure and data from scratch. Tools — mysqldump, mysqlpump, phpMyAdmin.

Pros: portability (can transfer between MySQL/MariaDB versions), readability (it's plain text), ability to select specific tables. Cons: slow restoration on large databases, server load during dump.

Physical Backups

This is a copy of database files directly from disk. Tools — Percona XtraBackup, Mariabackup, LVM snapshots.

Pros: fast restoration (just copy files back), minimal load during backup. Cons: tied to MySQL version, can't restore individual tables (usually).

Full, Incremental, and Differential

Besides the copying method, backups differ by scope:

• Full — copy of the entire database. Simplest but largest in size.
• Incremental — only changes since the last backup. Fast and compact, but restoration requires full + all increments in sequence.
• Differential — changes since the last full backup. Compromise between size and restoration simplicity.

For most projects, the optimal combination is: full backup once a week + daily incrementals.

The 3-2-1 Rule: The Gold Standard of Backups

One of the most common mistakes is storing the backup on the same server where the database runs. The disk dies — and you lose both the database and the backup at once.

The 3-2-1 strategy solves this:

• 3 copies of data (original + 2 backups)
• 2 types of media (e.g., local disk + cloud storage)
• 1 copy offsite (physically in a different location/data center)

Hostiserver offers Backup Hosting specifically for this purpose — specialized storage based on HDD arrays with high fault tolerance, accessible via FTP/SFTP/Rsync. It's most commonly used by eCommerce projects, media portals, and SaaS platforms. We help set up automatic transfer of copies immediately after they're created on the local server.

Retention Policy: The GFS Scheme

We use the "Grandfather-Father-Son" (GFS) scheme — a balance between archive depth and storage costs:

For critical data (financial transactions, medical records), daily copy retention is extended to 30 days.

mysqldump: Reliable Classic

mysqldump is a tool that exists on every server with MySQL. Simple, tested over the years, and for databases up to 10-20 GB — perfectly sufficient.

Basic Backup

mysqldump -u root -p --single-transaction --routines --triggers --events --hex-blob mydb > /backup/mydb_$(date +%Y%m%d_%H%M).sql

Let's break down the parameters — this is the standard we use at Hostiserver by default:

• --single-transaction — mandatory for InnoDB. Creates backup without locking tables, site continues working.
• --routines — includes stored procedures and functions (business logic at DB level).
• --triggers — includes triggers.
• --events — includes MySQL scheduled events.
• --hex-blob — correct handling of binary data (BLOB fields). Without it, binary data may get corrupted during restoration.

If you need replication or PITR, add --master-data=2 — it will record the binary log position as a comment in the dump.

Backup with Compression

mysqldump -u root -p --single-transaction mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

SQL dumps compress 5-10x. A 2 GB database becomes a 200-400 MB file.

Backup All Databases at Once

mysqldump -u root -p --single-transaction --all-databases --routines --triggers | gzip > /backup/all_db_$(date +%Y%m%d).sql.gz

Restoration

# From regular file
mysql -u root -p mydb < /backup/mydb_20260302.sql
# From gzip archive
gunzip < /backup/mydb_20260302.sql.gz | mysql -u root -p mydb

Percona XtraBackup: Backups Without Stopping the Database

For production servers with databases from 50 GB, where even a few seconds of locking is a problem, there's Percona XtraBackup. It's an open-source tool that makes "hot" physical backups of InnoDB without stopping MySQL.

How it works: XtraBackup copies InnoDB files and simultaneously writes redo logs. Then during the prepare stage, it applies these logs to get a consistent state. All this — while the database continues processing queries.

Full Backup

# Create backup
xtrabackup --backup --target-dir=/backup/full
# Prepare (required before restoration!)
xtrabackup --prepare --target-dir=/backup/full
# Restore
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Incremental Backup

# First, full backup
xtrabackup --backup --target-dir=/backup/full
# Monday's increment
xtrabackup --backup --target-dir=/backup/inc_mon --incremental-basedir=/backup/full
# Tuesday's increment
xtrabackup --backup --target-dir=/backup/inc_tue --incremental-basedir=/backup/inc_mon

Incremental backups copy only changed InnoDB pages — so they're much smaller and faster than full backups.

Automation: Backups That Work Without You

Manual backups are backups that get forgotten. Automation through cron is the minimum that should be on every server.

Daily Backup via cron

# Edit crontab
crontab -e
# Backup every day at 3:00 AM
0 3 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

Backup Script

#!/bin/bash
# /usr/local/bin/backup_mysql.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M)
RETENTION_DAYS=14
# Create backup
mysqldump -u backup_user -p'secure_password' \
  --single-transaction --routines --triggers --events --hex-blob \
  --all-databases | gzip > "$BACKUP_DIR/all_db_$DATE.sql.gz"
# Check success
if [ $? -eq 0 ]; then
  echo "[$DATE] Backup created successfully: $(du -h $BACKUP_DIR/all_db_$DATE.sql.gz | cut -f1)"
else
  echo "[$DATE] ERROR: backup not created!" >& 2
  # Can add alert sending here
fi
# Delete old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$DATE] Deleted backups older than $RETENTION_DAYS days"

[mysqldump]
user=backup_user
password=secure_password

Backup Monitoring

A backup system that's silent is a backup system that might be broken. Add checks:

• Alert if backup file is smaller than expected size (empty dump = problem)
• Alert if backup didn't appear on schedule
• Weekly integrity check: gunzip -t backup.sql.gz

Point-in-Time Recovery: Restoring to a Specific Second

Imagine: at 14:35 a developer accidentally runs UPDATE without WHERE and overwrites all customer emails. The last full backup is from 3:00 AM. Without PITR, you lose everything that happened between 3:00 and 14:35 — orders, registrations, payments.

Point-in-Time Recovery solves this. The principle: MySQL records every change in the binary log. After restoring the full backup, you "replay" the binary logs up to the desired moment.

PITR is standard practice for Enterprise clients and projects where losing even an hour of data is critical (eCommerce, finance, SaaS). We configure binary log rotation and their regular transfer to Backup Hosting. This allows you to "roll forward" all changes that happened after the last nightly backup in case of disaster.

Configuring Binary Log

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 14
max_binlog_size = 100M

Recovery to a Specific Time

# 1. Restore full backup
mysql -u root -p mydb < /backup/full_20260302_0300.sql
# 2. Replay binary logs up to the moment BEFORE the error
mysqlbinlog --stop-datetime="2026-03-02 14:34:59" \
  /var/log/mysql/mysql-bin.000042 \
  /var/log/mysql/mysql-bin.000043 | mysql -u root -p mydb

Result: database restored with all data up to 14:34:59 — one second before the error.

A Backup That Wasn't Tested Doesn't Exist

This is not an exaggeration. We've seen cases where a client was making backups via cron for months, and then during restoration it turned out the files were empty — because the MySQL password changed and mysqldump was silently failing.

How to Verify

• Daily: file size check (not zero? not anomalously small?)
• Weekly: test restoration to staging server
• Monthly: full test restoration with data verification

Automatic Verification

# Check that file is not empty and larger than 1 MB
BACKUP_FILE="/backup/mysql/all_db_$(date +%Y%m%d)*.sql.gz"
MIN_SIZE=1048576  # 1 MB in bytes
FILE_SIZE=$(stat -c%s $BACKUP_FILE 2>/dev/null || echo 0)
if [ "$FILE_SIZE" -lt "$MIN_SIZE" ]; then
  echo "ALERT: Backup suspiciously small or missing!" | mail -s "Backup Alert" admin@example.com
fi

Which Tool to Choose

Here's a comparison of the main MySQL/MariaDB backup tools for different scenarios:

5 Backup Mistakes We See Constantly

1. Backup on the same disk as the database. Disk dies — you lose everything. Always copy backups to a separate drive or cloud storage.

2. No one tests the backups. Cron silently fails for months. Add file size monitoring and error alerts.

3. Backup without --single-transaction. mysqldump locks tables — site goes down. For InnoDB, always add this flag.

4. Forgot about procedures and triggers. mysqldump by default does NOT include stored procedures, triggers, and events. Use --routines --triggers --events.

5. Password in plaintext in crontab. Anyone with server access can see the password via ps aux. Use ~/.my.cnf with 600 permissions.

Frequently Asked Questions