Documentation Home
MySQL Enterprise Backup 4.0 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.1Mb
PDF (A4) - 1.2Mb
EPUB - 231.8Kb
HTML Download (TGZ) - 179.5Kb
HTML Download (Zip) - 210.9Kb


4.3.1.1 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. You can achieve that with a single-file backup. To send the single-file backup to standard output, use the mysqlbackup command backup-to-image without specifying the --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, which take the 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 mysqlbackup with the copy-back-and-apply-log command on the other end to restore the backup to a remote MySQL server.

Example 4.13 Single-File Backup to a Remote Host

The following command streams the backup as a single-file output to a remote host, where it may be saved directly to a tape device. --backup-dir=/tmp designates the directory for storing temporary files rather than the final output file.

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

For simplicity, all the connection and other necessary options are assumed to be specified in the default configuration file. To have the desired operations 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.


Example 4.14 Single-file Backup to a Remote MySQL Server

The following command streams the backup as a single backup file to be restored on a remote MySQL server:


mysqlbackup  --backup-dir=backup --backup-image=-  --compress backup-to-image | \
ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_tmp --datadir=/data \
--innodb_log_group_home_dir=. \
--innodb_log_files_in_group=<innodb_log_files_in_group_of_backedup_server> \
 --innodb_log_file_size=<innodb_log_file_size_of_backedup_server> \ 
--innodb_data_file_path=<innodb_data_file_path_of_backedup_server> \ 
--uncompress --backup-image=- copy-back-and-apply-log'

Example 4.15 Stream a Backup Directory to a Remote MySQL Server

The following command streams a backup directory as a single backup file to be restored on a remote MySQL server:

mysqlbackup --backup-image=- --backup-dir=/path/to/my/backup backup-dir-to-image | \
ssh <user name>@<remote host name> \ 
'mysqlbackup --backup-dir=backup_tmp --datadir=/data --backup-image=- copy-back-and-apply-log'


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.