FromDual Backup and Recovery Manager (brman) User Guide

Contents

• About
• Where to download brman
• What brman users say about
• FromDual Backup Manager (bman)
  • Where can bman help you
  • bman example
  • Backup types
  • Backup modes
  • Backup policies
  • Options
  • Target
  • Backup location, archiving, compressing and clean-up
  • Per schema backup
  • Instance name
  • bman configuration file
  • Simulate what happens
  • Logging
  • Using Catalog
  • Examples
  • Examples with Archiving an Cleaning
  • Example with backup shipping
  • Examples with other options
  • Examples with Backup Catalog
• FromDual Recovery Manager (rman)
  • Progress Indicator

About

The FromDual Backup and Recovery Manager for MariaDB and MySQL (brman) is an application to ease the use of the various MariaDB/MySQL backup tools. The Problem with MariaDB/MySQL backup tools is, that they have many options and thus are over-complicated and errors are easy made.

bman has the intention to make backups for MariaDB/MySQL easier and technically correct. This means it should per default not allow non-consistent backups or complain if some functions or parameters are used in the wrong way to guarantee proper backups.

In addition it has added some nice features which are missing in standard MariaDB/MySQL backup tools or which are only known from Enterprise backup solutions.

Where to download brman

The FromDual Backup and Recovery Manager for MariaDB and MySQL (brman) can be downloaded from our website.

What brman users say about

Mathias Brem DBA@DBAOnline on LinkedIn:

Ow! Nice!
FromDual Backup Manager is a very nice tool! Congratulations for FromDual! I made a shell script for catalog and maintained backups by xtrabackup, but bman is the best!

Xtrabackup + bman!!!!

FromDual Backup Manager (bman)

Where can bman help you

The intention of bman is to assist you in bigger MariaDB/MySQL set-ups where you have to follow some backup policies and where you need a serious backup concept.

bman example

To give you an impression of the power of the FromDual Backup Manager let us have a look at a little example:

shell> bman --target=brman:secret@192.168.1.42 --type=full --mode=logical --policy=daily \
--no-compress --backupdir=/mnt/slowdisk \
--archive --archivedestination=file:///mnt/nfsmount

With this backup method we do a logical full backup (mysqldump is triggered in the background). The backup is stored in the location for backups with the daily policy and is NOT compressed to speed up the backup by saving CPU power AND because the backup device is a de-duplicating drive. Then the backup is archived to and NFS mount.

Backup types

To achieve this we have defined different backup types:

A backup type is specified with the option --type=<backup_type>.

Backup modes

A backup can either be logical or physical. A logical backup is typically what you do with mysqldump. A physical backup is typically a physical file copy without looking into the data. That is what for example mariabackup does.

The backup mode is specified with the option --mode=<backup_mode>. The following backup modes are available:

Backup policies

Further we have introduced different backup policies. Policies are there to distinguish how different backups should be treated.

The following backup policies exist:

For example you could plan to do a daily MariaDB/MySQL backup with binary logs with a retention policy of 7 days. But once a week you want to do a weekly backup consisting of a full backup, a configuration backup and a structure dump. But this weekly backup you want to keep for 6 months. And because of legal reasons you want to do a yearly backup with a retention policy of 10 years.

A backup policy is specified with the --policy=<backup_policy> option. This leads us to the retention time:

Options

The retention time which should be applied to a specific backup policy you can specify with the option --retention=<period_in_days>. The retention option means that a backup is not deleted before this amount of days when you run a clean-up job with bman.

Let us do an example:

shell> bman --type=cleanup --policy=daily --retention=30

This means that all backups in the daily policy should be deleted when they are older than 30 days.

Target

With the --target option you specify the connect string to the database to backup. This database can be located either local (all backup types can be used) or remote (only client/server backup types can be used (mysqldump)).

A target looks as follows: user:password@host:port (similar to URI specification) whereas you can omit password and port.

Backup location, archiving, compressing and clean-up

The --backupdir option is to control location of the backup files. The policy folders are automatically created under this --backupdir location.
If you have a second layer of backup stores (e.g. tapes or slow backup drives or deduplicated drives or NFS drives) you can use the --archive option to copy your backup files to this second layer storage which is specified with the --archivedestination option. For restore performance reasons it is recommended to always keep one or two generations of backups on your fast local drive. If you want to remove the backuped files from the --backupdir destination after the archive job use the --cleanup option.
If you want to omit to compress backups, either to safe time or because your location uses deduplicated drives you can use the --no-compress option.

Per schema backup

Especially for shared hosting companies (or multi tenant applications) a full database backup is typically not the right backup strategy because a restore of one specific customer (= schema) is very complicated. For this case we have the --per-schema option. bman will do a backup of the whole database schema by schema. Keep in mind: This breaks consistency among schemas!

Sometimes you want to do a schema backup only for some specific schemas for this you can use the --schema option. This option allows you to specify schemas to backup or not to backup. --schema=+a,+b means backup schema a and b. --schema=-a,-b means backup all schemas except a and b.
The second variant is less error prone because you do not forget to backup a new schema.

Instance name

MariaDB/MySQL does not know the concept of naming an instance (mysqld). But for bigger environments it could be useful to uniquely name each instance. For this purpose we have introduced the option --instance-name=<give_it_a_name>. This instance name should be unique within your whole company. But we do not enforce it atm. The instance name is used to name backup files and later to identify the backup history of an instance in our backup catalog and to allow us to track the files for restore.

bman configuration file

Specifying everything on the command line is cumbersome. Thus bman considers a configuration file specified with the --config=<config_file> option.
A bman configuration file looks for example as follows:

policy                = daily
target                = brman:secret@127.0.0.1:3306
type                  = schema
schema                = -mysql
archive               = on
archivedestination    = file:///mnt/tape
per-schema            = on
no-compress           = on
no-memory-table-check = on

Simulate what happens

For the Sissies among us (as for example me) we have the --simulate option. This option simulates nearly all steps as far as possible without executing really anything. This option is either for testing some features or for debugging purposes.

Logging

If you want to track your backup history you can specify with the --log option where your bman log file should be located.

Using Catalog

It will be very useful when you can store your backups metadata in the database so you can check them in the future and to find out the backup criteria (type, mode, instance-name, ... etc) for specific backup processes. This could be achieved by using the catalog feature.

To activate this feature you have to create a schema for the catalog "default name is brman_catalog" then create its tables by using the option --create in a special bman command (check examples below).
Finally, to store your backup metadata in the catalog what you only have to do is adding the option --catalog=catalog_connection_string to the normal bman command.
Check the examples below for using catalog in bman.

Examples

To just see if bman works correctly just run:

shell> bman --version

To get a compact help of what bman can do run the following command. This output is the most reliable and most up-to-date source of information about brman (documentation may not be up-to-date):

shell> bman --help

Do a full (logical = default) backup and store it in the daily policy folder:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=full --policy=daily

Do a full physical backup and store it in the weekly policy folder:

shell> bman --target=brman:secret@127.0.0.1 --type=full --mode=physical --policy=weekly

Do a binary log backup omitting the password in the target and store it in the binlog policy folder. The binary logs should not be purged during the backup (otherwise they would):

shell> bman --target=brman@192.168.1.42:3307 --type=binlog --policy=binlog --no-purge

Do a schema backup omitting the mysql schema:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=schema --schema=-mysql --policy=daily --archive --archivedestination=file:///mnt/tape

Do a schema backup only of foodmart and world and write each backup to its own own file. Omit compressing these backups because they are located for example on deduplicated drives:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=schema --schema=+foodmart,+world --per-schema --policy=daily --no-compress

Do a configuration backup (my.cnf) and store it in the weekly policy folder:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=config --policy=weekly

Do a structure backup and store it in the monthly policy folder and name the file with the instance name:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=structure --policy=monthly --instance-name=prod-db

Do a weekly structure backup and archive it to an other backup location:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=structure --policy=weekly --archive --archivedestination=file:///mnt/tape

Do a weekly privilege backup:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=privilege --policy=weekly --mode=logical

Do a daily privilege backup per schema:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=privilege --policy=daily --per-schema

Examples with Archiving an Cleaning

Archive backup to other location:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=structure --policy=weekly --archive --archivedestination=file:///mnt/tape --cleanup

Cleanup old backups:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=cleanup --policy=daily --retention=30d

Cleanup old backups from archive location:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=cleanup --policy=daily --retention=30d --archive --archivedestination=file:///mnt/nfsmount

Example with backup shipping

If you want to ship your backup to an other (backup) server after database backup you can use the option --archivedestination. The protocols file, scp, sftp and rsync are supported:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=full --policy=daily --archivedestination=sftp://backup@backup.example.com:22/home/backup/production/daily/

Examples with other options

If you want to pass through options to the underlying backup utility (mysqldump, mariabackup, xtrabackup, mysqlbackup) you can use the --pass-through option. In the following example the mysqldump utility omits dumping the CountryLanguage table in a world schema backup:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=schema --mode=logical --policy=daily --schema=+world --pass-through="--ignore-table=world.CountryLanguage"

If you are using the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) and/or Zabbix for database monitoring you can send the backup information also to your monitoring solution for getting trends and see errors or anomalies:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=full --policy=daily --fpmmm-hostname=mariadb-103 --fpmmm-cache-file=/var/cache/fpmmm/fpmmm.FromDual.mariadb-103.cache

Examples with Backup Catalog

Creation of a backup catalog (assuming you have created already a catalog schema with the default name brman_catalog):

shell> bman --catalog=brman:secret@127.0.0.1:3306 --create

Backups against catalog:

shell> bman --target=brman:secret@127.0.0.1:3306 --catalog=brman:secret@127.0.0.1:3306 --instance-name=test --type=full --policy=daily

FromDual Recovery Manager (rman)

Progress Indicator

Since version 2.1.0 the FromDual Recovery Manager (rman) has also a progress indicator for the restore of logical backups made with mysqldump.

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql

Reading configuration from /etc/mysql/my.cnf
No rman configuration file.

Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql --policy=daily --instance-name=qamariadb102 

Options from command line
  target                = brman:******@127.0.0.1:3308
  type                  = full
  mode                  = logical
  progress              = 
  backup-name           = bck_qamariadb102_full_2019-08-20_21:15:23.sql
  policy                = daily
  instance-name         = qamariadb102

Resulting options
  target                = brman:******@127.0.0.1:3308
  type                  = full
  mode                  = logical
  progress              = 
  backup-name           = bck_qamariadb102_full_2019-08-20_21:15:23.sql
  policy                = daily
  instance-name         = qamariadb102
  log                   = ./rman.log
  datadir               = /var/lib/mysql
  owner                 = mysql
  backupdir             = /home/mysql/bck
  binlog-policy         = binlog


Logging to   ./rman.log
Backupdir is /home/mysql/bck
Version is   2.2.1

Start restore at 2019-08-20 21:18:46
  mysql --user=brman --password=****** --host=127.0.0.1 --port=3308
  From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz

  Restore progress:
. schema foodmart
. schema fromdual_a
. . table                                  audit         1 statements,            3 rows,                171 bytes
. . table                                     c1         1 statements,            3 rows,                 42 bytes
. . table                                     c2         1 statements,            3 rows,                 42 bytes
. . table                                  child         1 statements,            3 rows,                177 bytes
. . table                                 parent         1 statements,            3 rows,                175 bytes
. schema fromdual_b
. . table                                  audit         1 statements,            3 rows,                171 bytes
. . table                                     c1         1 statements,            3 rows,                 42 bytes
. . table                                     c2         1 statements,            3 rows,                 42 bytes
. . table                                  child         1 statements,            3 rows,                177 bytes
. . table                              employees         0 statements,            0 rows,                  0 bytes
. . table                                 parent         1 statements,            3 rows,                175 bytes
. schema fromdual_c
. . table                                  audit         1 statements,            3 rows,                171 bytes
. . table                                     c1         1 statements,            3 rows,                 42 bytes
. . table                                     c2         1 statements,            3 rows,                 42 bytes
. . table                                  child         1 statements,            3 rows,                177 bytes
. . table                                 parent         1 statements,            3 rows,                175 bytes
    ...
. schema test
. . table                                   test       347 statements,         4621 rows,             286528 bytes
. schema world
. . table                                   City         1 statements,         4079 rows,             177139 bytes
. . table                                Country         1 statements,          239 rows,              36481 bytes
. . table                        CountryLanguage         1 statements,          984 rows,              26160 bytes
. schema brman_catalog
. schema foodmart
. schema fromdual_a
. schema fromdual_b
. schema fromdual_c
. schema mysql
. schema test
. schema test_catalog
. schema world
  Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736
  WARNING: Progress numbers for Total Byte Counter may be different of dump file size.
  
  Restore time was: 0d 0h 1' 28"
End restore at 2019-08-20 21:20:14 (rc=0)

The overhead of FromDual Recovery Manager progress indicator is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration.