Are you looking for a faster way to stream a backup from one db server to another?

Maybe you are rebuilding a replica from the master after someone wrote to the slave and now the data is out of sync.

I had a look at what was out there in terms of articles about streaming backups.

Most of them got part of the way but clearly haven’t had people asking in chat “are we there yet?” over and over, wondering when their replicas will be back so they can direct the read traffic at them.
The key here is to use the parallel options that come with mariabackup and use pigz to compress and decompress in parallel.

Do you want speed?
Do you want to saturate the 10Gigabit bonded link or InfiniBand (I wish…) ?
Do you have spare CPUs to use?

WARNING:

• This command assumes the destination directory is empty. 
• The command will 8 CPUs (4 for mariabackup and 4 for pigz) on the source
• The command can use up to 8 CPUs (4 for pigz to decompress, 4 for mbstream)
• Don’t run pigz without -p option. As by default it will use all CPUs available.

The command:

mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | pigz -p 4 | ssh -q replica -t "pigz -dc -p 4 | mbstream --directory=/var/lib/mysql -x --parallel=4"

Explanation of the command:

1. This command will take a streaming backup (using xbstream) in parallel from the database in /var/lib/mysql.
2. It will write output the backup.log (just in case you need to debug…)
3. Then it will pipe that toq pigz (parellel gzip) which will compress in parallel.
4. This stream of compressed xbstream will be sent encrypted via ssh to host called replica.
5. On the replica, the compressed xbstream will be uncompressed by pigz, then piped thru mbstream into the /var/lib/mysql directory.

Next steps:

1. Prepare the backup using the appropriate amount of RAM.

   mariabackup --prepare --use-memory=16G --target-dir=/var/lib/mysql/

2. Change the ownership of the destination datadir

   chown -R mysql:mysql /var/lib/mysql

3. Start the db

   systemctl start mariadb

4. Look at xtrabackup_slave_info to get the slave info

   cat /var/lib/mysql/xtrabackup_slave_info

5. Change the master using CHANGE MASTER using that info.

Until next time.