MySQL S3 Backup

This is an Ansible Role that installs the Amazon AWS Command Line Interface (CLI) and sets up a bash script to upload MySQL database backups to Amazon S3.

Requirements

You need to have MySQL already installed on your system.

If you are using GPG encryption, make sure you have created the necessary credentials.

It's a good idea to create a user with limited permissions. Use the policy below, named AmazonS3CreateReadWriteAccess-[bucket-name], to give basic permissions (create/list/put) for the bucket [bucket-name].

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": ["s3:CreateBucket", "s3:ListBucket"],
            "Resource": ["arn:aws:s3:::[bucket-name]"]
        },
        {
            "Effect": "Allow",
            "Action": ["s3:PutObject"],
            "Resource": ["arn:aws:s3:::[bucket-name]/*"]
        }
    ]
}

You might also want to think about enabling versioning and setting lifecycle rules for your S3 bucket.

Role Variables

Here are the available variables along with their default values (check defaults/main.yml):

• mysql_backup_name: "mysql-s3-backup"
  Name used to identify this role and for default directory and file names.

• mysql_backup_dir: "/opt/{{ mysql_backup_name }}"
  Directory where the backup script and configuration will be stored.

• mysql_backup_cronfile: "{{ mysql_backup_name }}"

• mysql_backup_cron_enabled: true

• mysql_backup_cron_hour: 23

• mysql_backup_cron_minute: 0

• mysql_backup_cron_email: false
  Cron is set to run daily at 11 PM without sending an email. To receive an email, set this to the recipient's email address.

• mysql_backup_aws_profile: "{{ mysql_backup_name }}"
  Creates a new AWS profile for this script, or you can use "default" to skip profiles.

• mysql_backup_aws_access_key: "[access-key]"
  Your Amazon AWS access key.

• mysql_backup_aws_secret_key: "[secret-key]"
  Your Amazon AWS secret key.

• mysql_backup_aws_region: eu-west-1
  The region where your S3 bucket is located.

• mysql_backup_aws_format: text
  Output format for the AWS CLI.

• mysql_backup_gpg_secret_key: False
  GPG secret key used for backup encryption.

• mysql_backup_gpg_secret_dest: "~/{{ mysql_backup_name }}-gpg.asc"
  Location to store the GPG secret key.

• mysql_backup_system_user: root
  The user who will own and execute the backup script and cron job.

• mysql_backup_config: []
  Custom settings for the backup script (uses bash syntax).

Example settings within mysql_backup_config:

• Set a timestamp format, backup directory, and whether to remove local backups after completion.

More configurable options include AWS bucket name, backup directory, whether to enable upload to S3, and MySQL credentials.

Dependencies

• memiah.aws-cli

Example Playbook

- hosts: mysql-servers
  vars_files:
    - vars/main.yml
  roles:
    - memiah.mysql-s3-backup

In vars/main.yml:

mysql_backup_aws_access_key: "access_key_here"
mysql_backup_aws_secret_key: "secret_key_here"
mysql_backup_aws_region: eu-west-1
mysql_backup_config:
  aws_profile: "{{ mysql_backup_aws_profile }}"
  aws_bucket: "bucket_name_here"
  backup_dir: "{{ mysql_backup_dir }}/backups/${timestamp}"

License

MIT / BSD

Author Information

This role was created in 2016 by Memiah Limited.