Streaming MySQL database backups

Let’s say you want to backup a MySQL database but instead of going with the standard “dump the backup on the same server and send it to the backup server afterwards”, you’d like to save yourself an extra step and directly stream it remotely to the backup server. Or you’re in one of those funny scenarios where “yo database is so big” that you don’t have enough space for a full dump on the same server.

Enter innobackupex and xbstream, from Percona XtraBackup.

If you didn’t do that already, install percona-xtrabackup on both your target and backup servers. On Ubuntu, that would be an easy “apt-get install percona-xtrabackup”.

Now, if you’re an one-liner type of guy, you can just go like this, from your backup server:

ssh BACKUP_USER@MYSQL_SRV "sudo innobackupex --defaults-file=DEFAULTS_FILE \
  --stream=xbstream TEMP_DIR" | xbstream -x -C LOCAL_BACKUP_DIR

So what happens here? In a nutshell, you run innobackupex from your backup server, remotely via ssh on the database server, which streams the backup to STDOUT, as an xbstream stream, and from there back to the backup server, where it gets decoded back to a mysql backup via xbstream utility. MySQL credentials are handled in this example via defaults-file, but you can have many approaches here.

Normally, if you wouldn’t specify TEMP_DIR, innobackupex would make use of your default temp folder (most probably /tmp), so if your tables are small enough / your tmp folder is big enough, you can omit this parameter. Otherwise, make sure you specify a big enough TEMP_DIR.

Please note that the above one-liner is very basic and usually you would want to do at least two other things:

• compress the backup with –compress
• encrypt the backup with –encrypt=AES256 –encrypt-key-file=YOUR_ENCRYPTION_KEY

In both cases, you can specify the number of threads via –compress-threads / –encrypt-threads / –parallel.

As always, make sure you read the fine manual before use.