Backup & Restore MySQL Database on Kubernetes Using Trilio

By Trilio Content Team | July 30, 2021

This blog is the first in a series of How-To blogs. This first blog in the series is by solutions engineer Bhagirath Hapse. Read on for hands-on instruction from a Trilio expert. 

In this blog post, you will find out how to backup and restore MySQL relational databases to S3 compatible or NFS targets.

MySQL Database

MySQL is an open source relational database (DB) management system developed by Oracle. A relational database operates by organizing data into tables that can be connected based on the data they have in common. These relations are used to structure the data. MySQL is used by many database-driven web applications, including Drupal, Joomla, phpBB and WordPress. MySQL is also used by many popular websites, such as Facebook, Flickr, MediaWiki, Twitter and YouTube.

Why Backup?

Today’s modern digital businesses face the challenge of working with new and emerging technologies, such as containers, while also fulfilling key business requirements for infrastructure resiliency and uptime. In order to maintain business continuity, developers, SREs and IT Operations personnel need to have a strategy for backup and disaster recovery (DR,) as outlined below:

Data Recovery: When you need to ensure the resiliency of applications and infrastructure running on Kubernetes, the backup plan is key. Implementing a well orchestrated backup plan will help to protect the cluster, and thereby applications, from failure or service outage.

Application Portability and Migration: The technology industry has chosen Kubernetes as the go-to software development platform for cloud environments and, because of this, application portability and migration have taken on new levels of importance.

Data Consistency: Applications deployed on Kubernetes are microservices-based and dynamic in nature. Metadata is very important. Applications can be deployed using labels, Helm charts or Operators. Data consistency is a key aspect of application backup. There are multiple objects and resources that are tied to a specific namespace or common across all namespaces.

Considering all of this, you’ll need an enterprise solution that provides an application-centric approach to cloud-native data protection to satisfy all of these requirements.

Trilio for Kubernetes is a trusted Kubernetes-native data protection platform designed specifically to protect cloud-native applications from core to edge to cloud deployments. It has the ability to protect your application running on Kubernetes engines from different vendors, including public cloud providers and on-premise deployments.  

Now, let’s take a look at how Trilio for Kubernetes performs application consistent backup and restore for MySQL DBs.

Step-by-Step Procedure for Backup and Restore of MySQL DB

  • Prerequisites

Install Trilio for Kubernetes and configure following these steps:

    • An Amazon S3 target example is provided below:

apiVersion: triliovault.trilio.io/v1
kind: Target
metadata:
  name: demo-s3-target
spec:
  type: ObjectStore
  vendor: AWS
  objectStoreCredentials:
    url: "https://s3.amazonaws.com"
    accessKey: "AaBbCcDdEeFf"
    secretKey: "BogusKeyEntry"
    bucketName: "S3_Bucket_US_East"
    region: "us-east-1"
  thresholdCapacity: 100Gi

$ kubectl create -f tvk-backup-target.yaml

  • Create MySQL DB deployment using a Helm chart

Use the MySQL helm chart to deploy the application on the default namespace.

$helm repo add stable https://charts.helm.sh/stable
$ helm repo update
$ helm install mysql-qa --set mysqlRootPassword=triliopass stable/mysql

  • Insert test data

Port-forward the mysql-qa service to listen on the 3306 port.

Run command:

$ kubectl port-forward --address 0.0.0.0 svc/mysql-qa 3306:3306 &>/dev/null


 Run a python script to insert some test data into the mysql database. Create a python file mysql_helm_insert_data.py.

Install mysql client python library if not present
# For python2 env
# pip install mysql-connector
# For python3 env
# pip3 install mysql-connector

 

#!/usr/bin/python
import mysql.connector

mysql_host= "127.0.0.1"
mysql_user="root"
mysql_password="triliopass"

mydb = mysql.connector.connect(
  host=mysql_host,
  user=mysql_user,
  passwd=mysql_password
)

mycursor = mydb.cursor()

## Create Database: 'trilio_qa'
mycursor.execute("CREATE DATABASE IF NOT EXISTS trilio_qa")

# Close the database connection
if(mydb.is_connected()):
   mycursor.close()
   mydb.close()
   print("connection is closed")

## Create new connection object with database - 'trilio_qa'

mydb = mysql.connector.connect(
  host=mysql_host,
  user=mysql_user,
  passwd=mysql_password,
  database="trilio_qa"
)

mycursor = mydb.cursor()

## Create table: 'users'
mycursor.execute("CREATE TABLE IF NOT EXISTS users (firstname VARCHAR(255), lastname VARCHAR(255))")

sql = "INSERT INTO users (firstname, lastname) VALUES (%s, %s)"
val = [
  ( 'Peter', 'Smith' ) ,
  ( 'Amy', 'Johnson' ) ,
  ('Hannah', 'Williams' ) ,
  ( 'Michael', 'Brown' ) ,
  ( 'Sandy', 'Jones' ) ,
]
mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "records inserted.")

# Close the database connection
if(mydb.is_connected()):
   mycursor.close()
   mydb.close()

  • Create Hooks

MySQL DB does not have its own built-in backup and restore capabilities. In order to maintain data consistency before backing up MySQL DB, you need to understand how the data is stored. For MySQL, DB backup data is stored in /var/lib/mysql. This data directory stores the database schema, tables, configurations, policies, logs and metadata. For backup, the scope can range from individual databases to tables, policies, metadata, etc. A backup creates a copy of the table and metastore data at that point-in-time and stores the copy in the specified target directory.

Use the FLUSH command on MySQL DB and UNLOCK TABLES to make sure you have a consistent state of data before the backup is triggered. A full backup creates a copy of the metastore and shard data. An incremental backup creates a copy of the only metastore and shard data that has changed since the last incremental backup. If there are no existing incremental backups, the system automatically performs a complete backup. Use the MySQL DB backup inside the Trilio for Kubernetes backup hook.

apiVersion: triliovault.trilio.io/v1
kind: Hook
metadata:
  name: mysql-hook
spec:
  pre:
    execAction:
      command:
        - "bash"
        - "-c"
        - "mysql --user=root --password=$MYSQL_ROOT_PASSWORD -Bse 'FLUSH TABLES WITH READ LOCK;system ${WAIT_CMD};'"
    ignoreFailure: false
    maxRetryCount: 1
    timeoutSeconds: 10
  post:
    execAction:
      command:
        - "bash"
        - "-c"
        - "mysql --user=root --password=$MYSQL_ROOT_PASSWORD -Bse 'FLUSH LOGS; UNLOCK TABLES;'"
    ignoreFailure: false
    maxRetryCount: 1
    timeoutSeconds: 10

 

$kubectl create -f tvk-mysql-hook.yaml

  • Create BackupPlan

In the backup plan, specify the resources you want to protect. It could be a complete namespace, label based application, Helm chart, or an Operator.

Create a backup plan for mysql-qa applications deployed in the default namespace using labels.

apiVersion: triliovault.trilio.io/v1
kind: BackupPlan
metadata:
 name: mysql-backupplan
spec:
 backupNamespace: default
 backupConfig:
   target:
     name: demo-s3-target
    namespace: default
 hookConfig:
   mode: Sequential
   hooks:
     - hook:
         name: mysql-hook
        namespace: default
       podSelector:
         labels:
           - matchLabels:
               app: mysql-qa
         regex: mysql-qa*
       containerRegex: mysql-qa*

 

$kubectl create -f tvk-mysql-backupplan.yaml

  • Create Backup

As the name suggests, the yaml example below performs the backup operation by referencing the created backup plan shown above.

Schedule a backup using scheduleType: Periodic.

apiVersion: triliovault.trilio.io/v1
kind: Backup
metadata:
  name: mysql-hook-helm-full-backup
spec:
  type: Full
  scheduleType: Periodic
  backupPlan:
    name: mysql-backupplan
    namespace: default

 

$kubectl create -f tvk-mysql-backup.yaml

  • Restore to a different namespace, for example “restore-ns.” This needs to be created if it does not already exist.

Perform a restore operation into a different namespace, or any namespace on a different cluster, as you would in an application migration scenario.

The yaml definition below will restore the mysql-qa application to a different namespace on the same cluster using the backup created earlier.

apiVersion: triliovault.trilio.io/v1
kind: Restore
metadata:
  name: mysql-helm-restore
spec:
  backupPlan:
    name: mysql-backupplan
    namespace: default
  source:
    type: Backup
    backup:
      name: mysql-hook-helm-full-backup
      namespace: default
    target:
      name: demo-s3-target
            namespace: default
  restoreNamespace: restorens
  skipIfAlreadyExists: true

If the restore is triggered from a default namespace, then you need to provide the target/restore namespace when executing the restore yaml definition.

$kubectl create -f tvk-mysql-restore.yaml -n restore-ns

Conclusion

When you use this procedure, backing up MySQL with TrilioVault for Kubernetes is easy. The backups are application and data consistent and use the ‘hooks’ feature, which allows you to perform any pre/post backup actions.

Furthermore, TrilioVault for Kubernetes provides a wide range of helpful features, including:

  • Storing metadata and all application resources on a specified target
  • Supporting Helm/label/Operators, S3 or NFS- based backup targets 
  • Providing application hooks to ensure data consistent backups

With all of these features, Trilio for Kubernetes provides a strong platform for enterprise-grade Database backups for MySQL (as well as many other databases).

Bhagirath Hapse, Solutions Engineer for Trilio

Posted in Blog and tagged , , , ,