Automate MySQL Table-Level Backups to Amazon S3 with Encryption

Posted on: September 6, 2016 August 7, 2026
Categories: AWS, Database, Script, Shell script

Introduction

Backing up MySQL databases is one of the most critical responsibilities for database administrators and DevOps engineers. While full database backups are useful, there are many scenarios where backing up individual tables provides greater flexibility, faster recovery, and reduced storage consumption.

In this guide, you’ll learn how to automate table-level MySQL backups, compress and encrypt each backup using AES-256 encryption, upload them to Amazon S3, and automate the entire process using a Bash script.

By the end of this tutorial, you’ll have a reliable backup workflow suitable for production environments.

Why Take Table-Level MySQL Backups?

Traditional database dumps create a single SQL file for an entire database. While this works well for small databases, restoring a single table from a large dump can be time-consuming.

Backing up tables individually offers several advantages:

Faster restoration of individual tables
Easier troubleshooting
Reduced recovery time
Better organization of backups
Independent versioning of tables
Compatible with Amazon S3 Versioning

Solution Overview

The backup workflow performs the following actions:

Connects to the MySQL server.
Retrieves all available databases.
Retrieves every table within each database.
Creates a compressed dump for each table.
Encrypts each dump using AES-256.
Uploads the encrypted backup to Amazon S3.
Removes temporary local backup files.
Sends a backup summary (optional).
Cleans up older local backups.

Prerequisites

Before running the script, ensure the following components are installed and configured.

1. MySQL Client Utilities

Install MySQL client tools including mysqldump.

Verify installation:

$ mysqldump --version

2. AWS CLI or S3 Client

Although the original script uses s3cmd, AWS CLI v2 is recommended for new deployments.

Verify installation:

$ aws --version

$ s3cmd --version

3. Amazon S3 Bucket

Create an S3 bucket for backups.

Recommended settings:

Versioning Enabled
Server-side encryption enabled
Lifecycle policies configured
Private bucket
Block Public Access enabled

4. IAM Permissions

Grant the backup server permissions to:

Upload objects
List bucket contents
Read objects (for restoration)
Delete old backups (optional)

Avoid using the root AWS account for backup automation.

5. MySQL Credentials

Create a MySQL user with backup privileges.

Example:

$ GRANT SELECT, SHOW VIEW, EVENT, TRIGGER ON . TO 'backupuser'@'localhost';
$ FLUSH PRIVILEGES;

Backup Script Features

The script automatically:

Discovers all databases
Creates table-wise dumps
Compresses backups using gzip
Encrypts using AES-256
Uploads encrypted backups to Amazon S3
Deletes temporary backup files
Generates backup logs
Sends email notifications (optional)

Backup Script

Below is the complete Bash script.

#!/bin/sh
# support@pheonixsolutions.com
# script to take tablewise backup and upload it to a versioning enabled s3 bucket.

s3cmd --version &> /dev/null;
if [ $? -ne 0 ]; then
    echo -e "s3cmd not found; Please install s3cmd"
    exit 0;
fi

S3BACKUP="s3-bucket-name";
DB_USER=root
DB_PASS="DBPassword"
BASE_BAK_FLDR=/backup/sqldailybackups
DATA=`date|shasum|base64|head -c 16`
BACKUP_LOG=$BASE_BAK_FLDR/sqlbackuplog
FROM_EMAIL='from-email@domain.tld'
TO_EMAIL='to-email@domain.tld'

if [ -d /backup/sqldailybackups/]; then
    echo -e "Backup folder : /backup/sqldailybackups/";
else
    mkdir -p /backup/sqldailybackups/
fi

> $BACKUP_LOG

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

if [ -z "$DATA" ]; then
    echo -e "Base64 Failed: Hash Empty"| mail -r $EMAIL -s "SQL backup failed : `date` : $TO_EMAIL;
    exit 0;
fi

echo -e "`date` : $DATA" >> $BASE_BAK_FLDR/../logdate_

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"

    for table in $(echo "show tables;"|mysql "$DB" -u $DB_USER $DB_PASS -N 2>> $BACKUP_LOG); do

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

        openssl enc -aes-256-cbc -salt -a -in "$DB_BKP_FLDR"/"$table".sql.gz -out "$DB_BKP_FLDR"/"$table".sql.gz.enc -k $DATA
        s3cmd put "$DB_BKP_FLDR"/"$table".sql.gz.enc s3://$S3BACKUP/"$DB"/"$table".sql.gz.enc
        rm -f "$DB_BKP_FLDR"/"$table".sql.gz "$DB_BKP_FLDR"/"$table".sql.gz.enc

    done

    echo "$DB" >> $BACKUP_LOG

done

IFS=$OIFS

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

CUR_VER=`s3cmd info s3://$S3BACKUP/mysql/user.sql.gz.enc | grep "Last mod" | awk -F", " '{print $2}'`

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/\n\nCurrent Version on s3: $CUR_VER\n\nPrevious Version on s3: $OLD_VER\n\nData: $DATA" | mail -r $EMAIL -s "SQL Backup Completed @ `date` : $TO_EMAIL

Folder Structure

The generated backup structure looks like this:

backup/
└── sqldailybackups/
└── 07-08-2026/
├── database1/
│ ├── users.sql.gz.enc
│ ├── orders.sql.gz.enc
│ └── products.sql.gz.enc
│
└── database2/
├── customer.sql.gz.enc
└── invoice.sql.gz.enc

Encryption Process

Every SQL dump is encrypted using OpenSSL.

Example command:

openssl enc -aes-256-cbc \
-salt \
-a \
-in users.sql.gz \
-out users.sql.gz.enc \
-k

This ensures that even if the backup file is accessed without authorization, the data remains protected.

Store encryption keys securely. Do not commit them to version control or hardcode them in scripts.

Uploading to Amazon S3

Each encrypted backup is uploaded to Amazon S3.

Example structure:

s3://example-backup-bucket/

database1/
users.sql.gz.enc
orders.sql.gz.enc

database2/
customer.sql.gz.enc

If S3 Versioning is enabled, previous backup versions are retained automatically.

Automating Backups with Cron

Run the backup every night at 2:00 AM.

$ 0 2 * * * /opt/scripts/mysql-table-backup.sh

Always redirect output to a log file for troubleshooting.

Restoring a Backup

Step 1 — Download the Backup

Using AWS CLI:

$ aws s3 cp s3://example-backup-bucket/database1/users.sql.gz.enc .

Step 2 — Decrypt the Backup

openssl enc -aes-256-cbc \
-d \
-a \
-in users.sql.gz.enc \
-out users.sql.gz \
-k

The encryption key should be retrieved from your secure password manager or backup records.

Step 3 — Extract the SQL File

$ gunzip users.sql.gz

Step 4 — Restore the Table

$ mysql database1 < users.sql

The table is now restored.

Best Practices

For production environments, follow these recommendations:

Enable S3 Versioning.
Use IAM Roles instead of access keys whenever possible.
Enable Server-Side Encryption (SSE-S3 or SSE-KMS).
Encrypt backups before uploading.
Rotate encryption keys regularly.
Store secrets securely using AWS Secrets Manager or Vault.
Schedule automatic backups using cron.
Test restoration procedures periodically.
Monitor backup success and failures.
Configure S3 Lifecycle Policies to archive or delete old backups.

Conclusion

Automating MySQL table-level backups with encryption and Amazon S3 provides a secure and efficient backup strategy for modern environments. By combining mysqldump, compression, AES-256 encryption, and cloud storage, you can build a backup solution that supports granular restores, protects sensitive data, and scales with your infrastructure. To maximize reliability, automate the process with cron, regularly test backup restoration, and follow cloud security best practices such as IAM roles, S3 Versioning, and lifecycle policies.

Post Views: 245

admin

766