Script to take tablewise mysqldump and store in s3

Introduction

This guide covers a script to take table-wise mysqldump backups, encrypt them, and store them in S3. The original version of this script has several real bugs — including a serious one: the encryption key is derived from the current date, making it guessable rather than secure. This guide walks through a corrected version.

---

Implementation

I. Prerequisites

• s3cmd installed and configured on the backup machine
• MySQL root password
• An S3 bucket with versioning enabled
• AWS access key and secret key with write privileges to that bucket

II. How the Script Works

• Lists every MySQL database, then every table in each
• Dumps each table individually, compresses it, encrypts it
• Uploads the encrypted file to S3, deletes the local copy
• Removes local backups older than 5 days and emails a summary

III. Real Issues in the Original Script

• Passwords passed incorrectly. mysqldump -u $DB_USER $DB_PASS treats the password as an extra database name instead of authenticating with it — needs to be -p$DB_PASS.
• Encryption key derived from the current date. DATA=$(date|shasum|base64|head -c 16) is guessable, since an attacker who knows roughly when a backup ran can narrow down the key.
• Undefined $EMAIL variable used in mail -r $EMAIL, when only FROM_EMAIL/TO_EMAIL were actually defined.
• Malformed mail commands — the recipient was embedded inside an unterminated subject string instead of passed as a separate argument.
• A corrupted, merged line (>> $BASE_BAK_FLDR/../logdate_OIFS=$IFS; IFS=$'\n') that looks like two commands accidentally joined into one.
• Missing spaces in both a find command and an if [ -d ... ] test — both are shell syntax errors as written.
• No check that the S3 upload succeeded before deleting the local encrypted file.

IV. The Corrected Script

#!/bin/bash
# support@pheonixsolutions.com
# Script to take per-table mysqldump backups, encrypt them, and upload to a versioned S3 bucket.

set -uo pipefail

S3BACKUP="s3-bucket-name"
DB_USER="root"
DB_PASS="DBPassword"
BASE_BAK_FLDR="/backup/sqldailybackups"
BACKUP_LOG="$BASE_BAK_FLDR/sqlbackuplog"
KEY_LOG="$BASE_BAK_FLDR/encryption-keys.log"
FROM_EMAIL="from-email@domain.tld"
TO_EMAIL="to-email@domain.tld"

if ! command -v s3cmd >/dev/null 2>&1; then
    echo "s3cmd not found; please install s3cmd"
    exit 1
fi

mkdir -p "$BASE_BAK_FLDR"
: > "$BACKUP_LOG"

# Random encryption key instead of one derived from the date
DATA=$(openssl rand -base64 32)

if [ -z "$DATA" ]; then
    echo "Encryption key generation failed" | mail -r "$FROM_EMAIL" -s "SQL backup failed: $(date)" "$TO_EMAIL"
    exit 1
fi

echo "$(date) : $DATA" >> "$KEY_LOG"
chmod 600 "$KEY_LOG"

DBS_LIST=$(mysql -u "$DB_USER" -p"$DB_PASS" -N -e "show databases;" 2>>"$BACKUP_LOG")

OIFS=$IFS
IFS=$'\n'

for DB in $DBS_LIST; do
    DB_BKP_FLDR="$BASE_BAK_FLDR/$(date +%d-%m-%Y)/$DB"
    [ ! -d "$DB_BKP_FLDR" ] && mkdir -p "$DB_BKP_FLDR"

    TABLES=$(mysql -u "$DB_USER" -p"$DB_PASS" -N -e "show tables;" "$DB" 2>>"$BACKUP_LOG")

    for table in $TABLES; do
        DUMP_FILE="$DB_BKP_FLDR/$table.sql.gz"

        if [ "$table" = "event" ]; then
            mysqldump -u "$DB_USER" -p"$DB_PASS" --events "$DB" "$table" 2>>"$BACKUP_LOG" | gzip > "$DUMP_FILE"
        elif [ "$table" = "general_log" ] || [ "$table" = "slow_log" ]; then
            mysqldump -u "$DB_USER" -p"$DB_PASS" --skip-lock-tables "$DB" "$table" 2>>"$BACKUP_LOG" | gzip > "$DUMP_FILE"
        else
            mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB" "$table" 2>>"$BACKUP_LOG" | gzip > "$DUMP_FILE"
        fi

        openssl enc -aes-256-cbc -pbkdf2 -salt -a -in "$DUMP_FILE" -out "$DUMP_FILE.enc" -k "$DATA"

        if s3cmd put "$DUMP_FILE.enc" "s3://$S3BACKUP/$DB/$table.sql.gz.enc"; then
            rm -f "$DUMP_FILE" "$DUMP_FILE.enc"
        else
            echo "Upload FAILED for $DB/$table - local files retained" >> "$BACKUP_LOG"
        fi
    done

    echo "$DB" >> "$BACKUP_LOG"
done

IFS=$OIFS

find "$BASE_BAK_FLDR" -maxdepth 1 -mtime +5 -type d -exec rm -rf {} \;

echo -e "List of databases & mysqldump errors (if any):\n-----\n$(grep -v 'Using a password on the command line interface can be insecure' "$BACKUP_LOG")\n-----\nBackup location: s3://$S3BACKUP/\nEncryption key logged in: $KEY_LOG" \
  | mail -r "$FROM_EMAIL" -s "SQL Backup Completed @ $(date)" "$TO_EMAIL"

V. Key Fixes at a Glance

Fix	Why it matters
-p$DB_PASS instead of a bare password argument	Correctly authenticates instead of being misread as a database name
openssl rand -base64 32 instead of a date-derived key	Produces a genuinely random, non-guessable encryption key
Key logged separately with chmod 600	Keeps the key out of the general log and restricted to the owner
-pbkdf2 added to openssl enc	Stronger key-derivation function
Upload success checked before deleting local files	Prevents data loss if an S3 upload fails

VI. Schedule with Cron

crontab -e

0 2 * * * /path/to/backup-script.sh

VII. Restoring a Table

aws s3 cp s3://your-bucket-name/database_name/table_name.sql.gz.enc .
openssl enc -aes-256-cbc -d -pbkdf2 -a -in table_name.sql.gz.enc -out table_name.sql.gz -k "THE_KEY_FROM_THE_LOG"
gunzip table_name.sql.gz
mysql -u root -p database_name < table_name.sql

Retrieve the key from KEY_LOG (/backup/sqldailybackups/encryption-keys.log), matching the backup’s date.

VIII. Conclusion

The core idea — per-table backups, encrypted and versioned in S3 — is sound, but the original script had real bugs that would break it or quietly weaken its security. The corrected version fixes the password flag, email syntax, and corrupted line, and replaces a guessable encryption key with a properly random one.

---

Frequently Asked Questions

Why is a date-derived encryption key such a problem? Because an attacker who can see roughly when a backup was created only needs to test a small number of possible values to reconstruct the key.

Will I lose data if an upload fails? Not with the corrected script — it now checks the upload succeeded before deleting the local copy, and logs a failure if it didn’t.

Does this apply to MariaDB too? Yes — mysqldump and the same command syntax work identically against MariaDB.

---

Related Articles

• Fix ifconfig Not Working on CentOS 7
• Change SSH Default Port and Disable SSH Root Login

---

Talk to Our Technology Experts

Need a reliable, secure backup strategy for your databases? Our team can help with backup automation and cloud storage security.

Connect with our technology experts.