In this blog post, I will be showing you the commands that we can use to do streaming backup using mbstream, and how to redirect the stream to a slave , which can be useful especially when you have insufficient disk space in the Master server to hold multiple copies of backup images.
To redirect backup stream to a slave server, we use the socat utility.
Socat stands for Socket Cat. It is a relay for bidirectional data transfer between two independent data channels.
You can install socat using the command below
yum install -y socat
This is the first command that we will execute in Slave.
mkdir -p /mariadb/backup/rep/full_backup_`date +%Y%m%d` BKPREPDIR=/mariadb/backup/rep/full_backup_`date +%Y%m%d` socat -d -d TCP4-LISTEN:9999 STDOUT | ssh mysql@<slave_ip> \ "mbstream --directory=$BKPREPDIR -x"
The following is the breakdown of the command.
After the socat keyword, the next part of the command is the option part. The -d -d option prints fatal, error, warning, and notice messages.
Followed by the standard out stream keyword.
The next part of the command is the address type, option, and port.
Then after a pipe, the next command is the ssh connection to Slave. The port in ssh connection string is optional.
Now, Socat will listen on port 9999, and whatever it gets on port 9999, it will stream it to slave server, and to the directory that we specify.
When we execute this command in the slave server, this is what it should look like.
Below is the same command, but without the ssh connection port.
The following is the next command to be executed in the Master.
DB_ETC=/mariadb/bin/etc
USER=YourUser
PASS=YourPassword
CONNECTION_STRING="--user=$USER --password=$PASS"
DEFAULT_FILES=${DB_ETC}/my.cnf
mariabackup --defaults-file=${DEFAULT_FILES} ${CONNECTION_STRING} \
--backup --compress --stream=mbstream --parallel=4 \
--compress-threads=12 | socat - TCP4:<slave_ip>:9999
The –compress-threads option defines the number of worker threads to use in compression. It can be used together with –parallel option. In the example above, we use –parallel=4, and –compress-threads=12. This means that it will create 4 I/O threads to read the data; then pipe it to 12 threads for compression.
When we execute the mariabackup command in the Master, below is what we will observe in slave. We will see that it is starting to do data transfer.
When the backup finishes, we will see that it exits with status 0.
Cheers!
