DevOps Interview: Taking backup of Mysql db and store backup in AWS S3 using Ansible Playbook

The primary objective is to automate the process of taking backups of a MySQL database. Storing backups securely is important for disaster recovery. AWS S3 (Simple Storage Service) is a reliable and scalable object storage service offered by Amazon Web Services. This playbook is designed to upload the MySQL database backup to an S3 bucket. In the event of a server failure, data corruption, or any other disaster, having a recent backup stored in a separate, reliable location will help to restore the db.

Prerequisites:

1. Ansible should be installed in your machine to run this playbook.

2. you have access credentials (username and password) for the MySQL database you want to back up.

3. an AWS user with the necessary permissions to write to the S3 bucket where you want to store the backup.

4. AWS access key ID and secret access key for the AWS user you created.

5. AWS CLI is installed and configured on your machine.

Main Playbook:

---
- name: Take MySQL backup and store in S3
  hosts: all #give group or server name based on your need
  become: true
  tasks:
    - name: Create temporary directory
      tempfile:
        path: /tmp/mysql_backup
        state: present
    - name: Take MySQL backup
      shell: mysqldump -u <username> -p<password> <database-name> > /tmp/mysql_backup/mysql_backup.sql
    - name: Compress MySQL backup
      archive:
        path: /tmp/mysql_backup
        format: gzip
        dest: /tmp/mysql_backup.gz
    - name: Upload MySQL backup to S3
      aws_s3:
        bucket: <bucket-name>
        key: mysql_backup.gz
        src: /tmp/mysql_backup.gz
        aws_access_key_id: <access-key-id>
        aws_secret_access_key: <secret-access-key>
        mode: put
    - name: Remove temporary directory
      file:
        path: /tmp/mysql_backup
        state: absent

To prevent exposing sensitive information like AWS access credentials directly in our Ansible playbook, we can utilize Ansible Vault to encrypt and securely store these secrets.

Encrypt the AWS access credentials using Ansible Vault-

ansible-vault encrypt aws_access_key_id=<your-access-key-id>
ansible-vault encrypt aws_secret_access_key=<your-secret-access-key>

This will create encrypted files named aws_access_key_id.vault and aws_secret_access_key.vault.

Update the playbook to use the encrypted files-

- name: Upload MySQL backup to S3
      aws_s3:
        bucket: <bucket-name>
        key: mysql_backup.gz
        src: /tmp/mysql_backup.gz
        aws_access_key_id: "{{ lookup('file', 'aws_access_key_id.vault') }}"
        aws_secret_access_key: "{{ lookup('file', 'aws_secret_access_key.vault') }}"
        mode: put

Now, once done with all changes, save the main playbook and run.

It will ask for the Vault password during its execution for decrypting the encrypted AWS access credentials. Provide the password.

That’s all. Pat yourself on the back. :)

Hope you enjoyed it. Don’t forget to like it.