mysqldump / Restore

# 1) Logical backup — mysqldump
mysqldump -u root -p --databases app > app.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p app users orders > users_orders.sql

# Key flags for production:
mysqldump \\
    --single-transaction        # InnoDB consistent snapshot (no table locks)
    --quick                      # row-by-row (less RAM)
    --skip-lock-tables           # avoid locks (with single-transaction)
    --routines                   # include stored procedures
    --triggers                   # include triggers
    --events                     # include scheduled events
    --hex-blob                   # binary-safe
    --set-gtid-purged=OFF        # for replica setup
    --master-data=2              # record bin-log position (for PITR / replicas)
    --column-statistics=0        # avoid MySQL 8 compat issue against MariaDB
    -u backup -p$BACKUP_PW app > app-$(date +%Y%m%d-%H%M%S).sql

# Restore
mysql -u root -p app < app.sql

# 2) Compress on the fly + write to S3
mysqldump --single-transaction --quick app | \\
    gzip -9 | \\
    aws s3 cp - s3://backups/app-$(date +%Y%m%d).sql.gz

# 3) mysqlpump — parallel dumps (MySQL 5.7+; deprecated in 8.0.34+, removed eventually)
mysqlpump --default-parallelism=4 --include-databases=app > app.sql

# 4) Physical backup — xtrabackup (Percona)
# Copies raw data files; much faster than mysqldump for large DBs.

xtrabackup --backup --target-dir=/backup/full --user=backup --password=$PW
xtrabackup --prepare --target-dir=/backup/full         # apply logs; ready to restore

# Restore (instance stopped, datadir empty):
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# Incremental:
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1

# 5) Binary logs — Point-In-Time Recovery (PITR)
# Enable binlog in my.cnf:
[mysqld]
server-id      = 1
log_bin        = /var/log/mysql/mysql-bin
binlog_format  = ROW
binlog_expire_logs_seconds = 604800   # keep 7 days

# After restoring a backup, apply binlogs from the backup point to desired moment:
mysqlbinlog \\
    --start-position=120 \\
    --stop-datetime='2024-01-15 14:00:00' \\
    /var/log/mysql/mysql-bin.000042 \\
    /var/log/mysql/mysql-bin.000043 | mysql -u root -p

# This rolls forward to 14:00 — recover from accidental DROP at 14:01.

# 6) RPO + RTO planning
# RPO (Recovery Point Objective) — how much data loss is acceptable?
#   • Daily mysqldump → 24-hour RPO
#   • + binlog every minute → 1-minute RPO
#   • Streaming replication → near-zero RPO (with safety risk)
#
# RTO (Recovery Time Objective) — how fast must we recover?
#   • mysqldump restore: hours for big DBs
#   • xtrabackup restore: minutes (copy files)
//   • Hot standby (replica): seconds (failover)

# 7) Strategy patterns
# • Daily mysqldump → upload to S3 (cheap, slow restore, good for compliance archive)
# • xtrabackup nightly + hourly binlog ship → faster restore
# • Async replica → instant failover, then promote replica
# • Sync replication (Galera/InnoDB Cluster) → HA without backup-restore for outages

# 8) Verifying backups
# Untested backups don't exist. Quarterly restore drill:
#   1. Spin up new instance
#   2. Restore latest backup
#   3. Apply binlogs to recent point
//   4. Run smoke tests (SELECT count(*), check tables)
//   5. Decommission

# Automate this in CI/CD or as a scheduled job.

# 9) AWS RDS / Aurora
# • Automated daily snapshots + 5-min binlog snapshots
# • PITR within 35-day retention
# • Cross-region copy via AWS Backup
# • Restore = new instance from snapshot (no in-place restore)
# • Aurora Backtrack — rewind cluster up to 72h without restore

# 10) Encryption
mysqldump ... | gzip | gpg --encrypt --recipient backups@example.com > app.sql.gz.gpg
# Or rely on S3 server-side encryption + KMS
aws s3 cp app.sql s3://backups/ --sse aws:kms --sse-kms-key-id alias/backups

# 11) Retention + lifecycle
# • 7 daily, 4 weekly, 12 monthly, 7 yearly
# • S3 lifecycle rules: Standard → Glacier after 30d → expire after 7y
# • Test restoring from EACH tier — Glacier retrieval is slow

# 12) Granular restore
# Need only ONE table back?
# mysqldump --no-create-info --where='id in (1,2,3)' app users > rows.sql
# Or restore the full backup to a temp DB and SELECT INTO.

# 13) Backup user privileges
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'pw';
GRANT SELECT, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER
    ON *.* TO 'backup'@'localhost';

# Minimal permissions for mysqldump --single-transaction.

# 14) Monitoring backup health
# • Alert if backup older than X hours
# • Alert if restore time > target RTO
# • Alert on backup size anomaly (compression rate drop = data corruption?)
# • Log backup duration trends

# 15) Common bugs
# • mysqldump without --single-transaction → locks all tables during dump → outage
# • Forgetting binary logs → can't do PITR; only point-in-time of backup
# • Backup file in same disk as DB — disk failure loses both; ship offsite
# • No restore test → discovers broken backup at 3am incident
# • Restoring with NEW server-id but copied auto_increment → ID collision
# • Backup with mysqldump 8 against MySQL 5.7 — version mismatch errors
# • Compression but no encryption → backups in cloud storage readable to anyone with bucket access
# • Retention deletes the only working backup before drill verifies it
# • Forgetting to back up grants + users — mysql.user table needs explicit include
# • binlog_expire too short → can't restore beyond expiry; lengthen for safety

# Backup
mysqldump -u root -p myapp > myapp.sql
# Restore
mysql -u root -p myapp < myapp.sql
# Compressed
mysqldump myapp | gzip > myapp.sql.gz