Link

How to back up MySQL, MariaDB or Postgres

Many services and servers require a database, whose content needs to be backed up as well. There are different ways to achieve this using Borg or Borgmatic. One method you should avoid is to copy the internal data directory of the database, as it’s not save to copy those files while the database service is running. A better way is to create a compressed dump of the actual data and add that file to the backup. There are multiple ways to do so:

Using a Standalone Backup Script

Most popular distributions include scripts, like automysqlbackup1 or autopostgresqlbackup2. Those scripts will usually save a daily dump of all databases to a folder, like /var/lib/automysqlbackup. By running this script before your backup and including the dump folder in your source directories, you get a timely backup of your database.

For Borgmatic, you can use pre-backup hooks to run the database dump script:

hooks:
    before_backup:
        - /usr/sbin/autopostgresqlbackup

Using Borgmatic’s Built-In Database Hook

If you are already using Borgmatic and have a large database, you can also use their built-in database hooks to dump the database content and include it in your backup. The advantage of this technique is that the DB backup is deleted after each backup run and you don’t need to store multiple versions locally.

To dump all MySQL and Postgres DBs before each backup, one would use the following snippet:3

hooks:
    postgresql_databases:
        - name: all
    mysql_databases:
        - name: all

Have any other questions? Email Us!

References