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, 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 to be saved under the file name my_backup.img (--backup-dir=/tmp designates the directory for storing temporary files rather than the final output file):

mysqlbackup --defaults-file=~/my_backup.cnf --backup-image=- --backup-dir=/tmp backup-to-image | \
  ssh <user name>@<remote host name> 'cat > ~/backups/my_backup.img'

For simplicity, all the connection and other necessary options are assumed to be specified in the default configuration file. ssh can be substituted with another communication protocol like ftp, and cat can be substituted with another command (for example, dd or tar for normal archiving).

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=. --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'