Shinguz: Cool new features in FromDual Backup and Recovery Manager 2.0.0

A while ago we released our FromDual Backup and Recovery Manager (brman) 2.0.0 for MariaDB and MySQL. So what are the new cool features of this new release?

First of all brman 2.0.0 is compatible with MariaDB 10.3 and MySQL 8.0:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=logical --policy=daily

Reading configuration from /etc/mysql/my.cnf
Reading configuration from /home/mysql/.my.cnf
No bman configuration file.

Command line: /home/mysql/product/brman-2.0.0/bin/bman.php --target=brman:******@127.0.0.1:3318 --type=full --mode=logical --policy=daily 

Options from command line
  target          = brman:******@127.0.0.1:3318
  type            = full
  mode            = logical
  policy          = daily

Resulting options
  config          = 
  target          = brman:******@127.0.0.1:3318
  type            = full
  mode            = logical
  policy          = daily
  log             = ./bman.log
  backupdir       = /home/mysql/bck
  catalog-name    = brman_catalog

Logging to   ./bman.log
Backupdir is /home/mysql/bck
Hostname is  chef
Version is   2.0.0 (catalog v0.2.0)

Start backup at 2018-08-13_11-57-31
  Binary logging is disabled.
  Schema to backup: mysql, foodmart, world, test

  schema_name      engine  cnt  data_bytes    index_bytes   table_rows 
  foodmart                    0             0             0           0
  mysql            CSV        2             0             0           4
  mysql            InnoDB     4         65536         49152          17
  mysql            MyISAM    25        515327        133120        2052
  test             InnoDB     3         49152             0           0
  world                       0             0             0           0

  /home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318 --all-databases  --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events
  to Destination: /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql
  Backup size is 488835
  Backup does NOT contain any binary log information.
  Do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql
  md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql
  md5 = 31cab19021e01c12db5fe49165a3df93
  /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_11-57-31.sql
End backup at 2018-08-13 11:57:31 (rc=0)

Next brman also support mariabackup now:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=physical --policy=daily
...
Start backup at 2018-08-13_12-02-18
  Backup with tool mariabackup version 10.3.7 (from path /home/mysql/product/mariadb-10.3/bin/mariabackup).
  Schema to backup: mysql, foodmart, world, test

  schema_name      engine  cnt  data_bytes    index_bytes   table_rows 
  foodmart                    0             0             0           0
  mysql            CSV        2             0             0           4
  mysql            InnoDB     4         65536         49152          17
  mysql            MyISAM    25        515327        133120        2052
  test             InnoDB     3         49152             0           0
  world                       0             0             0           0

  Binary logging is disabled.
  /home/mysql/product/mariadb-10.3/bin/mariabackup --defaults-file=/tmp/bck_full_2018-08-13_12-02-18.cnf --user=brman --host=127.0.0.1 --port=3318 --no-timestamp --backup --target-dir=/home/mysql/bck/daily/bck_full_2018-08-13_12-02-18
180813 12:02:19 Connecting to MySQL server host: 127.0.0.1, user: brman, password: set, port: 3318, socket: not set
Using server version 10.3.7-MariaDB
/home/mysql/product/mariadb-10.3/bin/mariabackup based on MariaDB server 10.3.7-MariaDB Linux (x86_64)
mariabackup: uses posix_fadvise().
mariabackup: cd to /home/mysql/database/mariadb-103/data/
mariabackup: open files limit requested 0, set to 1024
mariabackup: using the following InnoDB configuration:
mariabackup:   innodb_data_home_dir =
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
mariabackup:   innodb_log_group_home_dir = ./
2018-08-13 12:02:19 0 [Note] InnoDB: Number of pools: 1
mariabackup: Generating a list of tablespaces
2018-08-13 12:02:19 0 [Warning] InnoDB: Allocated tablespace ID 59 for mysql/transaction_registry, old maximum was 0
180813 12:02:19 >> log scanned up to (15975835)
180813 12:02:19 [01] Copying ibdata1 to /home/mysql/bck/daily/bck_full_2018-08-13_12-02-18/ibdata1
180813 12:02:19 [01]        ...done
...

Then brman 2.0.0 supports seamlessly all three physical backup methods (mariabackup, xtrabackup, mysqlbackup) in their newest release.

On a customer request we have added the option --pass-through to pass additional specific options through to the final back-end application (mysqldump, mariabackup, xtrabackup, mysqlbackup):

As an example the customer wanted to pass through the option --ignore-table to mysqldump:

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

...
Start backup at 2018-08-13_12-11-40
  Schema to backup: world

  schema_name      engine  cnt  data_bytes    index_bytes   table_rows 
  world            InnoDB     3        655360             0        5411

  Binary logging is disabled.
  /home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318  --quick --single-transaction --flush-logs --triggers --routines --hex-blob --databases 'world' --events --ignore-table=world.CountryLanguage
  to Destination: /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql
  Backup size is 217054
  Backup does NOT contain any binary log information.
  Do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql
  md5sum --binary /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql
  md5 = f07e319c36ee7bb1e662008c4c66a35a
  /usr/bin/pigz -6 /home/mysql/bck/daily/bck_schema_2018-08-13_12-11-40.sql
End backup at 2018-08-13 12:11:40 (rc=0)

In the field it is sometimes wanted to not purge the binary logs during a binlog backup. So we added the option --no-purge to not purge binary logs during binlog backup. It looked like this before:

shell> bman --target=brman:secret@127.0.0.1:3326 --type=binlog --policy=binlog
...
Start backup at 2018-08-13_12-16-48
  Binlog Index file is: /home/mysql/database/mysql-80/data/binlog.index
  Getting lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock
  Releasing lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock
  FLUSH /*!50503 BINARY */ LOGS

  Copy /home/mysql/database/mysql-80/data/binlog.000006 to /home/mysql/bck/binlog/bck_binlog.000006
  Binary log binlog.000006 begin datetime is: 2018-08-13 12:14:14 and end datetime is: 2018-08-13 12:14:30
  Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000006
  md5sum --binary /home/mysql/bck/binlog/bck_binlog.000006
  md5 = a7ae2a271a6c90b0bb53c562c87f6f7a
  /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000006
  PURGE BINARY LOGS TO 'binlog.000007'

  Copy /home/mysql/database/mysql-80/data/binlog.000007 to /home/mysql/bck/binlog/bck_binlog.000007
  Binary log binlog.000007 begin datetime is: 2018-08-13 12:14:30 and end datetime is: 2018-08-13 12:14:31
  Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000007
  md5sum --binary /home/mysql/bck/binlog/bck_binlog.000007
  md5 = 5b592e597241694944d70849d7a05f53
  /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000007
  PURGE BINARY LOGS TO 'binlog.000008'
...

and like this after:

shell> bman --target=brman:secret@127.0.0.1:3326 --type=binlog --policy=binlog --no-purge

...
Start backup at 2018-08-13_12-18-52
  Binlog Index file is: /home/mysql/database/mysql-80/data/binlog.index
  Getting lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock
  Releasing lock: /home/mysql/product/brman-2.0.0/lck/binlog-logical-binlog.lock
  FLUSH /*!50503 BINARY */ LOGS

  Copy /home/mysql/database/mysql-80/data/binlog.000015 to /home/mysql/bck/binlog/bck_binlog.000015
  Binary log binlog.000015 begin datetime is: 2018-08-13 12:16:48 and end datetime is: 2018-08-13 12:18:41
  Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000015
  md5sum --binary /home/mysql/bck/binlog/bck_binlog.000015
  md5 = 1f9a79c3ad081993b4006c58bf1d6bee
  /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000015

  Copy /home/mysql/database/mysql-80/data/binlog.000016 to /home/mysql/bck/binlog/bck_binlog.000016
  Binary log binlog.000016 begin datetime is: 2018-08-13 12:18:41 and end datetime is: 2018-08-13 12:18:42
  Do MD5 checksum of /home/mysql/bck/binlog/bck_binlog.000016
  md5sum --binary /home/mysql/bck/binlog/bck_binlog.000016
  md5 = ef1613e99bbfa78f75daa5ba543e3213
  /usr/bin/pigz -6 /home/mysql/bck/binlog/bck_binlog.000016
...

To make the logical backup (mysqldump) slightly faster we added the --quick option. This is done automatically and you cannot influence this behaviour.

  /home/mysql/product/mariadb-10.3/bin/mysqldump --user=brman --host=127.0.0.1 --port=3318  --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events

Some of our customers use brman in combination with MyEnv and they want to have an overview of used software. So we made the version output of brman MyEnv compliant:

mysql@chef:~ [mariadb-103, 3318]> V

The following FromDual Toolbox Packages are installed:
------------------------------------------------------------------------
MyEnv:           2.0.0
BRman: 2.0.0
OpsCenter:       0.4.0
Fpmmm:           1.0.1
Nagios plug-ins: 1.0.1
O/S:             Linux / Ubuntu
Binaries:        mysql-5.7
                 mysql-8.0
                 mariadb-10.2
                 mariadb-10.3
------------------------------------------------------------------------

mysql@chef:~ [mariadb-103, 3318]>

In MySQL 5.7 general tablespaces were introduced. The utility mysqldump is not aware of general tablespaces and does not dump this information. This leads to errors during restore. FromDual brman checks for general tablespaces and writes them to the backup log so you can later extract this information at least from there. We consider this as a bug in mysqldump. MariaDB up to 10.3 has not implemented this feature yet so it is not affected of this problem.

...
Start backup at 2018-08-13_12-25-46
  WARNING: 5 general tablespaces found! mysqldump does NOT dump tablespace creation statements.
CREATE TABLESPACE `brman_test_ts` ADD DATAFILE './brman_test_ts.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB
CREATE TABLESPACE `ts2` ADD DATAFILE './ts2.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB
CREATE TABLESPACE `ts3` ADD DATAFILE './ts3.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB
CREATE TABLESPACE `ts4` ADD DATAFILE './ts4.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB
CREATE TABLESPACE `ts1` ADD DATAFILE './ts1.ibd' FILE_BLOCK_SIZE=4096 ENGINE=InnoDB
...

FromDual brman backups are quite complex and can run quite some long time thus timestamps are logged so we can find out where the time is spent or where the bottlenecks are:

...
  At 2018-08-13 12:27:17 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0
  md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0
  md5 = d41d8cd98f00b204e9800998ecf8427e
  At 2018-08-13 12:27:17 compress file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0
  /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ib_logfile0

  At 2018-08-13 12:27:18 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1
  md5sum --binary /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1
  md5 = 097ab6d70eefb6e8735837166cd4ba54
  At 2018-08-13 12:27:18 compress file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1
  /usr/bin/pigz -6 /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/ibdata1

  At 2018-08-13 12:27:19 do MD5 checksum of uncompressed file /home/mysql/bck/daily/bck_full_2018-08-13_12-27-16/xtrabackup_binlog_pos_innodb
...

A general FromDual policy is to not use the MariaDB/MySQL root user for anything except direct DBA interventions. So backup should be done with its own user. FromDual suggest brman as a username and the utility complains with a warning if root is used:

shell> bman --target=root@127.0.0.1:3318 --type=full --policy=daily
...
Start backup at 2018-08-13_12-30-29

WARNING: You should NOT use the root user for backup. Please create another user as follows:

         CREATE USER 'brman'@'127.0.0.1' IDENTIFIED BY 'S3cret123';
GRANT ALL ON *.* TO 'brman'@'127.0.0.1';
If you want to be more restrictive you can grant privileges as follows:
GRANT SELECT, LOCK TABLES, RELOAD, PROCESS, TRIGGER, SUPER, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO 'brman'@'127.0.0.1';
Additionally for MySQL Enterprise Backup (MEB):
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'brman'@'127.0.0.1';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'brman'@'127.0.0.1';
GRANT FILE ON *.* TO 'brman'@'127.0.0.1';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'brman'@'127.0.0.1';
Additionally for MariaBackup / XtraBackup:
GRANT INSERT, SELECT ON PERCONA_SCHEMA.xtrabackup_history TO 'brman'@'127.0.0.1';
...

Some customers have implemented a monitoring solution. FromDual brman can report backup return code, backup run time and backup size to the FromDual Performance Monitor for MariaDB and MySQL (fpmmm/Zabbix) now:

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

shell> cat /var/cache/fpmmm/fpmmm.FromDual.mariadb-103.cache
mariadb-103 FromDual.MySQL.backup.full_logical_rc 1534156619 "0"
mariadb-103 FromDual.MySQL.backup.full_logical_duration 1534156619 "129"
mariadb-103 FromDual.MySQL.backup.full_logical_size 1534156619 "7324744568"

Some customers run their databases on shared hosting systems or in cloud solutions where they do not have all the needed database privileges. For those users FromDual brman is much less intrusive now and allows backups on those restricted systems as well:

#
# /home/shinguz/etc/brman.conf
#

policy                = daily
target                = shinguz_brman:secret@localhost
type                  = schema
per-schema            = on
schema                = -shinguz_shinguz
log                   = /home/shinguz/log/bman_backup.log
backupdir             = /home/shinguz/bck


shell> /home/shinguz/brman/bin/bman --config=/home/shinguz/etc/brman.conf 1>/dev/null
...
  WARNING: Binary logging is enabled but you are lacking REPLICATION CLIENT privilege. I cannot get Master Log File and Pos!
WARNING: I cannot check for GENERAL tablespaces. I lack the PROCESS privilege. This backup might not restore in case of presence of GENERAL tablespaces.
...

Details: Check for binary logging is made less intrusive. If RELOAD privilege is missing --master-data and/or --flush-logs options are omitted. Schema backup does not require SHOW DATABASES privilege any more.

Some customers want to push theire backups directly to an other server during backup (not pull from somewhere else). For those customers the new option --archivedestination was introduced which replaces the less powerfull option --archivedir which is deprecated. So archiving with rsync, scp and sftp is possible now (NFS mounts was possible before already):

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --policy=daily --archivedestination=sftp://oli@backup.fromdual.com:22/home/oli/bck/production/daily/
...
  /home/mysql/product/mysql-5.7.21/bin/mysqldump --user=root --host=127.0.0.1 --port=33006 --master-data=2 --quick --single-transaction --triggers --routines --hex-blob --events 'tellmatic'
  to Destination: /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql
  Backup size is 602021072
  Binlog file is mysql-bin.019336 and position is 287833
  Do MD5 checksum of uncompressed file /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql
  md5sum --binary /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql
  md5 = 06e1a0acd5da8acf19433b192259c1e1
  /usr/bin/pigz -6 /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql
  Archiving /home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql.gz to sftp://oli@backup.example.com:/home/oli/bck/production/daily/
echo 'put "/home/mysql/backup/daily/bck_schema_tellmatic_2018-08-13_11-41-26.sql.gz"' | sftp -b - -oPort=22 oli@backup.fromdual.com:/home/oli/bck/production/daily/

End backup at 2018-08-13 11:42:19 (rc=0)

Taxonomy upgrade extras: 

via Planet MySQL
Shinguz: Cool new features in FromDual Backup and Recovery Manager 2.0.0