database-backup-restore

仓库: aj-geddes/useful-ai-prompts
安装量: 163
排名: #5300

安装

npx skills add https://github.com/aj-geddes/useful-ai-prompts --skill database-backup-restore

Database Backup & Restore Overview

Implement comprehensive backup and disaster recovery strategies. Covers backup types, retention policies, restore testing, and recovery time objectives (RTO/RPO).

When to Use Backup automation setup Disaster recovery planning Recovery testing procedures Backup retention policies Point-in-time recovery (PITR) Cross-region backup replication Compliance and audit requirements PostgreSQL Backup Strategies Full Database Backup

pg_dump - Text Format:

Simple full backup

pg_dump -h localhost -U postgres -F p database_name > backup.sql

With compression

pg_dump -h localhost -U postgres -F p database_name | gzip > backup.sql.gz

Backup with verbose output

pg_dump -h localhost -U postgres -F p -v database_name > backup.sql 2>&1

Exclude specific tables

pg_dump -h localhost -U postgres database_name \ --exclude-table=temp_* --exclude-table=logs > backup.sql

pg_dump - Custom Binary Format:

Custom binary format (better for large databases)

pg_dump -h localhost -U postgres -F c database_name > backup.dump

Parallel jobs for faster backup (PostgreSQL 9.3+)

pg_dump -h localhost -U postgres -F c -j 4 \ --load-via-partition-root database_name > backup.dump

Backup specific schema

pg_dump -h localhost -U postgres -n public database_name > backup.dump

Get backup info

pg_dump_all -h localhost -U postgres > all_databases.sql

pg_basebackup - Physical Backup:

Take base backup for streaming replication

pg_basebackup -h localhost -D ./backup_data -U replication_user -v -P

Label backup for archival

pg_basebackup -h localhost -D ./backup_data \ -U replication_user -l "backup_$(date +%Y%m%d)" -v -P

Tar format with compression

pg_basebackup -h localhost -D - -U replication_user \ -Ft -z -l "backup_$(date +%s)" | tar -xz -C ./backups/

Incremental & Differential Backups

WAL Archiving Setup:

-- postgresql.conf configuration -- wal_level = replica -- archive_mode = on -- archive_command = 'test ! -f /archive/%f && cp %p /archive/%f' -- archive_timeout = 300

-- Monitor WAL archiving SELECT name, setting FROM pg_settings WHERE name LIKE 'archive%';

-- Check WAL directory -- ls -lh $PGDATA/pg_wal/

-- List archived WALs -- ls -lh /archive/

Continuous WAL Backup:

!/bin/bash

Backup script with WAL archiving

BACKUP_DIR="/backups" DB_NAME="production" TIMESTAMP=$(date +%Y%m%d_%H%M%S)

Create base backup

pg_basebackup -h localhost -D $BACKUP_DIR/base_$TIMESTAMP \ -U backup_user -v

Archive WAL files

WAL_DIR=$BACKUP_DIR/wal_$TIMESTAMP mkdir -p $WAL_DIR cp /var/lib/postgresql/14/main/pg_wal/* $WAL_DIR/

Compress backup

tar -czf $BACKUP_DIR/backup_$TIMESTAMP.tar.gz \ $BACKUP_DIR/base_$TIMESTAMP $BACKUP_DIR/wal_$TIMESTAMP

Verify backup

pg_basebackup -h localhost -U backup_user --analyze

Upload to S3

aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.tar.gz \ s3://backup-bucket/postgres/

MySQL Backup Strategies Full Database Backup

mysqldump - Text Format:

Simple full backup

mysqldump -h localhost -u root -p database_name > backup.sql

All databases

mysqldump -h localhost -u root -p --all-databases > all_databases.sql

With flush privileges and triggers

mysqldump -h localhost -u root -p \ --flush-privileges --triggers --routines \ database_name > backup.sql

Parallel backup (MySQL 5.7.11+)

mydumper -h localhost -u root -p password \ -o ./backup_dir --threads 4 --compress

Backup Specific Tables:

Backup specific tables

mysqldump -h localhost -u root -p database_name table1 table2 > tables.sql

Exclude tables

mysqldump -h localhost -u root -p database_name \ --ignore-table=database_name.temp_table \ --ignore-table=database_name.logs > backup.sql

Binary Log Backups

Enable Binary Logging:

-- Check binary logging status SHOW VARIABLES LIKE 'log_bin%';

-- Configure in my.cnf -- [mysqld] -- log-bin = mysql-bin -- binlog_format = ROW

-- View binary logs SHOW BINARY LOGS;

-- Get current position SHOW MASTER STATUS;

Binary Log Backup:

Backup binary logs

MYSQL_PWD="password" mysqldump -h localhost -u root \ --single-transaction --flush-logs --all-databases > backup.sql

Copy binary logs

cp /var/log/mysql/mysql-bin.* /backup/binlogs/

Backup incremental changes

mysqlbinlog /var/log/mysql/mysql-bin.000001 > binlog_backup.sql

Restore Procedures PostgreSQL Restore

Restore from Text Backup:

Drop and recreate database

psql -h localhost -U postgres -c "DROP DATABASE IF EXISTS database_name;" psql -h localhost -U postgres -c "CREATE DATABASE database_name;"

Restore from text backup

psql -h localhost -U postgres database_name < backup.sql

Restore with verbose output

psql -h localhost -U postgres -1 database_name < backup.sql 2>&1 | tee restore.log

Restore from Binary Backup:

Restore from custom format

pg_restore -h localhost -U postgres -d database_name \ -v backup.dump

Parallel restore (faster)

pg_restore -h localhost -U postgres -d database_name \ -j 4 -v backup.dump

Dry run (test restore without committing)

pg_restore --list backup.dump > restore_plan.txt

Point-in-Time Recovery (PITR):

List available backups and WAL archives

ls -lh /archive/

Restore to specific point in time

pg_basebackup -h localhost -D ./recovery_data \ -U replication_user -c fast

Create recovery.conf

cat > ./recovery_data/recovery.conf << EOF recovery_target_timeline = 'latest' recovery_target_xid = '1000000' recovery_target_time = '2024-01-15 14:30:00' recovery_target_name = 'before_bad_update' EOF

Start PostgreSQL with recovery

pg_ctl -D ./recovery_data start

MySQL Restore

Restore from SQL Backup:

Restore full database

mysql -h localhost -u root -p < backup.sql

Restore specific database

mysql -h localhost -u root -p database_name < database_backup.sql

Restore with progress

pv backup.sql | mysql -h localhost -u root -p database_name

Restore with Binary Logs:

Restore from backup then apply binary logs

mysql -h localhost -u root -p < backup.sql

Get starting binary log position from backup

grep "SET @@GLOBAL.GTID_PURGED=" backup.sql

Apply binary logs after backup

mysqlbinlog /var/log/mysql/mysql-bin.000005 \ --start-position=12345 | \ mysql -h localhost -u root -p database_name

Point-in-Time Recovery:

Restore base backup

mysql -h localhost -u root -p database_name < base_backup.sql

Apply binary logs up to specific time

mysqlbinlog /var/log/mysql/mysql-bin.000005 \ --stop-datetime='2024-01-15 14:30:00' | \ mysql -h localhost -u root -p database_name

Backup Validation

PostgreSQL - Backup Integrity Check:

Verify backup file

pg_dump --analyze --schema-only database_name > /dev/null && echo "Backup OK"

Test restore procedure

createdb test_restore pg_restore -d test_restore backup.dump psql -d test_restore -c "SELECT COUNT(*) FROM information_schema.tables;" dropdb test_restore

MySQL - Backup Integrity:

Check backup file syntax

mysql -h localhost -u root -p < backup.sql --dry-run

Verify checksum

md5sum backup.sql

Save checksum: echo "abc123def456 backup.sql" > backup.sql.md5

md5sum -c backup.sql.md5

Automated Backup Schedule

PostgreSQL - Cron Backup:

!/bin/bash

backup.sh - Daily backup script

BACKUP_DIR="/backups/postgresql" RETENTION_DAYS=30 TIMESTAMP=$(date +%Y%m%d_%H%M%S)

Create backup

pg_dump -h localhost -U postgres mydb | gzip > \ $BACKUP_DIR/backup_$TIMESTAMP.sql.gz

Delete old backups

find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

Upload to S3

aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.sql.gz \ s3://backup-bucket/postgresql/

Log backup

echo "$TIMESTAMP: Backup completed" >> /var/log/db_backup.log

Crontab Entry:

Daily backup at 2 AM

0 2 * * * /scripts/backup.sh

Hourly backup

0 * * * * /scripts/hourly_backup.sh

Weekly full backup

0 3 0 * * /scripts/weekly_backup.sh

Backup Retention Policy

PostgreSQL - Retention Strategy:

-- Create retention tracking CREATE TABLE backup_retention_policy ( backup_id UUID PRIMARY KEY, database_name VARCHAR(255), backup_date TIMESTAMP, backup_type VARCHAR(20), -- 'full', 'incremental', 'wal' retention_days INT, expires_at TIMESTAMP GENERATED ALWAYS AS (backup_date + INTERVAL '1 day' * retention_days) STORED );

-- Example retention periods INSERT INTO backup_retention_policy VALUES ('backup-001', 'production', NOW(), 'full', 30), ('backup-002', 'production', NOW(), 'incremental', 7), ('backup-003', 'staging', NOW(), 'full', 7);

-- Query expiring backups SELECT backup_id, expires_at FROM backup_retention_policy WHERE expires_at < NOW();

RTO/RPO Planning Recovery Time Objective (RTO): How quickly must the system recover Recovery Point Objective (RPO): How much data loss is acceptable

Example: - RTO: 1 hour (system must be recovered within 1 hour) - RPO: 15 minutes (no more than 15 minutes of data loss acceptable)

Backup frequency: Every 15 minutes (to meet RPO) Replication lag: < 5 minutes (for RTO)

Best Practices Checklist

✅ DO test restore procedures regularly ✅ DO implement automated backups ✅ DO monitor backup success ✅ DO encrypt backup files ✅ DO store backups offsite ✅ DO document recovery procedures ✅ DO track backup retention policies ✅ DO monitor backup performance

❌ DON'T rely on untested backups ❌ DON'T skip backup verification ❌ DON'T store backups on same server ❌ DON'T use weak encryption ❌ DON'T forget backup retention limits

Resources PostgreSQL Backup & Restore MySQL Backup & Recovery Percona Backup for MongoDB AWS RDS Automated Backups

← 返回排行榜