How to Backup Your Chamilo LMS MySQL Database

Backup is the process of creating and storing copies of data that can be used to protect organizations against data loss. Chamilo LMS relies on MySQL or MariaDB as a database, so it is important to make sure the data is backed up. Database failures can be the result of hardware or software failure, data corruption, or a human-caused event, such as a malicious attack (virus or malware), or accidental deletion of data. Backups allow data to be restored from an earlier point in time to help the business recover from an unplanned event.

What’s the best data backup solution for Chamilo MySQL Database ?

A solid backup strategy can make a big difference in your digital life and give you peace of mind. 

Here are few things to look for in a backup system.

• Ease to set up
• How quickly your data can be backed up
• The security of your data
• How quickly you can restore and recover your data 

ClusterControl provides you with centralized backup management for the Chamilo databases. 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. In this blog we are going to look into how to backup your Chamilo database using ClusterControl. 

Building a Database Backup Strategy 

A backup script scheduled with CRON is not a backup strategy. Silent failures can be quite dangerous, as it might go days or weeks before you discover that your backups are unusable. Are the backups secure and encrypted? Are the backup files stored offsite, to other locations? How about data retention rules?

ClusterControl provides comprehensive support for the backup management process. So let’s have a look at how it works.

ClusterControl provides two options:

1. Instant Backups 
2. Scheduled Backups

Under the “Backup” section, select “Create Backup”.

Instant Backups 

To take an instant backup, go to Backup -> Create Backup and you will be presented with the following dialog:

By default, it will take a mysqldump. But 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; the entire data sets or separate schemas? See the image below:

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

When scheduling backups with ClusterControl, time must be in the UTC timezone of the ClusterControl server. The reason is to avoid the confusion of backup execution time. When working with a replication or cluster setup, 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 can leverage for your backup policy.

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

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

Managing the backup using ClusterControl CLI 

The s9s is a command line tool to interact with ClusterControl. It allows you to take a backup on the command line. You just need to decide which node to backup and the backup method. The storage location by default will be located on the controller node, unless you specify the –on-node flag. If the backup destination directory does not exist, ClusterControl will create it for you. 

[root@centos11 vagrant]# s9s backup --create --backup-method=mysqldump --cluster-id=42 --nodes=xxxxx:3306 --backup-directory=/home/vagrant 

Job with ID 1733 registered.

The ID for the backup job is 1733. You can attach to this job if you would like to monitor the progress,

​[root@centos11 vagrant]# s9s job --wait --job-id=1733

Create mysqldump Backup

- Job 1733 RUNNING3   [█         ] ---% Job is running 

You can then list out the backups created for this cluster,

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

ID PI CID V I STATE     OWNER HOSTNAME CREATED          SIZE TITLE

 6  -  42 - - FAILED    xxxxx xxxxx 2021-01-26 00:57    0 Untitled Backup Record

 7  -  42 - F COMPLETED xxxxx xxxxx 2021-01-26 02:26 248K Untitled Backup Record

 8  -  42 - F COMPLETED xxxxx xxxxx 2021-01-26 02:27 248K Untitled Backup Record

Total 3

Conclusion 

Database backups are mandatory in the Chamilo environment. They help you protect your data and are in the center of any disaster recovery scenario. ClusterControl can help automate the backup process for your databases and, in case of failure, restore it with a few clicks. Also, you can be sure they are executed successfully and reliable so in case of disaster, you will not lose your data.