-->

Backing Up MySQL to AWS S3 with PowerShell

aws ,  devops s3, mysql, powershell

I have an application that uses MySQL database. Because of cost concerns it’s running on an EC2 instance instead of RDS. As it’s not a managed environment, the burden of backing up my data falls on me. This is a small step by step guide that details how I’m backing up my MySQL database to AWS S3 with PowerShell

PART 01 - AWS SETUP

  1. Create a bucket named (e.g. “application-backups”) on AWS S3 using AWS Management Console.
  2. Create a new IAM user (e.g “upload-backup-to-s3”)
  3. Create a new policy using management console. The policy will only give enough permissions to put objects into a single S3 bucket:
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::xxxxxxx"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:PutObject",
            "Resource": [
                "arn:aws:s3:::xxxxxxx/*"
            ]
        }
    ]
}

In S3 bucket properties copy ARN and replace the x’s above with that.

  1. Customize S3 bucket LifeCycle settings to determine how long you want the old logs retain in your bucket. In my case I set it to expire at 21 days which I think it’s large enough window for relevant database backups. I probably wouldn’t restore anything older than 21 days anyway.

  2. [Optional - For email notifications] Create an SES user by clicking SES -> SMTP Settings -> Create My SMTP Credentials

Make sure you don’t make the mistake I made which was creating an IAM user with a policy that can send emails. In the SMTP settings page there’s a note right below the button:

Your SMTP user name and password are not the same as your AWS access key ID and secret access key. Do not attempt to use your AWS credentials to authenticate yourself against the SMTP endpoint.

When you click the button it creates an IAM user basically for the secret key is 44 bytes whereas the IAM user I created had a secret key of 40 characters. Anyway, bottom line is in order to be able to send emails via SES create the user as described above and all should be fine.

PART 02 - POWERSHELL SCRIPT

  1. Download and install AWS Tools for Windows PowerShell (https://aws.amazon.com/powershell/)

  2. Create a script as shown below. In a nutshell what the script does is:

a. Execute mysqldump command (Comes with MySQL Server)

b. Zip the backup file (which reduces the size significantly)

c. Upload the zip file to S3 bucket

d. Send a notification email using SES

e. Delete the local files

This is the full script:

  • For SQL Server databases, there’s a PowerShell cmdlet called Backup-SQLDatabase but for MySQL I think the most straightforward way is using mysqldump that comes with MySQL server.

  • For password-protected zip files, you can take a look at this article (I haven’t tried it myself)

Final step: Schedule the script by using Windows Task Scheduler

This is quite straightforward. Just create a task, schedule it to how often you want to backup your database.

In the actions section, enter “powershell” as “program/script” and the path of your PowerShell script as “argument” and that’s it.

Resources