Database_backup

Chapter 74: Database Backup and Restore

Comprehensive Database Backup Strategies

---

74.1 MySQL/MariaDB Backup

mysqldump

# Full backup
mysqldump -u root -p --all-databases > full_backup.sql

# Single database
mysqldump -u root -p mydb > mydb.sql

# Multiple databases
mysqldump -u root -p --databases db1 db2 db3 > backup.sql

# With options
mysqldump -u root -p \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    mydb > mydb.sql

# Quick backup (no locks)
mysqldump -u root -p --single-transaction --quick mydb > backup.sql

# Lock tables (for MyISAM)
mysqldump -u root -p --lock-tables mydb > backup.sql

# Compressed backup
mysqldump -u root -p mydb | gzip > backup.sql.gz

# Partial backup (only tables)
mysqldump -u root -p mydb table1 table2 > tables.sql

Restore

# Restore database
mysql -u root -p mydb < backup.sql

# Restore compressed
gunzip < backup.sql.gz | mysql -u root -p

# Restore to different database
mysql -u root -p newdb < backup.sql

# Execute SQL directly
mysql -u root -p -e "source backup.sql"

Binary (Physical) Backup

# Using xtrabackup (Percona)
# Install
sudo apt install percona-xtrabackup

# Full backup
xtrabackup --backup --target-dir=/backup/full --user=root --password=root

# Prepare backup
xtrabackup --prepare --target-dir=/backup/full

# Restore
xtrabackup --copy-back --target-dir=/backup/full

# Incremental backup
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# Stream backup
xtrabackup --backup --stream=tar | gzip > backup.tar.gz

---

74.2 PostgreSQL Backup

pg_dump

# Plain SQL backup
pg_dump -U user mydb > backup.sql
pg_dump -U user mydb -f backup.sql

# Custom format (compressed)
pg_dump -U user -Fc mydb > backup.dump

# Directory format (parallel)
pg_dump -U user -Fd mydb -f backup_dir

# Only schema
pg_dump -U user --schema-only mydb > schema.sql

# Only data
pg_dump -U user --data-only mydb > data.sql

# Compressed
pg_dump -U user mydb | gzip > backup.sql.gz

Restore

# Plain SQL restore
psql -U user mydb < backup.sql

# Custom format restore
pg_restore -U user -d mydb backup.dump

# Restore to different database
createdb -U user newdb
pg_restore -U user -d newdb backup.dump

# Restore specific table
pg_restore -U user -d mydb --data-only --table=mytable backup.dump

pg_basebackup (Physical)

# Full base backup
pg_basebackup -h localhost -U replicator -D /backup/base -P -Xs

# With tar format
pg_basebackup -h localhost -U replicator -D /backup -Ft -z -P

# With replication slot
pg_basebackup -h master -D /backup -U replicator -P --slot=replica_slot

---

74.3 Automation

Backup Script Example

/usr/local/bin/db-backup.sh

#!/bin/bash
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
DB_USER="backup"
DB_PASS="password"

# MySQL backup
mysqldump -u $DB_USER -p$DB_PASS --single-transaction \
    --routines --triggers $DB_NAME | gzip > \
    $BACKUP_DIR/mysql_${DB_NAME}_${DATE}.sql.gz

# PostgreSQL backup
pg_dump -U $DB_USER -Fc $DB_NAME | gzip > \
    $BACKUP_DIR/pg_${DB_NAME}_${DATE}.dump.gz

# Keep only 7 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
find $BACKUP_DIR -name "*.dump.gz" -mtime +7 -delete

# Log
echo "Backup completed: $DATE" >> /var/log/backup.log

Cron Schedule

/etc/cron.d/database-backup

# Daily at 2 AM
0 2 * * * root /usr/local/bin/db-backup.sh

# Every 6 hours
# 0 */6 * * * root /usr/local/bin/db-backup.sh

---

74.4 Interview Questions

Basic Questions

1. What is mysqldump?

   • MySQL logical backup tool

2. What’s the difference between pg_dump and pg_basebackup?

   • pg_dump: logical (SQL), pg_basebackup: physical

3. What does —single-transaction do?

   • Wraps dump in transaction for consistent backup

4. What is point-in-time recovery?

   • Restoring to specific point using WAL

5. How do you automate backups?

   • Cron jobs with backup scripts

---

Summary

                 Quick Reference
+------------------------------------------------------------------+
|                                                                   |
|    MySQL:                                                          |
|    +----------------------------------------------------------+  |
|    | mysqldump -u user -p db > backup.sql                   |  |
|    | mysql -u user -p db < backup.sql                        |  |
|    | xtrabackup --backup --target-dir=/backup               |  |
|    +----------------------------------------------------------+  |
|                                                                   |
|    PostgreSQL:                                                     |
|    +----------------------------------------------------------+  |
|    | pg_dump -U user db > backup.sql                         |  |
|    | psql -U user db < backup.sql                           |  |
|    | pg_basebackup -D /backup                              |  |
|    +----------------------------------------------------------+  |
|                                                                   |
+------------------------------------------------------------------+