Automating MySQL Backups to Amazon S3 Using Docker on EC2

By Jeff Butler | January 7, 2025

Image for Automating MySQL Backups to Amazon S3 Using Docker on EC2

Seamlessly Secure Your Data: Automate MySQL Backups with Docker on EC2 to Amazon S3

Setting up automated backups of a MySQL database to Amazon S3 within a Docker environment on an Amazon EC2 instance provides a robust solution for ensuring data integrity and availability. This guide details the process, including common issues you might encounter and how to resolve them, ensuring your setup is secure and efficient.

Prerequisites

  • Amazon EC2 Instance: Running a supported Linux distribution with Docker installed.
  • Docker Compose: For managing multi-container Docker applications.
  • AWS Account: Access to S3 for storing backups.
  • MySQL Database: Running inside a Docker container.

Step 1: Prepare the Backup Script

Create the Script

Navigate to your project directory on the EC2 instance and set up a scripts directory:

mkdir -p scripts
nano scripts/backup.sh

Script Contents

#!/bin/bash
DB_HOST="db"
DB_USER="your_user"
DB_PASSWORD="your_password"
DB_NAME="your_database"

DATE=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILENAME="/backup/$DB_NAME-$DATE.sql"

mkdir -p /backup
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILENAME
aws s3 cp $BACKUP_FILENAME s3://your_bucket_name/path/to/backup/
rm $BACKUP_FILENAME

Make it Executable

chmod +x scripts/backup.sh

Step 2: Docker Configuration

Update Your Dockerfile

FROM php:8.1-apache
RUN apt-get update && apt-get install -y awscli default-mysql-client cron libpng-dev libjpeg-dev libfreetype6-dev && rm -rf /var/lib/apt/lists/*

COPY scripts/backup.sh /usr/local/bin/backup.sh
RUN chmod +x /usr/local/bin/backup.sh

Cron Job Setup

Schedule backups every 10 minutes (for testing):

echo "*/10 * * * * /usr/local/bin/backup.sh >> /var/log/backup.log 2&1" > scripts/backup-cron
COPY scripts/backup-cron /etc/cron.d/backup-cron
RUN chmod 0644 /etc/cron.d/backup-cron
RUN crontab /etc/cron.d/backup-cron

Modify CMD

CMD cron && apache2-foreground

Step 3: Build and Deploy

docker-compose build --no-cache
docker-compose up -d

Troubleshooting Common Issues

  • Permission Issues: Ensure the MySQL user has the necessary privileges. 
    GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO 'your_user'@'%';
FLUSH PRIVILEGES;
  • Script Execution Problems: If the script fails, check MySQL credentials and AWS CLI configuration.
  • AWS S3 Access: Verify the IAM role and policies to ensure S3 write access.
  • Cron Jobs Not Running: Confirm cron is active and logging correctly. Manually start cron if necessary and check /var/log/cron.log for outputs.

Conclusion

This setup on an EC2 instance not only ensures your MySQL database is regularly backed up to S3, leveraging Docker for environment management and AWS for scalable, reliable storage, but also incorporates robust monitoring and security practices. Adjust the cron schedule as necessary for your production environment after initial testing. Always verify backups and maintain security best practices to protect your data.

Next » All Articles