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 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
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
innodb_data_home_dir configuration options.
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
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
my.cnf is used as a
mysqlbackup --backup-dir=/backups --backup-image=- backup-to-image > /backup/mybackup.mbi
Example 3.6 Convert Existing Backup Directory to Single Image
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
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
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
mysqlbackup --backup-image=/var/my.mbi \ --src-entry=meta/comments.txt extract
The following command extracts the
meta/comments.txt file into a specified
/tmp/mycomments.txt by using the
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
directory contents (all its files and subdirectories) into the
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