You are here
Backup Manager for MySQL, MariaDB and Percona Server (mysql_bman)
The MySQL Backup Manager (
mysql_bman) is a wrapper script for standard MySQL backup tools. The Problem with MySQL backup tools is, that they have many options and thus are overcomplicated and errors are easy made.
mysql_bman has the intention to make backups for 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 MySQL backup tools or which are only known from Enterprise backup solutions.
mysql_bman user say about
Mathias Brem DBA@DBAOnline on LinkedIn:
mysql backup manager is a very nice tool! Congratulations for FromDual! I made a shell script for catalog and maintained backups by
mysql_bman is the best!
Xtrabackup + mysql_bman!!!!
Where to get
mysql_bman is a part of the MyEnv utilities.
mysql_bman help you
The intention of
mysql_bman is to assist you in bigger MySQL set-ups where you have to follow some backup policies and where you need a serious backup concept.
To give you an impression of the power of the MySQL Backup Manager let us have a look at a little example:
shell> mysql_bman --target=bman:firstname.lastname@example.org --type=full --mode=logical --policy=daily \ --no-compress --backupdir=/mnt/slowdisk \ --archive --archivedir=/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.
To achieve this we have defined different backup types:
|full||full logical backup (|
|config||configuration file backup (|
|cleanup||clean-up of backup pieces older than n days|
|schema||backup of one or more schemas|
|privilege||privilege dump (SHOW GRANTS FOR)|
A backup type is specified with the option
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
The backup mode is specified with the option
--mode=<backup_mode>. The following backup modes are available:
|logical||do a logical backup (|
|physical||do a physical backup (|
Further we have introduced different backup policies. Policies are there to distinguish how different backups should be treated.
The following backup policies exist:
|daily||directory to store daily backups|
|weekly||directory to store weekly backups|
|monthly||directory to store monthly backups|
|quarterly||directory to store quarterly backups|
|yearly||directory to store yearly backups|
For example you could plan to do a daily 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:
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
Let us do an example:
shell> mysql_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 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).
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
--backupdir option is to control location of the backup files. The policy folders are automatically created under this
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
--archivedir option. For restore performance reasons it is recommended to always keep one or two generations of backups on you fast local drive. If you want to remove the backuped files from the
--backupdir destination after the archive job use the
If you want to omit to compress backups, either to safe time or because your location uses deduplicated drives you can use the
Per schema backup
Especially for hosting companies 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
mysql_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 database.
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.
mysql_bman configuration file
Specifying everything on the command line is cumbersome. Thus
mysql_bman considers a configuration file specified with the
mysql_bman configuration file looks for example as follows:
policy = daily target = email@example.com:3306 type = schema schema = -mysql policy = daily archive = on archivedir = /mnt/tape per-schema = on no-compress = 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.
If you want to track your backup history you can specify with the
--log option where your
mysql_bman log file should be located.
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 database for the catalog "default name is bman_catalog" then create its tables by using the option
--create in a special mysql_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 mysql_bman command.
Check the examples below for using catalog in mysql_bman.
A little more help you can get with the following command:
shell> mysql_bman --help
Do a full (logical = default) backup and store it in the daily policy folder:
shell> mysql_bman --firstname.lastname@example.org:3306 --type=full --policy=daily
Do a full physical backup and store it in the weekly policy folder:
shell> mysql_bman --email@example.com --type=full --mode=physical --policy=weekly
Do a binary-log backup omitting the password in the target and store it in the daily policy folder:
shell> mysql_bman --firstname.lastname@example.org:3307 --type=binlog --policy=daily
Do a MySQL configuration backup and store it in the weekly policy folder:
shell> mysql_bman --email@example.com: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> mysql_bman --firstname.lastname@example.org:3306 --type=structure --policy=monthly --instance-name=prod-db
Do a weekly structure backup and archive it to an other backup location:
shell> mysql_bman --email@example.com:3306 --type=structure --policy=weekly --archive --archivedir=/mnt/tape
Do a schema backup omitting the mysql schema:
shell> mysql_bman --firstname.lastname@example.org:3306 --type=schema --schema=-mysql --policy=daily --archive --archivedir=/mnt/tape
Do a schema backup only of
world and write it to their own files. Omit compressing these backups because they are located for example on deduplicated drives:
shell> mysql_bman --email@example.com:3306 --type=schema --schema=+foodmart,+world --per-schema --policy=daily --no-compress
Creation of a backup catalog (assuming you have created already a catalog database with the default name "bman_catalog"):
shell> mysql_bman --firstname.lastname@example.org:3306 --create
Backups against catalog:
shell> mysql_bman --email@example.com:3306 --firstname.lastname@example.org:3306 --instance-name=test --type=full --policy=daily
shell> mysql_bman --email@example.com:3306 --type=privilege --policy=daily --mode=logical