MySQL Enterprise Backup User's Guide (Version 8.0.17)

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 prefix sbt: in front of the filename, and optionally pass other --sbt-* parameters to mysqlbackup to control various aspects of the SBT processing. The --sbt-* options are listed in Section 18.9, “Single-File Backup Options”.

On the OSB side, you can schedule MySQL Enterprise Backup jobs by specifying a configurable command that calls mysqlbackup. 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: 

Each time an online backup is made to a tape using the SBT API , besides recording the backup in the mysql.backup_history and the mysql.backup_progress tables, an entry is also made to the mysql.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 the backup_sbt_history table is shown below:

mysql> DESCRIBE `backup_sbt_history`;
+--------------------+---------------+------+-----+---------------------+-------+
| Field              | Type          | Null | Key | Default             | Extra |
+--------------------+---------------+------+-----+---------------------+-------+
| backup_id          | bigint(20)    | 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(11)       | NO   |     | NULL                |       |
--------------------------------------------------------------------------------

Here are the descriptions for the fields in the table:

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

Here are some sample entries in the mysql.backup_sbt_history table:

mysql> select * from mysql.backup_sbt_history;
+-------------------+------------------+---------------------+---------------------+--------------+---------------+----------------+
| backup_id         | backup_file_name | file_creation_time  | file_expiry_time    | volume_label | sbt_error_msg | sbt_error_code |
+-------------------+------------------+---------------------+---------------------+--------------+---------------+----------------+
| 14377247250385175 | test1.mbi        | 2015-07-24 09:59:00 | 0000-00-00 00:00:00 | MySQL-000018 |               |              0 |
| 14377259679196369 | test2.mbi        | 2015-07-24 10:19:43 | 0000-00-00 00:00:00 | MySQL-000018 |               |              0 |
| 14377265350728997 | test3.mbi        | 2015-07-24 10:29:10 | 0000-00-00 00:00:00 | MySQL-000018 |               |              0 |
| 14377265350728997 | test3.mbi        | 2015-07-24 10:29:10 | 0000-00-00 00:00:00 | MySQL-000019 |               |              0 |
+-------------------+------------------+---------------------+---------------------+--------------+---------------+----------------+
4 rows in set (0.00 sec)

To restore MySQL data from tape: 

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

Example 11.1 Sample mysqlbackup Commands Using MySQL Enterprise Backup with Oracle Secure 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