Since version 3.6 MEB supports backups to tape through the SBT interface.The officially supported tool for such backups to tape is Oracle Secure Backup (OSB).But there are a lot of other Storage Managers. MEB allows to use them through the SBT interface. Since version 3.7 it also has option --sbt-environment which allows to pass environment variables, not needed by OSB, to third-party managers. At the same time MEB can not guarantee it would work with all of them.This month we were contacted by a customer who wanted to use IBM Tivoli Storage Manager (TSM) with MEB. We could only say them same thing I wrote in previous paragraph: this solution is supposed to work, but you have to be pioneers of this technology. And they agreed.
They agreed to be the pioneers and so the story begins.MEB requires following options to be specified by those who want to connect it to SBT interface:--sbt-database-name: a name which should be handed over to SBT interface. This can be any name. Default, MySQL, works for most cases, so user is not required to specify this option.--sbt-lib-path: path to SBT library. For TSM this library comes with "Data Protection for Oracle", which, in its turn, interfaces with Oracle Recovery Manager (RMAN), which uses SBT interface. So you need to install it even if you don't use Oracle.--sbt-environment: environment for third-party manager. This option is not needed when you use OSB, but almost always necessary for third-party SBT managers. TSM requires variable TDPO_OPTFILE to be set and point to the TSM configuration file.--backup-image=sbt:: path to the image. Prefix "sbt:" indicates that image should be sent through SBT interfaceSo full command in our case would look like:
./mysqlbackup --port=3307 --protocol=tcp --user=backup_user --password=foobar \
--backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \
--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt" --backup-dir=/path/to/my/dir backup-to-imageAnd this command results in the following output log:
MySQL Enterprise Backup version 3.7.1 [2012/02/16]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.
INFO: Starting with following command line ...
./mysqlbackup --port=3307 --protocol=tcp --user=backup_user
--password=foobar --backup-image=sbt:my-first-backup
--sbt-lib-path=/usr/lib/libobk.so
--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt"
--backup-dir=/path/to/my/dir backup-to-image
sbt-environment: 'TDPO_OPTFILE=/path/to/my/tdpo.opt'
INFO: Got some server configuration information from running server.
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-to-image' run mysqlbackup
prints "mysqlbackup completed OK!".
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /path/to/data
innodb_data_home_dir = /path/to/data
innodb_data_file_path = ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M
innodb_log_group_home_dir = /path/to/data
innodb_log_files_in_group = 2
innodb_log_file_size = 268435456
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /path/to/my/dir/datadir
innodb_data_home_dir = /path/to/my/dir/datadir
innodb_data_file_path = ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M
innodb_log_group_home_dir = /path/to/my/dir/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 268435456
Backup Image Path= sbt:my-first-backup
mysqlbackup: INFO: Unique generated backup id for this is 13297406400663200
120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Data Protection for Oracle: version 5.5.1.0'
120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.5.1.0'
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: System tablespace file format is Antelope.
mysqlbackup: INFO: Found checkpoint at lsn 31668381.
mysqlbackup: INFO: Starting log scan from lsn 31668224.
120220 8:54:00 mysqlbackup: INFO: Copying log...
120220 8:54:00 mysqlbackup: INFO: Log copied, lsn 31668381.
We wait 1 second before starting copying the data files...
120220 8:54:01 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata1 (Antelope file format).
mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000
120220 8:55:30 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata2 (Antelope file format).
mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000
120220 8:57:18 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata3 (Antelope file format).
mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
120220 08:57:22 mysqlbackup: INFO: Starting to lock all the tables....
120220 08:57:22 mysqlbackup: INFO: All tables are locked and flushed to disk
mysqlbackup: INFO: Opening backup source directory '/path/to/data/'
120220 08:57:22 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/path/to/data/'
mysqlbackup: INFO: Backing up the database directory 'mysql'
mysqlbackup: INFO: Backing up the database directory 'test'
mysqlbackup: INFO: Copying innodb data and logs during final stage ...
mysqlbackup: INFO: A copied database page was modified at 31668381.
(This is the highest lsn found on page)
Scanned log up to lsn 31670396.
Was able to parse the log up to lsn 31670396.
Maximum page number for a log record 328
120220 08:57:23 mysqlbackup: INFO: All tables unlocked
mysqlbackup: INFO: All MySQL tables were locked for 0.000 seconds
120220 08:59:01 mysqlbackup: INFO: meb_sbt_backup_close: blocks: 4162 size: 1048576 bytes: 4363985063
120220 8:59:01 mysqlbackup: INFO: Full backup completed!
mysqlbackup: INFO: MySQL binlog position: filename bin_mysql.001453, position 2105
mysqlbackup: WARNING: backup-image already closed
mysqlbackup: INFO: Backup image created successfully.:
Image Path: 'sbt:my-first-backup'
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 31668224
End LSN : 31670396
-------------------------------------------------------------
mysqlbackup completed OK!Backup successfully completed.To restore it you should use same commands like you do for any other MEB image, but need to provide sbt* options as well:
$./mysqlbackup --backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \
--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt" --backup-dir=/path/to/my/dir image-to-backup-dirThen apply log as usual:
$./mysqlbackup --backup-dir=/path/to/my/dir apply-logThen stop mysqld and finally copy-back:
$./mysqlbackup --defaults-file=path/to/my.cnf --backup-dir=/path/to/my/dir copy-back
Disclaimer. This is only story of one success which can be useful for someone else. MEB is not regularly tested and not guaranteed to work with IBM TSM or any other third-party storage manager.
