To avoid having a large number of backup files to keep track, store, and transport,mysqlbackup conveniently creates backups in a single-file format. It can also pack an existing backup directory into a single file, unpack the single file back to a backup directory, list the contents of a single-file backup, verify the contents of a single-file backup against embedded checksums, or extract a single file into a directory tree. For the syntax of the relevantmysqlbackup options, seeSection 20.9, “Single-File Backup Options”.
Advanced: Whilemysqlbackup can also create a directory backup (see description for thebackup command for details) instead of a single-file backup, the single-file format is preferable in most cases: a single-file backup is easier to handle and store, and certain functions ofmysqlbackup are not supported for directory backups—for example, backup to cloud and backup to tape using the System Backup to Tape (SBT) API. Throughout the manual, directory backup is mostly treated as an advanced topic, and information and examples for directory backups are marked with theAdvanced tag, like this paragraph.
Because the single-file backup can be streamed or piped to another process such as a tape backup or a command, you can use the technique to put the backup onto another storage device or server and avoid significant storage overhead on the original database server.
To create a single-file backup, use thebackup-to-image command. The following examples illustrate how to perform a single-file backup and other related operations.
Example 4.1 Single-File Backup to Absolute Path
This command creates a single backup image on the given absolute path. It still requires--backup-dir, which is used to hold temporary output, status, and metadata files.
mysqlbackup --defaults-file=/home/dbadmin/my.cnf --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-imageExample 4.2 Single-File Backup to Relative Path
When a relative path instead of an absolute path was supplied with the--backup-image option, the path is taken to be relative to thebackup directory. Therefore, in this example, the resulting single-file backup is created as/backups/sales.mbi.
mysqlbackup --defaults-file=/home/dbadmin/my.cnf --backup-image=sales.mbi --backup-dir=/backups backup-to-imageExample 4.3 Single-File Backup to Standard Output
The following command dumps the backup output to standard output. Again, the folder specified with the--backup-dir option is used as a temporary directory.
mysqlbackup --defaults-file=/home/dbadmin/my.cnf --backup-dir=/backups --backup-image=- backup-to-image > /backup/mybackup.mbiExample 4.4 Convert Existing Backup Directory to Single Image
Thebackup-dir directory is bundled into the/backup/my.mbi file.
mysqlbackup --backup-image=/backup/my.mbi --backup-dir=/var/mysql/backup backup-dir-to-imageExample 4.5 Extract Existing Image to Backup Directory
The image contents are unpacked intobackup-dir.
mysqlbackup --backup-dir=/var/backup --backup-image=/backup/my.mbi image-to-backup-dirExample 4.6 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-imageExample 4.7 Validate a Single-File Backup
The following command verifies that the single-file backup is not corrupted, truncated, or damaged by validating the checksum value for each data page in the backup.
mysqlbackup --backup-image=/logs/fullimage.mi validateExample 4.8 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 extractExample 4.9 Extract Single-File Backup into a Backup Directory
This command extracts all contents of a single-file backup into the directory specified with the--backup-dir option.
mysqlbackup --backup-image=/var/my.mbi --backup-dir=/var/backup extractExample 4.10 Selective Extract of Single File
The following command extracts the single filemeta/comments.txt from the backup imagemy.mbi into the local path./meta/comments.txt.
mysqlbackup --backup-image=/var/my.mbi \ --src-entry=meta/comments.txt extract The following command extracts themeta/comments.txt file from the backup imagemy.mbi 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 ofmeta/comments.txt (which is inside the single-file backupmy.mbi) to standard output.
mysqlbackup --backup-image=/var/my.mbi --src-entry=meta/comments.txt --dst-entry=- extractExample 4.11 Selective Extract of Single Directory
The following command extracts a single directorymeta from the backup imagemy.mbi into a local file system path./meta. All contents in themeta directory are extracted, including any subdirectories. (Notice the slash (/) at the end of the valuemeta/ for--src-entry, without which all files or folders containing the stringmeta in their pathnames will be extracted.)
mysqlbackup --backup-image=/backup/my.mbi --src-entry=meta/ extractExample 4.12 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 extractmysqlbackup --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.