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)
via Planet MySQL
Shinguz: Cool new features in FromDual Backup and Recovery Manager 2.0.0