Data Backup from relational databases in the cloud.

Many Azure Infrastructure deployments that involve a MySQL or MSSQL server instance will tend to rely on the features available from the cloud resource for backup and recovery. While these resources support continuous replication with another instance and scheduled backups on the same instance, there is no feature available to backup individual databases. Application-engineering teams that rely on the data in a single database must rely on customized automation for this purpose. Infrastructure engineering teams that deploy these database servers for the benefit of these application engineering teams have a wide range of options to write such an automation. Most of them rely on leveraging the command line utilities from the database publishers with the two most popular on Azure being MySQL or MSSQL. A command line utility like mysqldump can provide automation to backup and restore individual databases and most automation relies on archiving those backup files on storage accounts or s3 storage that are designed to be durable. Depending on where the application engineering teams host these applications, the customizations could leverage the same hosts for creating backups and restores. For example, an infrastructure using Azure Kubernetes Service to host the applications If the application engineering teams prefer GitOps over Azure DevOps to transmit the secrets to the command-line utility, one option is to host the automation as GitHub Actions. This article demonstrates how to do that with two files: one for a GitHub Action workflow that deploys an AKS job to backup or restore a single database and another describing the AKS job. The automation can also parameterize the hosts, database source and destination, and storage accounts so that the process can be repeated for various consumers.

name: "MySql Backup Restore"

on:

  push:

    branches:

      - main

    paths:

      - 'mysqlconfig/U2-Prepay-Non-Prod/**'

defaults:

  run:

    shell: bash

 

#Enforing to use OIDC for authentication

permissions:

  actions: read

  checks: read

  contents: read

  deployments: read

  id-token: write

  issues: read

  discussions: read

  packages: read

  pages: read

  pull-requests: write

  repository-projects: read

  security-events: read

  statuses: read

 

jobs:

  pre-deploy:

    name: Prepay NonProd

    runs-on: [ uhg-runner ]

    environment: prod

    strategy:

      matrix:

        subscriptions: [${{ input.subscription }}]

    env:

      ARM_CLIENT_ID: ${{ secrets.AZURE_CLIENT_ID }}

      ARM_USE_OIDC: true

      ARM_SUBSCRIPTION_ID: ${{ input.subscription }}

      ARM_TENANT_ID: ${{ secrets.AZURE_TENANT_ID }}

      GH_PAT: ${{ secrets.GH_PAT }}

    steps:

      #Login to Azure

      - name: 'Az CLI login'

        uses: azure/login@v1

        with:

          client-id: ${{ secrets.AZURE_CLIENT_ID }}

          tenant-id: ${{ secrets.AZURE_TENANT_ID }}

          subscription-id: ${{ input.subscription }}

         

      - name: 'Action Checkout'

        uses: actions/checkout@v3

        with:

          fetch-depth: 0   

      - name: 'Git Config URL'

        run: git config --global url."https://${{ secrets.GH_PAT }}@github.com".insteadOf https://github.com

      - name: 'Setup working Directory'

        run: |

             WORK_DIR="./"

             echo "WORK_DIR=${WORK_DIR}" >> $GITHUB_ENV

             echo "Current working directory is $WORK_DIR"   

         

      - name: Get git changes

        id: changes

        run: |

          jsonfile=$(git diff --name-only --diff-filter=A ${{ github.event.before }} ${{ github.event.after }} | grep .json$ | head -1 | xargs)

          echo "$jsonfile"

 

          githubjson=$(cat ${jsonfile})

 

          echo $githubjson

 

          DB_HOST=`echo $(jq -r '.DB_HOST' <<< "$githubjson")`

          echo "DB_HOST=$DB_HOST" >> $GITHUB_ENV

 

          DB_USER=`echo $(jq -r '.DB_USER' <<< "$githubjson")`

          echo "DB_USER=$DB_USER" >> $GITHUB_ENV

 

          DB_NAME=`echo $(jq -r '.DB_NAME' <<< "$githubjson")`

          echo "DB_NAME=$DB_NAME" >> $GITHUB_ENV

 

          KV_NAME=`echo $(jq -r '.KV_NAME' <<< "$githubjson")`

          echo "KV_NAME=$KV_NAME" >> $GITHUB_ENV

 

          KV_SECRET_NAME=`echo $(jq -r '.KV_SECRET_NAME' <<< "$githubjson")`

          echo "KV_SECRET_NAME=$KV_SECRET_NAME" >> $GITHUB_ENV

 

          KV_BLOB_CONN_STR=`echo $(jq -r '.KV_BLOB_CONN_STR' <<< "$githubjson")`

          echo "KV_BLOB_CONN_STR=$KV_BLOB_CONN_STR" >> $GITHUB_ENV

 

          FILE_NAME=`echo $(jq -r '.FILE_NAME' <<< "$githubjson")`

          echo "FILE_NAME=$FILE_NAME" >> $GITHUB_ENV

 

          BACKUP_RESTORE=`echo $(jq -r '.BACKUP_RESTORE' <<< "$githubjson")`

          echo "BACKUP_RESTORE=$BACKUP_RESTORE" >> $GITHUB_ENV

 

          AKS_RG=`echo $(jq -r '.AKS_RG' <<< "$githubjson")`

          echo "AKS_RG=$AKS_RG" >> $GITHUB_ENV

 

          AKS_NAME=`echo $(jq -r '.AKS_NAME' <<< "$githubjson")`

          echo "AKS_NAME=$AKS_NAME" >> $GITHUB_ENV

 

          AKS_NAMESPACE=`echo $(jq -r '.AKS_NAMESPACE' <<< "$githubjson")`

          echo "AKS_NAMESPACE=$AKS_NAMESPACE" >> $GITHUB_ENV

         

          BLOB_CONTAINER_NAME=`echo $(jq -r '.BLOB_CONTAINER_NAME' <<< "$githubjson")`

          echo "BLOB_CONTAINER_NAME=$BLOB_CONTAINER_NAME" >> $GITHUB_ENV

 

          EMAIL_RECIPIENTS=`echo $(jq -r '.EMAIL_RECIPIENTS' <<< "$githubjson")`

          echo "EMAIL_RECIPIENTS=$EMAIL_RECIPIENTS" >> $GITHUB_ENV

 

          EMAIL_API=`echo $(jq -r '.EMAIL_API' <<< "$githubjson")`

          echo "EMAIL_API=$EMAIL_API" >> $GITHUB_ENV

 

      - name: Setup kubectl

        id: install-kubectl

        uses: Azure/setup-kubectl@v3

 

      - name: Setup kubelogin

        id: install-kubelogin

        uses: azure/use-kubelogin@v1

        with:

          kubelogin-version: 'v0.0.32'

 

      - name: Deploy Job to AKS

        id: aksdeploy

        run: |

          az account set --subscription=$ARM_SUBSCRIPTION_ID

         

          DB_PASSWORD=$(az keyvault secret show --name $KV_SECRET_NAME --vault-name $KV_NAME --query "value")

          DB_PASSWORD=$(echo "$DB_PASSWORD" | sed -e 's/[\/&]/\\&/g')

          echo "DB_PASSWORD=$DB_PASSWORD" >> $GITHUB_ENV

 

          BLOB_CONNECTION=$(az keyvault secret show --name $KV_BLOB_CONN_STR --vault-name $KV_NAME --query "value")

          echo "BLOB_CONNECTION=$BLOB_CONNECTION" >> $GITHUB_ENV

 

          az aks get-credentials --resource-group $AKS_RG --name $AKS_NAME --overwrite-existing --admin

         

          if kubectl get job mysql-backup-job  -n $AKS_NAMESPACE > /dev/null 2>&1; then

             echo 'deleting existing job......'

             kubectl delete jobs mysql-backup-job  -n $AKS_NAMESPACE

          else

             echo 'no job to delete, moving on....'

          fi

 

          kubelogin convert-kubeconfig -l azurecli

          sed -i -e "s#%%HOST%%#$DB_HOST#" ./aks-job.yaml;

          sed -i -e "s#%%USER%%#$DB_USER#" ./aks-job.yaml;

          sed -i -e "s#%%PASS%%#$DB_PASSWORD#" ./aks-job.yaml;

          sed -i -e "s#%%DB%%#$DB_NAME#" ./aks-job.yaml;

          sed -i -e "s#%%RFILE%%#$FILE_NAME#" ./aks-job.yaml;

          sed -i -e "s#%%BACRST%%#$BACKUP_RESTORE#" ./aks-job.yaml;

          sed -i -e "s#%%CONNSTR%%#$BLOB_CONNECTION#" ./aks-job.yaml;

          sed -i -e "s#%%CNTNAME%%#$BLOB_CONTAINER_NAME#" ./aks-job.yaml;

          sed -i -e "s#%%EMAIL%%#$EMAIL_RECIPIENTS#" ./aks-job.yaml;

          sed -i -e "s#%%EMAILAPI%%#$EMAIL_API#" ./aks-job.yaml;

          sed -i -e "s#%%AKSNS%%#$AKS_NAMESPACE#" ./aks-job.yaml;

          kubectl apply -f ./aks-job.yaml

 

aks-job.yaml:

apiVersion: batch/v1

kind: Job

metadata:

  name: mysql-backup-job

  namespace: %%AKSNS%%

spec:

  template:

    metadata:

      labels: {}

    spec:

      containers:

      - env:

        - name: DB_HOST

          value: %%HOST%%

        - name: DB_USER

          value: %%USER%%

        - name: DB_PASSWORD

          value: %%PASS%%

        - name: DB_NAME

          value: %%DB%%

        - name: BLOB_MNT

          value: .

        - name: RESTORE_FILE

          value: %%RFILE%%

        - name: BACKUP_RESTORE

          value: %%BACRST%%

        - name: BLOB_CONNECTION_STRING

          value: %%CONNSTR%%

        - name: BLOB_CONTAINER_NAME

          value: %%CNTNAME%%

        - name: EMAIL_RECIPIENTS

          value: %%EMAIL%%

        - name: EMAIL_API

          value: %%EMAILAPI%%

        image: mycontainerregistry.azurecr.io/mysql-backup-restore

        imagePullPolicy: Always

        name: mysql-backup-job

        resources: {}

      restartPolicy: Never

 

Previous articles: IaCResolutionsPart175.docx