Database Backup: MySQL and PostgreSQL Dump Guide

Database backup is one of the most critical server administration tasks. Regular backups are essential to recover from hardware failure, human error, or cyberattacks. This guide covers backup and restore procedures for both MySQL and PostgreSQL.

Why Database Backup?

Hardware failure or disk corruption
Accidentally deleted data
Issues after software updates
Cyberattacks or ransomware
Copying data to development/test environments

Without backups, data loss is irreversible. Follow the 3-2-1 rule: 3 copies, 2 different media types, 1 offsite location.

MySQL / MariaDB Backup

Basic Backup with mysqldump

hljs bash

# Backup a single database
mysqldump -u root -p database_name > backup.sql

# With password inline (for scripts)
mysqldump -u root -pPASSWORD database_name > backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Backup specific tables
mysqldump -u root -p database_name table1 table2 > tables.sql

# Compressed backup
mysqldump -u root -p database_name | gzip > backup_$(date +%Y%m%d).sql.gz

Advanced mysqldump Options

hljs bash

# Consistent backup for large databases (with transaction)
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  database_name > full_backup.sql

# Structure only (no data)
mysqldump -u root -p --no-data database_name > schema.sql

# Data only (no structure)
mysqldump -u root -p --no-create-info database_name > data.sql

# Backup from remote server
mysqldump -h remote_server -u user -p database_name > remote_backup.sql

MySQL Restore

hljs bash

# Restore from SQL file
mysql -u root -p database_name < backup.sql

# Restore from compressed backup
gunzip -c backup.sql.gz | mysql -u root -p database_name

# Create new database and restore
mysql -u root -p -e "CREATE DATABASE new_database;"
mysql -u root -p new_database < backup.sql

# Restore all databases
mysql -u root -p < all_databases.sql

Secure Credential Storage

hljs bash

# Create ~/.my.cnf
nano ~/.my.cnf

hljs ini

[client]
user=root
password=YOUR_PASSWORD
host=localhost

hljs bash

# Restrict file permissions
chmod 600 ~/.my.cnf

# Now use without password prompt
mysqldump database_name > backup.sql

PostgreSQL Backup

Basic Backup with pg_dump

hljs bash

# Backup a single database
pg_dump -U postgres database_name > backup.sql

# Compressed custom format (recommended)
pg_dump -U postgres -Fc database_name > backup.dump

# Directory format (for parallel restore)
pg_dump -U postgres -Fd database_name -f backup_dir/

# Backup all databases (including roles)
pg_dumpall -U postgres > all_pg.sql

# Compressed backup
pg_dump -U postgres database_name | gzip > backup_$(date +%Y%m%d).sql.gz

Advanced pg_dump Options

hljs bash

# Backup specific schema
pg_dump -U postgres -n public database_name > public_schema.sql

# Backup specific tables
pg_dump -U postgres -t table1 -t table2 database_name > tables.sql

# Structure only
pg_dump -U postgres -s database_name > schema.sql

# Data only
pg_dump -U postgres -a database_name > data.sql

# Backup from remote server
pg_dump -h remote_server -U postgres -p 5432 database_name > remote_backup.sql

PostgreSQL Restore

hljs bash

# Restore from SQL format
psql -U postgres database_name < backup.sql

# Restore custom format (.dump)
pg_restore -U postgres -d database_name backup.dump

# Parallel restore (faster)
pg_restore -U postgres -d database_name -j 4 backup.dump

# Create new database and restore
psql -U postgres -c "CREATE DATABASE new_database;"
pg_restore -U postgres -d new_database backup.dump

# Restore from compressed backup
gunzip -c backup.sql.gz | psql -U postgres database_name

Passwordless Access (.pgpass)

hljs bash

# Create ~/.pgpass
nano ~/.pgpass

# Format: host:port:database:user:password
localhost:5432:*:postgres:YOUR_PASSWORD

hljs bash

# Restrict file permissions
chmod 600 ~/.pgpass

Automated Backups with Cron

MySQL Automated Backup Script

hljs bash

# Create /root/scripts/mysql-backup.sh
mkdir -p /root/scripts
nano /root/scripts/mysql-backup.sh

hljs bash

#!/bin/bash

# Configuration
DB_USER="root"
DB_PASS="YOUR_PASSWORD"
DB_NAME="database_name"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

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

# Delete old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

hljs bash

# Make script executable
chmod +x /root/scripts/mysql-backup.sh

# Test it
/root/scripts/mysql-backup.sh

# Add to crontab (every night at 02:00)
crontab -e
0 2 * * * /root/scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

PostgreSQL Automated Backup Script

hljs bash

#!/bin/bash

# Configuration
DB_USER="postgres"
DB_NAME="database_name"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

# Take backup
pg_dump -U $DB_USER -Fc $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Compress
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Delete old backups
find $BACKUP_DIR -name "*.dump.gz" -mtime +$RETENTION_DAYS -delete

echo "PostgreSQL backup completed: ${DB_NAME}_${DATE}.dump.gz"

Sending Backups to Remote Server

hljs bash

# Copy to remote server with rsync
rsync -avz /var/backups/mysql/ user@remote-server:/backup/mysql/

# Send single file with scp
scp /var/backups/mysql/backup.sql.gz user@remote-server:/backup/

# Send to cloud storage with rclone (S3, Google Drive, etc.)
rclone copy /var/backups/mysql/ remote:bucket-name/mysql/

Backup Verification

hljs bash

# Check backup file size
ls -lh /var/backups/mysql/

# Verify by restoring to test database
mysql -u root -p -e "CREATE DATABASE test_restore;"
mysql -u root -p test_restore < backup.sql
mysql -u root -p -e "SHOW TABLES;" test_restore
mysql -u root -p -e "DROP DATABASE test_restore;"

Test your backups regularly. A backup you can't restore is useless. Perform a test restore at least once a month.

Best Practices

Regular backups: Daily or hourly for critical databases
Compression: Use gzip to save disk space
Remote storage: Store backups on a different server or cloud
Retention policy: Keep 7 daily, 4 weekly, 12 monthly backups
Encryption: Encrypt backups containing sensitive data
Monitoring: Email alerts for backup failures
Testing: Regularly verify backups by restoring them

Conclusion

Regular database backups are the most important defense against data loss. By setting up automated backup scripts for MySQL and PostgreSQL and storing backups offsite, you can keep your data safe on REXE servers.

Database Backup: MySQL and PostgreSQL Dump Guide

Table of Contents