Practical Example of Using New Logical Backup for MySQL DB backup/recovery on Kubernetes

If you pay attention on the update with release 8.0.21, MySQL introduced new MySQL Shell’s instance dump utility. The new dump utility provides consistent online backup on InnoDB tables with parallel threads for dumping data and good compression ratio, which are not provided by the legacy mysqldump. This tool is also more informative with progress information displayed during dump process. Running dump with “dry run” mode is also available to show what information on backup process, database objects to be dumped, and what MySQL database service compatibility issues to be fixed without doing the actual backup with the options provided.

 

What amazing about new dump utility is its supportability to MySQL Database Service DB System and Oracle Cloud Infrastructure Object Storage bucket as destination of the dump files and access the Object Storage bucket from the compute instance (see this URL: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html). It was designed to help migrate MySQL onto MySQL Database Service DB system.

 

The tool is compatible with MySQL version 5.7 and above. Using new MySQL Shell instance dump utility on 5.6 does not work because of unsupported SQL command during instance checking and others that only available on version 5.7 and above. See below.

Intrigued with this utility, I did backup test with 2 GB data sets using default options on MacBook Pro (MacOS Mojave 10.14.4) 4 core 2.3 GHz Intel i5 16 GB RAM 2133 MHz LPDDR3 and local disk with SSD. It took around 15 - 16 seconds to complete the backup with total compressed dump size around 49 MB. I did several iterations to catch these statistics.    

Prepare Source and Target Databases

For simplicity, I deployed two pods within a StatefulSet named as “node”. I used the “node-0” as source database and the “node-1” as target restoration. Herewith the definition in my YAML file:

---

apiVersion: apps/v1

kind: StatefulSet

metadata:

  name: node

  namespace: mysql-cluster

spec:

  replicas: 2

  serviceName: node

  selector:

    matchLabels:

      app: node

  template:

    metadata:

      labels:

        app: node

    spec:

     containers:

     - image: mysql/enterprise-server:8.0

       name: mysql

       env:

         - name: MYSQL_ROOT_PASSWORD

           valueFrom:

             secretKeyRef:

                name: mysql-root-password

                key: password

       volumeMounts:

         - name: data-vol

           mountPath: /var/lib/mysql

  volumeClaimTemplates:                               

  - metadata:

      name: data-vol                                  

    spec:

      accessModes: [ "ReadWriteOnce" ]                

      resources:

        requests:

          storage: 1Gi

---

apiVersion: v1

kind: Service

metadata:

  name: node-0

  namespace: mysql-cluster

spec:

  ports:

  - port: 3306

  selector:

    statefulset.kubernetes.io/pod-name: node-0

  clusterIP: None

---

apiVersion: v1

kind: Service

metadata:

  name: node-1

  namespace: mysql-cluster

spec:

  ports:

  - port: 3306

  selector:

    statefulset.kubernetes.io/pod-name: node-1

  clusterIP: None

Install sample database on Source

I run the following command to deploy sample database using Sakila onto “node-0” and created a user for connecting from backup-server.

kubectl -n mysql-cluster exec -it node-0 -- yum install -y tar

kubectl -n mysql-cluster cp sakila-schema.sql node-0:/tmp/sakila-schema.sql

kubectl -n mysql-cluster cp sakila-data.sql node-0:/tmp/sakila-data.sql

kubectl -n mysql-cluster exec -it node-0 -- mysql -uroot -proot -e "source /tmp/sakila-schema.sql"

kubectl -n mysql-cluster exec -it node-0 -- mysql -uroot -proot -e "source /tmp/sakila-data.sql"

kubectl -n mysql-cluster exec -it node-0 -- mysql -uroot -proot -e "create user root@'%' identified by 'root'; grant backup_admin, reload, select, event, show view on *.* to root@'%' with grant option;"

Deploy Backup Server Pod

Backup server pod is special, as this pod mounts PV/PVC into another directory than “/var/lib/mysql”. In this example, I was using a folder name “/backup” to store my dump files onto persistent disks through PV/PVC. Thus, we run MySQL database instance on this pod as ephemeral because “/var/lib/mysql” does not persist data on disks. I applied the following YAML file to deploy backup server.

---

apiVersion: apps/v1

kind: StatefulSet

metadata:

  name: backup-server

  namespace: mysql-cluster

spec:

  replicas: 1

  serviceName: backup-server

  selector:

    matchLabels:

      app: backup-server

  template:

    metadata:

      labels:

        app: backup-server

    spec:

     containers:

     - image: mysql/enterprise-server:8.0

       name: mysql

       env:

         - name: MYSQL_ROOT_PASSWORD

           valueFrom:

             secretKeyRef:

                name: mysql-root-password

                key: password

       volumeMounts:

         - name: data-vol

           mountPath: /backup

  volumeClaimTemplates:                               

  - metadata:

      name: data-vol                                  

    spec:

      accessModes: [ "ReadWriteOnce" ]                

      resources:

        requests:

          storage: 1Gi

Backup “node-0” database

To backup database, I run Instance Dump utility using MySQL Shell on the backup server connecting to source database. Herewith the initial status of “/backup” before I run this backup, it’s empty!

So, I run the following command to backup database instance on the “node-0”

kubectl -n mysql-cluster exec -it backup-server-0 -- mysqlsh root:root@node-0 -e "util.dumpInstance('/backup')"

At this point, I could see “/backup” on backup server was populated with the dump files.

Restore Dump Files into “node-1”

I run the following command to show database schema on “node-1” prior restoration.

Prior restoration, the target database has to be prepared by setting system variable “LOCAL_INFILE” to “ON” and database user for target server to connect has to be created.

kubectl -n mysql-cluster exec -it node-1 -- mysql -uroot -proot -e "set persist local_infile=on"

kubectl -n mysql-cluster exec -it node-1 -- mysql -uroot -proot -e "create user root@'%' identified by 'root'; grant all privileges on *.* to root@'%' with grant option;"

 Then, I used the following command to restore dump files onto “node-1” from the backup server.

kubectl -n mysql-cluster exec -it backup-server-0 -- mysqlsh root:root@node-1 -e "util.loadDump('/backup')"

Running “show databases” on “node-1” after restoration came out with the following result:

The above output shows sakila schema is already restored onto “node-1” from the backup dump.

Key Points

MySQL Shell’s Instance Dump provides nice tool to perform backup and recovery on MySQL database running on Kubernetes for small to medium data size with less stringent on backup recovery SLA. For huge data sets or stringent SLA in terms of backup and recovery (i.e. production system), it is strongly suggested to use MySQL Enterprise Backup (MEB) that offers block-level backup with many more features, such as incremental backup, easy to perform PITR, integration with backup software, etc. with far less time required to perform backup and recovery. My test on the same data sets and environment showed the MEB superiority over MySQL Shell’s instance dump. It was able to complete the backup in average 8.28 sec. Running MEB on Kubernetes is possible too. I’ll cover how to use MEB on container on my future blog post.