Documentation Home
MySQL Enterprise Backup 3.9 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.9Mb
PDF (A4) - 0.8Mb Streaming the Backup Data to Another Device or Server

To limit the storage overhead on the database server, you can transfer the backup data to a different server without ever storing it locally. The primary MySQL Enterprise Backup feature related to streaming is the single-image backup. To send the single-file backup to standard output, specify by the mysqlbackup option backup-to-image with no --backup-image option. (You can also specify --backup-image=- to make it obvious that the data is sent to stdout.) To stream the data, you use the single-file backup in combination with operating system features such as pipes, ssh/scp, and so on that can take input from standard output and create an equivalent file on a remote system. You can either store the single-file backup directly on the remote system, or invoke the mysqlbackup command with the image-to-backup-dir option on the other end to reproduce the directory structure of a regular backup.

Example 3.16 Single-File Backup to a Remote Host

The following command streams the backup output to a remote host, where it is saved directly to a tape device. --backup-dir=/tmp designates the directory for storing temporary work files rather than the final output file. For simplicity, all the connection and other necessary options are assumed to be taken from the default configuration file. For the operation to run on the remote system, substitute the combination of command, device, and so on that you use as part of your normal archiving procedure, such as dd or tar.

mysqlbackup --backup-image=- --backup-dir=/tmp backup-to-image | \
ssh user@host command arg1 arg2...

User Comments
  Posted by Stefan Lasiewski on August 4, 2015
The example above stating `ssh user@host command arg1 arg2` doesn't illustrate the power of this feature. There are many options here.

Note that streaming backups works well for smaller databases, but streaming larger amounts of data (like 300GB - 1 TB or more) over STDIO is harder to do as you'll start hitting bottlenecks and might need to start researching Linux kernel tunable (sysctls, etc.).

Here are some simple examples which show how to stream the backup. From here, you can move onto more advanced functions. You could replace the ssh stream with a command like FTP, or an STDIN stream to a ZFS host (complete with RAIDZ2, compression and deduplication!).

* First, as a simple example of backup to a stream, this shows how to backup the stream to a local file:

mysqlbackup --backup-image=- --backup-dir=/tmp/mysqlbackup.$$ backup-to-image > /tmp/mysqlbackup.img

* Stream the backup over SSH to a file on the remote system, using `cat`:

mysqlbackup --backup-image=- --backup-dir=/tmp/mysqlbackup.$$ backup-to-image | \
ssh user@host 'cat > mysqlbackup.img'

* Validate the remote backup image:

ssh user@host 'cat mysqlbackup.img' | mysqlbackup validate --backup-image=-

* Restore from the remote backup image:

ssh user@host 'cat mysqlbackup.img' | su - mysql -c "/opt/mysql/meb-3.12/bin/mysqlbackup --backup-image=- --backup-dir=/tmp/mysqlbackup.$$ copy-back-and-apply-log"

Sign Up Login You must be logged in to post a comment.