3.3.5 Making a Single-File Backup Streaming the Backup Data to Another Device or Server Backing Up to Tape

To avoid a large number of backup files to track and keep safe, and to simplify moving backup data around, the mysqlbackup command can create a backup in a single-file format, pack an existing backup into a single file, unpack the single file back to the original backup directory structure, list the contents of a single-file backup, verify the contents of a single-file backup against embedded checksums, or extract a single file or directory tree. For the syntax of the relevant mysqlbackup options, see Section 4.1.10, “Single-File Backup Options”.

Because the single-file backup can be streamed or piped to another process, such as a tape backup or a command such as scp, you can use this technique to put the backup on another storage device or server without significant storage overhead on the original database server. (During preparation of the single-file backup, some small work files are prepared temporarily inside the specified backup directory.)

To create a single-file backup, specify the mysqlbackup option backup-to-image. All the original data files must be under a single directory, rather than spread across different paths. Specify the same path for the datadir, innodb_log_group_home_dir, and innodb_data_home_dir configuration options.

Example 3.4 Single-File Backup to Absolute Path

This command creates a single backup image in the given absolute path. It still requires --backup-dir, which is used to hold temporary output, status, and metadata files.

mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image

Example 3.5 Single-File Backup to Relative Path

This command specifies --backup-image with a relative path underneath the backup directory. The resulting single-file backup is created as /backups/sales.mbi.

mysqlbackup --backup-image=sales.mbi --backup-dir=/backups backup-to-image

Example 3.6 Single-File Backup to Standard Output

The following command dumps the backup output to standard output. Again, the --backup-dir directory specified in my.cnf is used as a temporary directory.

mysqlbackup --backup-dir=/backups --backup-image=- backup-to-image > /backup/mybackup.mbi

Example 3.7 Convert Existing Backup Directory to Single Image

The backup-dir directory specified in my.cnf is bundled into the /backup/my.mbi file. The directory can contain anything, not necessarily a backup produced by MySQL Enterprise Backup.

mysqlbackup --backup-image=/backup/my.mbi --backup-dir=/var/mysql/backup backup-dir-to-image

Example 3.8 Extract Existing Image to Backup Directory

The image contents are unpacked into backup-dir.

mysqlbackup --backup-dir=/var/backup --backup-image=/backup/my.mbi image-to-backup-dir

Example 3.9 List Single-File Backup Contents

The image contents are listed with each line indicating a file or directory entry.

mysqlbackup --backup-image=/backup/my.mbi list-image

Example 3.10 Extract Single-File Backup into Current Directory

The following command extracts all contents from a single-file backup into the current working directory.

mysqlbackup --backup-image=/var/my.mbi extract

Example 3.11 Extract Single-File Backup into a Backup Directory

This command behaves like the image-to-backup-dir option, by extracting all contents of a single-file backup into the --backup-dir directory.

mysqlbackup --backup-image=/var/my.mbi --backup-dir=/var/backup extract

Example 3.12 Selective Extract of Single File

The following command extracts the single file meta/comments.txt into the local path./meta/comments.txt.

mysqlbackup --backup-image=/var/my.mbi \
  --src-entry=meta/comments.txt extract

The following command extracts the meta/comments.txt file into a specified path /tmp/mycomments.txt by using the --dst-entry option.

mysqlbackup --backup-image=/var/my.mbi \
  --src-entry=meta/comments.txt \
  --dst-entry=/tmp/mycomments.txt extract

The following command dumps the contents of meta/comments.txt (inside a single-file backup) to standard output.

mysqlbackup --backup-image=/var/my.mbi --src-entry=meta/comments.txt --dst-entry=- extract

Example 3.13 Selective Extract of Single Directory

The following command extracts a single directory meta into a local file system path ./meta. Extracting a directory extracts all its contents, including any subdirectories.

mysqlbackup --backup-image=/backup/my.mbi --src-entry=meta extract

The following command extracts all meta directory contents (all its files and subdirectories) into the directory /tmp/my-meta.

mysqlbackup --backup-image=/backup/my.mbi --src-entry=meta \
  --dst-entry=/tmp/my-meta extract

Example 3.14 Checking a Single-File Backup for Corruption Problems

For an example showing the output of successful and unsuccesful checks, see Section C.4, “Validating a Single-File Backup Image”.

Example 3.15 Dealing with Absolute Path Names

Since absolute pathnames are extracted to the same paths in local system, it could be a problem if you do not have write permission for that path. You can remap absolute paths as follows:

mysqlbackup --backup-image=/backup/my.mbi --src-entry=/ --dst-entry=/myroot extract
mysqlbackup --backup-image=/backup/my.mbi --src-entry=. extract

The first command extracts all absolute paths to /myroot directory in the local system. The second command extracts all relative paths to the current directory.