Documentation Home
MySQL Enterprise Backup 8.4 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb


11.1 Backing Up to Tape with Oracle Secure Backup

Tape drives are affordable, high-capacity storage devices for backup data. MySQL Enterprise Backup can interface with media management software (MMS) such as Oracle Secure Backup (OSB) to drive MySQL backup and restore jobs. The media management software must support Version 2 or higher of the System Backup to Tape (SBT) API.

On the MySQL Enterprise Backup side, you run the backup job as a single-file backup using the--backup-image parameter, with the prefixsbt: in front of the filename, and optionally pass other--sbt-* parameters tomysqlbackup to control various aspects of the SBT processing. The--sbt-* options are listed inSection 20.9, “Single-File Backup Options”.

On the OSB side, you can schedule MySQL Enterprise Backup jobs by specifying a configurable command that callsmysqlbackup. You control OSB features such as encryption by defining a“storage selector” that applies those features to a particular backup, and passing the name of the storage selector to OSB using the MySQL Enterprise Backup parameter--sbt-database-name=storage_selector.

To back up MySQL data to tape: 

  • Specify the--backup-image=sbt:name parameter ofmysqlbackup to uniquely identify the backup data. Thesbt: prefix sends the backup data to the MMS rather than a local file, and the remainder of the argument value is used as the unique backup name within the MMS.

  • Specify the--sbt-database-name parameter ofmysqlbackup to enable the OSB operator to configure a storage selector for backups from this MySQL source. (This parameter refers to a“storage selector” defined by the OSB operator, not to any MySQL database name.) By default,mysqlbackup supplies a value ofMySQL for this MMS parameter. The argument to this option is limited to 8 bytes.

  • If you have multiple media management programs installed, to select the specific SBT library to use, specify the--sbt-lib-path parameter of themysqlbackup command. If you do not specify the--sbt-lib-path parameter,mysqlbackup uses the normal operating system paths and environment variables to locate the SBT library, which is namedlibobk.so on Linux and Unix systems andORASBT.DLL on Windows systems. When you specify--sbt-lib-path, you can use a different filename for the library in addition to specifying the path.

  • Specify any other product-specific settings that are normally controlled by environment variables using the--sbt-environment option.

Each time an online backup is made to a tape using the SBT API , besides recording the backup in themysql.backup_history and themysql.backup_progress tables, an entry is also made to themysql.backup_sbt_history table on the backed up MySQL instance. That facilitates the management of tape backups by allowing easy look-ups for information on them. The definition of thebackup_sbt_history table is shown below:

mysql> DESCRIBE `backup_sbt_history`;+--------------------+---------------+------+-----+---------------------+----------------+| Field              | Type          | Null | Key | Default             | Extra          |+--------------------+---------------+------+-----+---------------------+----------------+| id                 | int           | NO   | PRI | NULL                | auto_increment || backup_id          | bigint        | NO   |     | NULL                |                || backup_file_name   | varchar(4096) | NO   |     | NULL                |                || file_creation_time | timestamp     | NO   |     | 0000-00-00 00:00:00 |                || file_expiry_time   | timestamp     | NO   |     | 0000-00-00 00:00:00 |                || volume_label       | varchar(64)   | NO   |     | NULL                |                || sbt_error_msg      | varchar(4096) | NO   |     | NULL                |                || sbt_error_code     | int           | NO   |     | NULL                |                |+--------------------+---------------+------+-----+---------------------+----------------+

Here are the descriptions for the fields in the table:

  • id: Auto-increment primary key for the table.

  • backup_id: The backup's ID, which is also recorded in the backup's entries in themysql.backup_history and themysql.backup_progress tables.

  • backup_file_name: The file name provided by the user through the--backup-image=sbt:name option.

  • file_creation_time: Creation date and time for the tape backup.

  • file_expiry_time: Expiration date and time for the tape backup.

  • volume_label: Volume label for the physical medium which contains the tape backup.

  • sbt_error_msg: Error message, when an error occurs while retrieving information for the tape backup.

  • sbt_error_code: Error code, when an error occurs while retrieving information for the tape backup.

Multiple entries, one for each volume label, are created in themysql.backup_sbt_history table, if the backup is split across multiple volumes.

Here are some sample entries in themysql.backup_sbt_history table:

mysql> SELECT * FROM mysql.backup_sbt_history;+----+-------------------+------------------+---------------------+---------------------+-----------------+---------------+----------------+| id | backup_id         | backup_file_name | file_creation_time  | file_expiry_time    | volume_label    | sbt_error_msg | sbt_error_code |+----+-------------------+------------------+---------------------+---------------------+-----------------+---------------+----------------+|  1 | 15921945689894983 | backup_img1.msb  | 2020-06-15 07:16:09 | 2020-06-15 07:16:09 | /sbt_bup_dir    |               |              0 ||  2 | 15921945689894983 | backup_img1.msb  | 2020-06-15 07:16:09 | 2020-06-15 07:16:09 | backup_img1.msb |               |              0 |+----+-------------------+------------------+---------------------+---------------------+-----------------+---------------+----------------+2 rows in set (0.00 sec)

A backup to tape always uses one write thread.

To restore MySQL data from tape: 

  • Specify the--backup-image=sbt:name parameter ofmysqlbackup as part of the restore operation. Use the samename value which was used during the backup. This single parameter retrieves the appropriate data from the appropriate tape device.

  • Optionally use the--sbt-lib-path option, using the same value as for the backup operation.

  • Specify any other product-specific settings that are normally controlled by environment variables using the--sbt-environment option.

For product-specific information about Oracle Secure Backup, seethe Oracle Secure Backup documentation.

Example 11.1 Samplemysqlbackup Commands Using MySQL Enterprise Backup with OracleSecure Backup

# Uses libobk.so or ORASBT.DLL, at standard locations:mysqlbackup --port=3306 --protocol=tcp --user=root --password \  --backup-image=sbt:backup-shoeprod-2011-05-30 \  --backup-dir=/backup backup-to-image# Associates this backup with storage selector 'shoeprod':mysqlbackup --port=3306 --protocol=tcp --user=root --password \  --backup-image=sbt:backup-shoeprod-2011-05-30 \  --sbt-database-name=shoeprod \  --backup-dir=/backup backup-to-image# Uses an alternative SBT library, /opt/Other-MMS.so:mysqlbackup --port=3306 --protocol=tcp --user=root --password \  --backup-image=sbt:backup-shoeprod-2011-05-30 \  --sbt-lib-path=/opt/Other-MMS.so \  --backup-dir=/backup backup-to-image