3.3.5 Making a Single-File Backup

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 5.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, 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, use the mysqlbackup subcommand backup-to-image.

Example 3.3 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.4 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.5 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.6 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.7 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.8 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.9 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.10 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.11 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.12 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.13 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.