How to Recover Lost MySQL Data: Physical, Logical, and PITR Backup Guide

1. Physical and Logical Backup Details

1.1 Logical Backup

Concept: Logical backup exports data using SQL statements; tools: mysqldump and mydumper.

Advantages:

Highly portable across MySQL versions and architectures.

Backup files are plain text and editable.

Selective backup of individual tables or databases.

Disadvantages:

Slower restore speed.

Higher CPU consumption during backup.

Long duration for large databases.

Examples:

# Single‑database backup
mysqldump -uroot -p --single-transaction --master-data=2 mydb > mydb_backup.sql

# Multiple‑database backup
mysqldump -uroot -p --databases db1 db2 > dbs_backup.sql

# Full‑database backup
mysqldump -uroot -p --all-databases > all_db_backup.sql

# Backup only schema
mysqldump -uroot -p --no-data mydb > mydb_schema.sql

# Backup only data
mysqldump -uroot -p --no-create-info mydb mytable > mytable_data.sql

# Compressed backup
mysqldump -uroot -p mydb | gzip > mydb_backup.sql.gz

# Multi‑threaded backup with mydumper
mydumper -u root -p -B mydb -o /backup/mydb/ -t 4

Logical Backup Restoration:

# Restore full backup
mysql -uroot -p mydb < mydb_backup.sql

# Restore compressed backup
gunzip < mydb_backup.sql.gz | mysql -uroot -p mydb

# Use source command inside mysql client
source /path/to/backup.sql

# Restore with mydumper
myloader -u root -p -B mydb -d /backup/mydb/ -t 4

1.2 Physical Backup

Concept: Physical backup copies MySQL data files (ibdata1, .ibd, .frm, etc.) directly, typically using XtraBackup or filesystem snapshots.

Advantages:

Fast backup and restore.

Better support for large databases.

Minimal impact on running system.

Disadvantages:

Less portable; depends on specific MySQL version and platform.

Backup files are large.

Usually requires stopping the service or special tools.

Physical Backup Types:

Cold backup – stop MySQL service and copy the data directory.

Warm backup – acquire a read lock; reads continue.

Hot backup – no impact on reads/writes (e.g., XtraBackup).

Example (cold backup):

# Stop MySQL
systemctl stop mysqld

# Copy data directory
cp -rp /var/lib/mysql /backup/mysql_bak_$(date +%Y%m%d)

# Start MySQL
systemctl start mysqld

2. XtraBackup Practical Guide

2.1 XtraBackup Principles

XtraBackup is an open‑source hot‑backup tool from Percona that supports InnoDB/XtraDB engines.

How it works:

Copies data files while a background process monitors InnoDB redo logs.

Records the starting Log Sequence Number (LSN).

Tracks redo log changes during copy.

Applies logs in a preparation phase to make data consistent.

2.2 Installation and Configuration

# 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

# Ubuntu
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
apt-get update
apt-get install percona-xtrabackup-80

# Verify installation
xtrabackup --version

2.3 Full Backup and Restore

# Create backup user and grant privileges
mysql -uroot -p -e "CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'Backup@123'"
mysql -uroot -p -e "GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'"
mysql -uroot -p -e "GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost'"

# Full backup
xtrabackup --backup \
  --user=bkpuser \
  --password=Backup@123 \
  --target-dir=/backup/mysql/full_$(date +%Y%m%d_%H%M%S)

# Prepare backup (make data consistent)
xtrabackup --prepare --target-dir=/backup/mysql/full_20241201_143000

# Restore backup
systemctl stop mysqld
rm -rf /var/lib/mysql/*   # clear data directory
xtrabackup --copy-back --target-dir=/backup/mysql/full_20241201_143000
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

2.4 Incremental Backup

# Day 1: Full backup
xtrabackup --backup --user=bkpuser --password=Backup@123 --target-dir=/backup/mysql/full_backup

# Day 2: First incremental backup
xtrabackup --backup --user=bkpuser --password=Backup@123 --target-dir=/backup/mysql/inc1 \
  --incremental-basedir=/backup/mysql/full_backup

# Day 3: Second incremental backup
xtrabackup --backup --user=bkpuser --password=Backup@123 --target-dir=/backup/mysql/inc2 \
  --incremental-basedir=/backup/mysql/inc1

# Prepare for restore (apply logs in order)
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full_backup
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full_backup \
  --incremental-dir=/backup/mysql/inc1
xtrabackup --prepare --target-dir=/backup/mysql/full_backup \
  --incremental-dir=/backup/mysql/inc2

# Restore
xtrabackup --copy-back --target-dir=/backup/mysql/full_backup

2.5 Compressed and Streamed Backup

# Compressed backup
xtrabackup --backup --user=bkpuser --password=Backup@123 \
  --compress --compress-threads=4 \
  --target-dir=/backup/mysql/compressed_backup

# Decompress backup
xtrabackup --decompress --target-dir=/backup/mysql/compressed_backup

# Stream backup to remote server
xtrabackup --backup --user=bkpuser --password=Backup@123 \
  --stream=xbstream | ssh user@backup_server "cat > /backup/mysql/backup.xbstream"

# Parallel backup
xtrabackup --backup --user=bkpuser --password=Backup@123 \
  --parallel=4 --target-dir=/backup/mysql/parallel_backup

3. Point‑In‑Time Recovery (PITR) Details

3.1 PITR Principle

PITR uses binary logs (binlog) to restore the database to a specific point in time or transaction position.

Workflow:

Restore a full physical or logical backup.

Apply binlog events that occurred after the backup.

Stop applying logs at the desired timestamp or position.

Prerequisites:

Binary logging must be enabled.

A complete full backup must exist.

All binlog files generated after the backup must be retained.

3.2 Binlog Configuration

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog-format = ROW
expire_logs_days = 7
max_binlog_size = 1G
binlog_cache_size = 32K
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
binlog_do_db = mydb   # optional: only record specific databases

3.3 Binlog Management Commands

# Show binlog status
SHOW MASTER STATUS;
SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'log_bin%';

# View binlog contents
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

# Flush binlog
FLUSH LOGS;

# Set retention period
SET GLOBAL expire_logs_days = 7;

# Purge specific binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2024-12-01 00:00:00';

3.4 PITR with XtraBackup

# Restore full XtraBackup backup
xtrabackup --copy-back --target-dir=/backup/mysql/full_backup
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

# Get binlog position from backup
cat /backup/mysql/full_backup/xtrabackup_binlog_info
# Example output: mysql-bin.000020, 452156

# Apply binlog up to a specific datetime
mysqlbinlog --start-position=452156 \
  /var/log/mysql/mysql-bin.000020 \
  /var/log/mysql/mysql-bin.000021 \
  --stop-datetime="2024-12-01 15:30:00" | mysql -uroot -p

Core Backup Principles

3‑2‑1 backup rule: at least three copies, two media types, one off‑site.

Regular drills: perform a full restore test at least monthly.

Encryption everywhere: encrypt transfer, storage, and backup files.

Least‑privilege: grant only necessary permissions.

Audit trail: record all sensitive operations.