Percona XtraBackup SST Configuration¶

Percona XtraBackup SST works in two stages:

First it identifies the type of data transfer based on the presence of xtrabackup_ist file on the joiner node.
Then it starts data transfer:
- In case of SST, it empties the data directory except for some files (galera.cache, sst_in_progress, grastate.dat) and then proceeds with SST
- In case of IST, it proceeds as before.

Note

As of Percona XtraDB Cluster 5.7, xtrabackup-v2 is the only XtraBackup SST method.

SST Options¶

The following options specific to SST can be used in my.cnf under [sst].

Note

Non-integer options which have no default value are disabled if not set.
:Match: Yes implies that option should match on donor and joiner nodes.
SST script reads my.cnf when it runs on either donor or joiner node, not during mysqld startup.
SST options must be specified in the main my.cnf file.

streamfmt¶

Parameter	Description
Values:	xbstream, tar
Default:	xbstream
Match:	Yes

Used to specify the Percona XtraBackup streaming format. The recommended value is streamfmt=xbstream. Certain features are not available with tar, for instance: encryption, compression, parallel streaming, streaming incremental backups. For more information about the xbstream format, see The xbstream Binary.

transferfmt¶

Parameter	Description
Values:	`socat`, `nc`
Default:	`socat`
Match:	Yes

Used to specify the data transfer format. The recommended value is the default transferfmt=socat because it allows for socket options, such as transfer buffer sizes. For more information, see socat(1).

Note

Using transferfmt=nc does not support any of the SSL-based encryption modes (values 2, 3, and 4 for the encrypt option). Only encrypt=1 is supported.

tca¶

Example: tca=/etc/ssl/certs/mycert.crt

Used to specify the full path to the certificate authority (CA) file for socat encryption based on OpenSSL.

tcert¶

Example: tcert=/etc/ssl/certs/mycert.pem

Used to specify the full path to the certificate file in PEM format for socat encryption based on OpenSSL.

Note

For more information about tca and tcert, see https://www.dest-unreach.org/socat/doc/socat-openssltunnel.html. The tca is essentially the self-signed certificate in that example, and tcert is the PEM file generated after concatenation of the key and the certificate generated earlier. The names of options were chosen to be compatible with socat parameter names as well as with MySQL’s SSL authentication. For testing you can also download certificates from launchpad.

Note

Irrespective of what is shown in the example, you can use the same .crt and .pem files on all nodes and it will work, since there is no server-client paradigm here, but rather a cluster with homogeneous nodes.

tkey¶

Example: tkey=/etc/ssl/keys/key.pem

Used to specify the full path to the private key in PEM format for socat encryption based on OpenSSL.

encrypt¶

Parameter	Description
Values:	0, 1, 2, 3
Default:	0
Match:	Yes

Used to enable and specify SST encryption mode:

Set encrypt=0 to disable SST encryption. This is the default value.
Set encrypt=1 to perform symmetric SST encryption based on XtraBackup.
Set encrypt=2 to perform SST encryption based on OpenSSL with socat. Ensure that socat is built with OpenSSL: socat -V | grep OPENSSL. This is recommended if your nodes are over WAN and security constraints are higher.
Set encrypt=3 to perform SST encryption based on SSL for just the key and certificate files as implemented in Galera cluster

It does not provide certificate validation. In order to work correctly, paths to the key and certificate files need to be specified as well, for example:

[sst]
encrypt=3
tkey=/etc/mysql/key.pem
tcert=/etc/mysql/cert.pem

Set encrypt=4 for SST encryption with SSL files generated by MySQL. This is the recommended mode.

Considering that you have all three necessary files:

[sst]
encrypt=4
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

Note

All encryption modes can only be used when wsrep_sst_method is set to xtrabackup-v2 (which is the default).

For more information, see Encrypting PXC Traffic.

encrypt-algo¶

Values: AES128, AES192, AES256

Used to specify the SST encryption algorithm. It uses the same values as the --encryption option for XtraBackup (see this document). The encrypt-algo option is considered only if encrypt is set to 1.

sockopt¶

Used to specify key/value pairs of socket options, separated by commas, for example:

[sst]
sockopt="retry=2,interval=3"

The previous example causes socat to try to connect three times (initial attempt and two retries with a 3-second interval between attempts).

Note

For versions of Percona XtraDB Cluster before 5.7.17-29.20, the value must begin with a comma, for example:

[sst]
sockopt=",cipher=AES128"

This option only applies when socat is used (transferfmt=socat). For more information about socket options, see socat (1).

Note

You can also enable SSL based compression with sockopt. This can be used instead of the Percona XtraBackup compress option.

ncsockopt¶

Used to specify socket options for the netcat transfer format (transferfmt=nc).

progress¶

Values: 1, path/to/file

Used to specify where to write SST progress. If set to 1, it writes to MySQL stderr. Alternatively, you can specify the full path to a file. If this is a FIFO, it needs to exist and be open on reader end before itself, otherwise wsrep_sst_xtrabackup will block indefinitely.

Note

Value of 0 is not valid.

rebuild¶

Parameter	Description
Values:	0, 1
Default:	0

Used to enable rebuilding of index on joiner node. This is independent of compaction, though compaction enables it. Rebuild of indexes may be used as an optimization.

Note

#1192834 affects this option.

time¶

Parameter	Description
Values:	0, 1
Default:	0

Enabling this option instruments key stages of backup and restore in SST.

rlimit¶

Example: rlimit=128k

Used to set a a ratelimit in bytes. Add a suffix (k, m, g, t) to specify units. For example, 128k is 128 kilobytes. For more information, see pv(1).

Note

Rate is limited on donor node. The rationale behind this is to not allow SST to saturate the donor’s regular cluster operations or to limit the rate for other purposes.

use_extra¶

Parameter	Description
Values:	0, 1
Default:	0

Used to force SST to use the thread pool’s extra_port. Make sure that thread pool is enabled and the extra_port option is set in my.cnf before you enable this option.

cpat¶

Used to define the files that need to be retained in the datadir before running SST, so that the state of the other node can be restored cleanly. For example:

[sst]
cpat='.*galera\.cache$\|.*sst_in_progress$\|.*grastate\.dat$\|.*\.err$\|.*\.log$\|.*RPM_UPGRADE_MARKER$\|.*RPM_UPGRADE_HISTORY$\|.*\.xyz$'

Note

This option can only be used when wsrep_sst_method is set to xtrabackup-v2 (which is the default value).

compressor¶

Parameter	Description
Default:	not set (disabled)
Example:	compressor=’gzip’

decompressor¶

Parameter	Description
Default:	not set (disabled)
Example:	decompressor=’gzip -dc’

Two previous options enable stream-based compression/decompression. When these options are set, compression/decompression is performed on stream, in contrast to performing decompression after streaming to disk, involving additional I/O. This saves a lot of I/O (up to twice less I/O on joiner node).

You can use any compression utility which works on stream: gzip, pigz (which is recommended because it is multi-threaded), etc. Compressor has to be set on donor node and decompressor on joiner node (although you can set them vice-versa for configuration homogeneity, it won’t affect that particular SST). To use XtraBackup based compression as before, set compress under [xtrabackup]. Having both enabled won’t cause any failure (although you will be wasting CPU cycles).

inno-backup-opts¶

inno-apply-opts¶

inno-move-opts¶

Parameter	Description
Default:	Empty
Type:	Quoted String

This group of options is used to pass XtraBackup options for backup, apply, and move stages. The SST script doesn’t alter, tweak, or optimize these options.

Note

Although these options are related to XtraBackup SST, they cannot be specified in my.cnf, because they are for passing innobackupex options.

sst-initial-timeout¶

Parameter	Description
Default:	100
Unit:	seconds

This option is used to configure initial timeout (in seconds) to receive the first packet via SST. This has been implemented, so that if the donor node fails somewhere in the process, the joiner node will not hang up and wait forever.

By default, the joiner node will not wait for more than 100 seconds to get a donor node. The default should be sufficient, however, it is configurable, so you can set it appropriately for your cluster. To disable initial SST timeout, set sst-initial-timeout=0.

Note

If you are using wsrep_sst_donor, and you want the joiner node to strictly wait for donors listed in the variable and not fall back (that is, without a terminating comma at the end), and there is a possibility of all nodes in that variable to be unavailable, disable initial SST timeout or set it to a higher value (maximum threshold that you want the joiner node to wait). You can also disable this option (or set it to a higher value) if you believe all other nodes in the cluster can potentially become unavailable at any point in time (mostly in small clusters) or there is a high network latency or network disturbance (which can cause donor selection to take longer than 100 seconds).

sst-idle-timeout¶

Parameter	Description
Version:	Introducted in `5.7.34-31.51`
Default:	120
Unit:	seconds

This option configures the time the SST operation waits on the joiner to receive more data. The size of the joiner’s sst directory is checked for the amount of data received. For example, the directory has received 50MB of data. The operation checks the data size again after the 120 seconds, the default value, has elapsed. If the data size is still 50MB, this operation is aborted. If the data has increased, the operation continues.

An example of setting the option:

[sst]
sst-idle-timeout=0

tmpdir¶

Parameter	Description
Version:	Introducted in `5.7.17-29.20`
Default:	Empty
Unit:	/path/to/tmp/dir

This option specifies the location for storing the temporary file on a donor node where the transaction log is stored before streaming or copying it to a remote host.

Note

Starting from Percona XtraDB Cluster 5.7.20-29.24 this option can be used on joiner node also, to specify non-default location to receive temporary SST files. This location must be large enough to hold the contents of the entire database. If tmpdir is empty then default location datadir/.sst will be used.

The tmpdir option can be set in the following my.cnf groups:

[sst] is the primary location (others are ignored)
[xtrabackup] is the secondary location (if not specified under [sst])
[mysqld] is used if it is not specified in either of the above

wsrep_debug

Specifies whether additional debugging output for the database server error log should be enabled. Disabled by default.

This option can be set in the following my.cnf groups:

Under [mysqld] it enables debug logging for mysqld and the SST script
Under [sst] it enables debug logging for the SST script only

encrypt_threads¶

Parameter	Description
Version:	Introducted in `5.7.19-29.22`
Default:	`4`

Specifies the number of threads that XtraBackup should use for encrypting data (when encrypt=1). The value is passed using the --encrypt-threads option in XtraBackup.

This option affects only SST with XtraBackup and should be specified under the [sst] group.

backup_threads¶

Parameter	Description
Version:	Introducted in `5.7.19-29.22`
Default:	`4`

Specifies the number of threads that XtraBackup should use to create backups. See the --parallel option in XtraBackup.

This option affects only SST with XtraBackup and should be specified under the [sst] group.

XtraBackup SST Dependencies¶

Each suppored version of Percona XtraDB Cluster is tested against a specific version of Percona XtraBackup:

Percona XtraDB Cluster 5.6 requires Percona XtraBackup 2.3
Percona XtraDB Cluster 5.7 requires Percona XtraBackup 2.4
Percona XtraDB Cluster 8.0 requires Percona XtraBackup 8.0

Other combinations are not guaranteed to work.

The following are optional dependencies of Percona XtraDB Cluster introduced by wsrep_sst_xtrabackup-v2 (except for obvious and direct dependencies):

qpress for decompression. It is an optional dependency of Percona XtraBackup and it is available in our software repositories.
my_print_defaults to extract values from my.cnf. Provided by the server package.
openbsd-netcat or socat for transfer. socat is a direct dependency of Percona XtraDB Cluster and it is the default.
xbstream or tar for streaming. xbstream is the default.
pv is required for progress and rlimit.
mkfifo is required for progress. Provided by coreutils.
mktemp is required. Provided by coreutils.
which is required.

XtraBackup-based Encryption¶

This is enabled when encrypt is set to 1 under [sst] in my.cnf. However, due to bug #1190335, it will also be enabled when you specify any of the following options under [xtrabackup] in my.cnf:

encrypt
encrypt-key
encrypt-key-file

There is no way to disable encryption from innobackupex if any of the above are in my.cnf under [xtrabackup]. For that reason, consider the following scenarios:

If you want to use XtraBackup-based encryption for SST but not otherwise, use encrypt=1 under [sst] and provide the above XtraBackup encryption options under [sst]. Details of those options can be found here.
If you want to use XtraBackup-based encryption always, use encrypt=1 under [sst] and have the above XtraBackup encryption options either under [sst] or [xtrabackup].
If you don’t want to use XtraBackup-based encryption for SST, but want it otherwise, use encrypt=0 or encrypt=2 and do NOT provide any XtraBackup encryption options under [xtrabackup]. You can still have them under [sst] though. You will need to provide those options on innobackupex command line then.
If you don’t want to use XtraBackup-based encryption at all (or only the OpenSSL-based for SST with encrypt=2), don’t provide any XtraBackup encryption options in my.cnf.

Note

The encrypt option under [sst] is different from the one under [xtrabackup]. The former is for disabling/changing encryption mode, while the latter is to provide an encryption algorithm. To disambiguate, if you need to provide the latter under [sst] (for example, in cases 1 and 2 above), it should be specified as encrypt-algo.

Warning

An implication of the above is that if you specify any of the XtraBackup encryption options, and encrypt=0 under [sst], it will still be encrypted and SST will fail. Look at case 3 above for resolution.

Warning

It is insecure to use the encrypt-key option when performing an SST with xtrabackup-v2 and encrypt=1 (using wsrep_sst_method='xtrabackup-v2' under [mysqld] and encrypt=1 under [sst]) since the key will appear on the command line, and will be visible via ps. Therefore it is strongly recommended to place the key into a file and use the encrypt-key-file option.

Memory Allocation¶

The amount of memory for XtraBackup is defined by the --use-memory option. You can pass it using the inno-apply-opts option under [sst] as follows:

[sst]
inno-apply-opts="--use-memory=500M"

If it is not specified, the use-memory option under [xtrabackup] will be used:

[xtrabackup]
use-memory=32M

If neither of the above are specified, the size of the InnoDB memory buffer will be used:

[mysqld]
innodb_buffer_pool_size=24M