How to back up a database with Borgmatic

Jack Wallen shows you how to configure the Borgmatic tool to back up your databases with ease.

I recently walked you through the process of installing the Borgmatic open-source backup solution. In that tutorial, I showed you how to back up directories on your Linux servers with Borgmatic. This time around I’m going to show you how easy it is to back up your databases with the same tool.

Borgmatic is capable of backing up databases such as MySQL, PostgreSQL and MongoDB. You can backup a single database, a collection of databases or all of your databases. Even better, it’s really simple.

Combine Borgmatic’s ability to backup directories and databases, and you have a pretty impressive solution to ensure you have reliable backups for your data.

Let’s find out just how easy it is to back up databases with Borgmatic.

SEE: 40+ open source and Linux terms you need to know (TechRepublic Premium)

What you’ll need

To make this work, you’ll need the following:

• A running instance of Linux with Borgmatic installed
• A database to backup
• A user with sudo privileges

With those things at the ready, let’s set this up.

How to create a new config file

Let’s create a new backup with the command:

generate-borgmatic-config -d database.yaml

This will create a file, named database.yaml, where you’ll configure the database backup.

Next, initialize a new repository for the database backups with:

borg init -e repokey database.borg

How to configure the database backup

Open the new file for editing with the command:

nano database.yaml

First, edit the repositories section, so it reflects our newly initialized database repository. That section will look like this:

repositories:

- database.borg

Next, instead of commenting out the necessary lines of code to set up the database backup, we’ll create a new section. Locate the line:

# hooks:

Under that line, let’s create a configuration for a MySQL database that looks like this:

hooks:

mysql_databases:

- name: DBNAME

username: USERNAME

password: PASSWORD

Where:

• DBNAME is the name of the database to be backed up (use all to back up all databases)
• USERNAME is a MySQL user who has access rights to the database(s) to be backed up
• PASSWORD is the password for the username with rights to the database(s)

Let’s say you wanted to backup the databases employees, customers and products with the user dbuser and a password of &ut#19-IotR. That configuration would look like this:

hooks:

mysql_databases:

• name: employees

username: dbuser

password: &ut#19-IotR

• name: customers

username: dbuser

password: &ut#19-IotR


• name: products

username: dbuser

password: &ut#19-IotR

If you have different usernames or passwords for each database, you could configure them as needed. Also, if you have to backup PostgreSQL or mongodb databases, those sections will look similar, only start with either postgresql_databases: or mongodb_databases.

Save and close the file.

How to run the backup

We’ll run an initial backup with the command:

sudo borgmatic --config database.yaml --verbosity 1

First, you’ll be prompted for your sudo password, followed by the password you created for the database.borg repository. Upon successful authentication, the backup will begin and complete without errors. When the backup completes, you’ll find the data in the database.borg directory.

And that, my friends, is how you back up databases with Borgmatic. Make sure to check out the original tutorial to find out how to set this up such that it runs automatically.

Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the latest tech advice for business pros from Jack Wallen.