Skip to main content
Back to Category

Database Backup: MySQL and PostgreSQL Dump Guide

MySQL and PostgreSQL database backup and restore using mysqldump and pg_dump. Automated backups with cron, restore procedures, and best practices to prevent data loss.

Read time: 13 min Server Management
mysqlpostgresqlbackupdumprestorecrondatabasemysqldump
Author
REXE Teknoloji Network & Security Team
Editor
REXE Teknoloji Technical Editorial
First published
Last updated

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

  1. Regular backups: Daily or hourly for critical databases
  2. Compression: Use gzip to save disk space
  3. Remote storage: Store backups on a different server or cloud
  4. Retention policy: Keep 7 daily, 4 weekly, 12 monthly backups
  5. Encryption: Encrypt backups containing sensitive data
  6. Monitoring: Email alerts for backup failures
  7. 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.

Frequently Asked Questions

What's the difference between mysqldump and pg_dump?

mysqldump is the backup tool for MySQL and MariaDB. pg_dump is specific to PostgreSQL. Both produce SQL output, but pg_dump also supports custom binary format (-Fc) and directory format (-Fd). Binary format enables faster restores and parallel processing.

How do I back up large databases?

For MySQL, use --single-transaction to consistently back up InnoDB tables. For PostgreSQL, use -Fc (custom format) and pg_restore -j for parallel restore. For very large databases, consider Percona XtraBackup (MySQL) or pg_basebackup (PostgreSQL).

How often should I back up?

It depends on criticality: hourly for e-commerce or financial data; daily for blogs or content sites. Define based on your RPO (Recovery Point Objective). General recommendation: daily full backup + hourly incremental backup.

Where should I store backups?

Follow the 3-2-1 rule: 3 copies, 2 different media types, 1 offsite location. Local disk + remote server + cloud storage (S3, Backblaze B2) is the ideal combination. Backups stored only on the same server are useless if the server fails.

Does the database lock during backup?

With MySQL's --single-transaction flag, InnoDB tables don't lock. MyISAM tables may require a brief lock. PostgreSQL's pg_dump uses MVCC and doesn't lock tables. Avoid running backups during peak hours.

Related Articles

Network TrafficInbound— GbpsOutbound— Gbps