How to Backup Your Moodle MariaDB Database

Previously, we had blogged about backing up your Moodle MySQL Database. This time, it’s all about backing up your Moodle MariaDB Database. In fact, it shares the same approach when backing up your Moodle MySQL Database as with your Moodle MariaDB Database. However, since MariaDB 10.2, it has slowly deviated and continues to have drastic differences with the MySQL version. In this regard, pay attention to approaches you might have to consider when you are backing up your MariaDB in contrast to MySQL if you were from it.

Best Practices for Making Your Moodle MariaDB Backup

Let’s consider this topic first. Taking a backup for your Moodle data has to apply the best practices for your MariaDB backup as this provides you security and assurance especially when disaster or catastrophe comes up in unpredictable situations. 

So what’s up with this? Taking a backup for your Moodle has to do at least with the following backups:

• Logical Backup
• Physical copy of your backup
• Point-in-Time (Incremental) Recovery

Logical Backup

A logical backup of data is stored in a human-readable format like SQL. Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of backup is suitable for smaller amounts of data where you might edit the data values or table structure, or recreate the data on a different machine architecture. If you tend to use a huge database backup, ensure you have compression enabled. Caveat as this can take a lot of disk space especially.

In MariaDB, a common tool to use is using mysqldump, but shall change in the future with mariadb-dump starting MariaDB 10.4.6 or 10.5.2 onwards. Alternatively, a common tool to use for MySQL/MariaDB DBA’s is mydumper if you want a parallel backup for your logical backup copy. Though there are some issues in some versions or it might have problems especially for the latest versions of MariaDB. If you want to use this, then pay attention to your backup logs when creating a copy.

Physical Backup

Physical backup contains the database binary data which consist of raw copies of the directories and files that store database contents. This is an appropriate action to take especially for a large database and it’s faster to recover a full copy of the database compared to a logical backup copy. On the other hand, taking a full physical backup takes time especially if you have a very large data set. It also depends on what parameters you have enabled or set that can impact its backup ETA.

A common tool to use for MariaDB is using mariabackup. Mariabackup is an open source tool provided by MariaDB. It is a fork of Percona XtraBackup designed to work with encrypted and compressed tables, and is the recommended backup method for MariaDB databases.

Point-in-Time Recovery (PITR)

Point-in-time recovery refers to recovery of data changes up to a given point in time. This given point in time is the desired recovery objective that has been determined and required to be put back in place, that is applied during recovery. PITR is a point-forward recovery and that means you can restore data from desired starting time to the desired ending time, for the opposite read Using MariaDB Flashback on a MySQL Server. PITR is also considered an additional method of data protection, as it safeguards loss of important information.

In common situations, your PITR backup applicable for your type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made. Point-in-time recovery then brings the server up to date incrementally from the time of the full backup to a more recent time. It also speeds up building a replica within a replication cluster from catching up with your MariaDB primary or active-writer database.

So what are these backups? In MariaDB, the common backups applicable for your PITR are your binary logs. Binary logging has to be configured properly in your MariaDB database and it has to be enabled. If you are using ClusterControl, this might not be difficult for you to configure as it is configured for you automatically and is enabled especially when setting up a replication cluster.

When applying PITR, the most common tool to use is mysqlbinlog or mariadb-binlog for MariaDB 10.5.2 onwards.

Best Approach To Backup Your Moodle MariaDB Database

When performing your Moodle MariaDB Database, always take your backup during non-peak hours or when traffic is too low. Before doing this, make sure that you have tested the backup and that it has been successfully finished. Once finished, test a restore if your backup is useful or not and that it satisfies your needs when data recovery is needed or when you need your backup to create another set of clusters (QA, dev, or extend to another data center). Basically, the following subsections shall be the approach and setup you have to deal with.

Setup A Replica And Take The Backup On Replica

If you are not familiar with replication, read our white paper MySQL Replication for High Availability. Basically, do not spare your active-writer or primary database node for performing or running a backup. Taking a backup has to be tested and it has not to be performed in production if it has not yet been tested with a set of commands and its backup policy you have created. Once good, just leave it and aim it on a replica. You may take a backup from your primary/master MariaDB database if you are left with no choice or at least you are sure of what you are doing.

Run Backup During Non-peak Hours

When performing a backup, ensure that it is your peak hours. Your replica has to be zero lag as possible so the most up-to-date data shall be blacked up.

Create A Backup Policy

The backup policy consists of your parameters and schedule that your backup kicks in. For the parameters, make sure that it suffices your needs, i.e. security, compression, etc. The schedule has to be permanent so your backup shall be there when needed in times of disaster and data recovery is needed. You shall also be able to determine your recovery time objective so that you can determine how often your backup schedule shall run and when.

How To Create Backup of Your Moodle MariaDB Database

Using mariadb-dump/mysqldump

The command below shall create your database backup for Moodle. In this example, the database name is moodle. We included triggers, stored procedures or routines, and events. Print the GTID and the master information which is useful when you want to provision a replica from the primary or master database node.

$  /usr/bin/mariadb-dump --defaults-file=/etc/my.cnf --flush-privileges --hex-blob --opt --master-data=2 --single-transaction --skip-lock-tables --triggers --routines --events --gtid --databases moodle

You can simply replace mariadb-dump with mysqldump if you are using MariaDB version < 10.5.2. If you need to compressed, you can run the command below:

$  /usr/bin/mysqldump --defaults-file=/etc/my.cnf --flush-privileges --hex-blob --opt --master-data=2 --single-transaction --skip-lock-tables --triggers --routines --events --gtid --databases moodle |gzip -6 -c > /backups/backup-n1/mysqldump_2021-01-25_182643_schemaanddata.sql.gz

Using mariabackup

The mariabackup can be taken simply. For this, we will be using mbstream as the desired streaming and archival format. You can use the command below:

$ /usr/bin/mariabackup --defaults-file=/etc/my.cnf --backup --parallel 1 --stream=xbstream > backup.xbstream

If you aim to compress it, you can do the following command:

$ /usr/bin/mariabackup --defaults-file=/etc/my.cnf --backup --parallel 1 --stream=xbstream | gzip -6 - > backup.xbstream.gz

Using ClusterControl

If you switch to a managed solution especially for taking backup of your Moodle, ClusterControl takes it on a simple yet offers advanced features for taking backup. Take a look at the screenshot below:

Taking a backup is very simple and easy to create. All you need to do is to go to → Backup → Create Backup.

In the screenshot above, I have mysqldump, mysqldump with PITR compatible, and mariabackup. You can select which host to take the backup. As mentioned earlier, ensure you are taking the backup on the replica or slave. That means, make sure that the slave is selected. See screenshot below:

While selecting or choosing mysqldump, ClusterControl allows the user to choose the type of data to be dumped. This also includes a PITR compatible dump for backup. See screenshot below:

For your binary or physical backup, aside from mysqldump as your logical backup, you can choose mariabackup either full or incremental. See below:

Storage locations only allow the user to choose either to stay on the node or stream it to the ClusterControl host. If you have an external server that is not registered to the ClusterControl, you can use NFS to mount the volume you want your backup to be dumped. Although that might not be ideal but for some cases, this shall satisfy especially if the network bandwidth or network transfer is fast enough to stream the data locally to the other node via network.

Essentially, you can choose the backups to be uploaded to the cloud. You can see the screenshot earlier and just tick the checkbox to enable it just like below:

As mentioned earlier that ClusterControl takes the backup in a simple to use yet provides advanced features, here are the options you can set.

For mysqldump,

For mariabackup,

very easy to use. ClusterControl also offers backup verification and backup restore so it’s easy for you to determine if backup is useful or not. This helps that your Moodle database backup is useful especially when data recovery has to be applied for emergency and recovery.

Conclusion

It can be easy to backup your Moodle MariaDB database but when data gets bigger and traffic gets higher, it can be a great challenge. You just need to follow best practices, make sure you have secured your data, ensure your backup is verified and it is useful when data recovery has to be applied.