Skip to content

Instantly share code, notes, and snippets.

@antonioaguilar
Forked from lucaswilric/db_backup.sh
Created March 25, 2023 21:36
Show Gist options
  • Save antonioaguilar/0abbc6e0d7e8cdf836e120fbddac14e2 to your computer and use it in GitHub Desktop.
Save antonioaguilar/0abbc6e0d7e8cdf836e120fbddac14e2 to your computer and use it in GitHub Desktop.
Back up, compress and encrypt a small MySQL or PostgreSQL database.
#!/bin/bash
# This script dumps a database to SQL, compresses, encrypts and timestamps it, then saves it to Dropbox. Ideal for a cronjob.
# It uses symmetric encryption, so guard your password carefully.
#
# NOT RECOMMENDED FOR LARGE DATABASES!
# Dump a MySQL database
# Read this for how to get your password into mysqldump:
# https://dev.mysql.com/doc/refman/5.1/en/password-security-user.html
mysqldump -u username > database.sql
# Or dump a PostgreSQL database. For this one, you'll need to put your credentials
# in ~/.pgpass in this format:
#
# hostname:port:database:username:password
#
pg_dump --host=localhost --username=username database > database.sql
# Compress: -9 means best, slowest encryption.
gzip -N -9 database.sql
# Encrypt
openssl enc -aes-256-cbc -in database.sql.gz -out database.sql.gz.aes256cbc -pass file:db_encryption.pass
# Store
mv database.sql.gz.aes256cbc /home/user/Dropbox/db-backups/`date +%Y%m%d%H%M%S`_database.sql.gz.aes256cbc
# Clean up
rm database.sql.gz
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment