Creating a Database Backup Script for Debugging and Staging

Creating a reliable database backup script is essential for data integrity and can be invaluable for debugging and staging. This document outlines how to set up such scripts for MySQL and PostgreSQL databases on a utility server for a client.

MySQL Database Backup Script

Save the following script at ~/bin/database-backup.sh:

#!/bin/sh
# Purpose: Perform a MySQL database backup for debugging and staging
# Prerequisite: ~/.my.cnf must exist with database credentials

# Setup variables
BACKUP_DIR=/home/deploy/database-backups
DATABASE_NAME=client_db_production
BACKUP_FILE=$(date +"%Y%m%d")-$DATABASE_NAME.sql
DATABASE_HOST=database-host-address

# Cleanup older backup files
find $BACKUP_DIR -mtime +2 -name "*.sql" -delete
find $BACKUP_DIR -mtime +5 -name "*.sql.tar.gz" -delete

# Perform the database backup
mysqldump -h $DATABASE_HOST --set-gtid-purged=off $DATABASE_NAME > $BACKUP_DIR/$BACKUP_FILE

# Create a tarball of the SQL file
tar czvf $BACKUP_FILE.tar.gz $BACKUP_FILE

# Optionally, copy this to an S3 bucket (requires 'aws-cli' to be installed)
# aws s3 cp $BACKUP_DIR/$BACKUP_FILE.tar.gz s3://your-bucket/production-snapshots/$BACKUP_FILE.tar.gz

To execute the MySQL backup script, you must have a .my.cnf file in the home directory containing MySQL credentials. This should look something like:

[mysqldump]
user=DB_USERNAME
password=DB_PASSWORD

PostgreSQL Database Backup Script

Save the following script at ~/bin/database-backup.sh:

#!/bin/sh
# Purpose: Perform a PostgreSQL database backup for debugging and staging
# Prerequisite: ~/.pgpass must exist with database credentials

# Setup variables
BACKUP_DIR=/home/deploy/database-backups
DATABASE_NAME=client_db_production
BACKUP_DIRECTORY="$BACKUP_DIR/$DATABASE_NAME-$(date '+%Y%m%d')"
DATABASE_HOST=database-host-address
DATABASE_USER=db_user

# Perform the backup
pg_dump --jobs=4 --host=$DATABASE_HOST --port=5432 --username=$DATABASE_USER \
  --dbname=$DATABASE_NAME --format=d --file=$BACKUP_DIRECTORY

# Archive the directory into a single tarball
tar -czvf $BACKUP_DIRECTORY.tar.gz -C $BACKUP_DIRECTORY .

# Optionally, upload this to an S3 bucket
# aws s3 cp $BACKUP_DIRECTORY.tar.gz s3://your-bucket/production-snapshots/$BACKUP_DIRECTORY.tar.gz

For PostgreSQL, a .pgpass file should exist in the home directory with the following structure:

DB_HOST:DB_PORT:DB_NAME:DB_USERNAME:DB_PASSWORD

Frequently Asked Questions (FAQs)

Q: How do I add one of these scripts to a server?
A: You can copy and paste the script, modifying it to match the credentials, hostnames, and other variables for the specific database. The credentials can usually be found in the application’s config/database.yml production configuration file.

Q: How do I make the script executable?
A: Run chmod +x ~/bin/database-backup.sh.

Q: How do I manually run this script?
A: Execute ~/bin/database-backup.sh from the terminal.

Note: Before running these scripts, ensure that the aws-cli tool is installed if you intend to use the AWS S3 upload option, and also confirm that the backup directories exist or are created automatically by the script.