Automatically Backup Alibaba MySQL using Grandfather-Father-Son Strategy

 

So, basically what is Grandfather-father-son or GFS?

GFS backup is a common rotation scheme for backup, in which there are three or more backup cycles, such as daily, weekly, and monthly. Typically, It consists of daily backups (son, at fixed intervals of hours in a day), a weekly full backup (father, once a week), and monthly full backup (Grandfather, once a month).

It helps to restore the database from the most possible recovery points in case of any requirement or disaster.

To achieve this, we are using the python script and running the script via Jenkins in a docker container for complete automation.

Something About Aliyun MySQL & Its features

Alibaba ApsaraDB for MySQL is an on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities, etc.

Features:

  1. Secure – It is certified by more than 10 authorities such as ISO 20000, SOC, PCI DSS, and Grade III Protection of Information Security. It provides Access control for IP address whitelisting, Protection, and encryption against DDOS & TDE to encrypt the data and, SQL audit.
  2. Highly available –  It is the first database service that guarantees 99.99% availability in its Service Level Agreement (SLA). It also provides the Primary/secondary architecture, Local disaster recovery & Remote disaster recovery.
  3. Flexible and scalable – Users can select subscription or pay-as-you-go billing to better meet your business needs. Along with this provides the Creation of read-only instances, Automatic scaling, Allocation of dedicated resources, etc.
  4. For more details, please click here.

How we are backing up & restoring the MySQL Database?

For Backup, our script will be doing below tasks using Alibaba python SDKs & OSS2 module:

  • List all the databases from RDS.
  • Create Directory Structure with respect to hourly, weekly & monthly basis.
  • Take the MySQL dump locally.
  • Push the dump to Alibaba OSS using the KMS method to encrypt the data on the fly.
  • Delete the dump from the local system.

Note: Script will create the directory structure in the below format in OSS.

1. hourly --> 06-November-2019 --> DB_NAME --> hourly.sql.gz
2. weekly --> November-19 --> Week Number --> DB_NAME --> date.sql.gz
3. monthly --> 2019 --> November --> DB_NAME --> date.sql.gz

And for decryption script performs the below tasks:

  • Take user input for the absolute path of the dump which is located in Alibaba OSS.
  • Pull the data from Alibaba OSS.
  • Decrypt the data using the KMS method on the fly.

Implementation

Before starting the implementation part make sure you meet the below Pre-requisites

  • Create Customer Managed Key(CMK) ID in Alibaba KMS service.
  • Create OSS Bucket for database backup in Alibaba.
  • Create RAM user with programmatic access for the OSS bucket & KMS.
  • Jenkins system with a docker plugin installed.
  • And RDS MySQL DB on which wants to perform backup/restore.

Step1: Create the Docker Image from Dockerfile.

Step2: Push the Docker Image to the container registry.

Step3: Save the below sensitive information in base64 format in Jenkins credentials, which we will be using as an environmental variable inside the docker container to run the python scripts.

Open the Jenkins & navigate to credentials → Systems→ Global credentials → Add Credentials and select kind as ‘Secret text’.

  • Alibaba RDS MySQL User with tag ‘Rds-User’.
  • Alibaba RDS MySQL Password with tag ‘Rds-Pass’.
  • Alibaba RDS MySQL Endpoint with tag ‘Rds-Endpoint’.
  • Alibaba User Access ID with tag ‘Aliyun-Access-Id’.
  • Alibaba User Access Key with tag ‘Aliyun-Access-Key’.
  • Alibaba OSS Endpoint with tag ‘Bucket-Endpoint’.
  • Alibaba OSS Bucket Name with tag ‘OSS-Name’.
  • Alibaba KMS ID with tag ‘kms-id’.
  • Alibaba KMS Region with tag ‘Kms-Region’.

Step4: Create the 3 Jobs as type ‘Pipeline’ for automated backups namely ‘mysqlBackupHourly’, ‘mysqlBackupWeekly’ & ‘mysqlBackupMonthly’ using the Jenkinsfiles, as shown below. Make sure you already have pushed these Jenkins files to your favorite Git repository

 

Step5: Similarly a Job can be created for the restoration.

So now you know how to configure the GFS Strategy for MySQL RDS using Jenkins Automation and I hope this will ease your problem for backing up & restoration.

Thank You & Have a nice day 🙂

Image Source

 

Opstree is an End to End DevOps solution provider

contact us

Leave a Reply