3.3.5.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. 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.14 Single-File Backup to a Remote Host

The following command streams the backup output to a remote host, where it may be saved directly to a tape device. --backup-dir=/tmp designates the directory for storing temporary work 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 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.


Example 3.15 Single-file Backup to a Remote MySQL Server

The following command streams a single-file backup to 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'