How to Backup Your Moodle MySQL Database

Data is an essential part of running a Moodle environment. Should the IT operations team lose data accidentally, then it could lead to a huge loss. So, to protect valuable information, sysadmins need to back up their Moodle database.

Backups are one of the most important things to take care of while managing a database. In this blog we are going to look into how to backup your Moodle database using ClusterControl.

ClusterControl provides you with centralized backup management for the Moodle databases, and it provides you with hot backups of large datasets, point in time recovery, in-transit data encryption, data integrity via automatic restore verification, cloud backups (AWS, Google and Azure) for Disaster Recovery, retention policies to ensure compliance, and automated alerts and reporting.

Building a Database Backup Strategy 

ClusterControl will provide two options,

1. Instant Backups 
2. Scheduled Backups

Instant Backups 

With ClusterControl, taking an instant backup is pretty straightforward. Go to Backup -> Create Backup and you will be presented with the following dialog,

By default it will take a mysqldump, the options are very straightforward you can choose based on your need. Here we are going to use Xtrabackup, which is a physical backup method that is probably your preferred option if you have a large database.

Partial backup is available if you would only like to backup specific database schemas or tables.

In the next step, you can compress and encrypt your backup, and specify the retention period. Here, you also have the “Verify Backup” feature.

Once the required options are enabled, click the create backup button.

You can monitor the progress of a backup by looking at Activity -> Jobs.

ClusterControl lists a report of all backups that have been taken, successful as well as failed ones.

Schedule Backup 

Scheduling a backup in advance is what you should do by default, it offers easy accessibility to other features like encryption, automatic test/verification of backup data, and cloud archiving.

Go straight to Backup -> Create Backup -> Schedule Backup and you will be presented with the following dialog,

There are a couple of settings to decide on. You can store a backup on the controller or locally, on the database node where the backup is taken. You need to decide on the location in which the backup should be stored, and which databases you’d like to backup; all data sets or separate schemas? See the image below:

The Advanced setting would take advantage of a cron-like configuration for more granularity. 

Timezone 

When scheduling backup with ClusterControl, all time must be scheduled in the UTC timezone of the ClusterControl server. The reason is to avoid the confusion of backup execution time. When working with a cluster, the database servers could be spread in different time zones and different geographical areas. Using one reference timezone to manage them all will ensure the backups are always executed at the correct time.

You can see the below image, how you could flag the options accordingly and there are tooltip icons which provide more information of the options you would like to leverage for your backup policy.

For this schedule, there was one more feature called Failover Host. If we enable this option,  in case of failure of the backup host, ClusterControl will pick another available node and trigger the backup on that node.

Once the schedule is created, you can edit the configuration backups, enable/disable the backup or delete the schedule under “Scheduled Backups” tab:

Backup Management with ClusterControl cli 

The s9s is a command line tool to interact with ClusterControl. This is especially useful for command line aficionados. 

​[root@centos11 vagrant]# s9s backup --list --cluster-id=37 --long --human-readable

ID PI CID V I STATE     OWNER  HOSTNAME      CREATED             SIZE TITLE

 5  -  37 - F COMPLETED XXXX   192.168.33.15 2020-12-31 01:50:22 4.2G Untitled Backup Record

 6  5  37 - I COMPLETED XXXX   192.168.33.15 2020-12-31 02:12:30  92K Untitled Backup Record

 8  -  37 - F COMPLETED XXXX   192.168.33.15 2021-01-11 00:07:19 1.7G Untitled Backup Record

Total 3

Summary

The database is a critical component of a Moodle platform, and a backup is one of the most important things to take care of while managing the database. It is important to get it right, as in case of major disaster, it is important to be able to recover the data. This can be conveniently achieved by using ClusterControl.