Percona XtraBackup SST configuration¶
Key takeaways
-
Two-Stage Process:
The XtraBackup SST process operates in two stages. First, it identifies the type of data transfer (SST or IST) based on the presence of thextrabackup_ist
file on the joiner node. Then, it proceeds with the appropriate data transfer method. For SST, it clears the data directory (except for specific files likegalera.cache
andgrastate.dat
) before starting the transfer. -
Configuration Options:
The SST process can be customized using various options in themy.cnf
file under the[sst]
section. Key options includestreamfmt
(to specify the streaming format, such asxbstream
),transferfmt
(to define the data transfer format, likesocat
), and encryption settings (ssl-ca
,ssl-cert
, andssl-key
) for secure transfers. -
Encryption and Security:
XtraBackup SST supports encryption to secure data during transfer. Theencrypt
option in the configuration allows users to enable and specify the encryption mode. By default, encryption is disabled, but it can be configured for enhanced security using SSL-based certificates.
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.
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 duringmysqld
startup. -
SST options must be specified in the main
my.cnf
file.
streamfmt¶
Parameter | Description |
---|---|
Values: | xbstream |
Default: | xbstream |
Match: | Yes |
Used to specify the Percona XtraBackup streaming format. The only option is the xbstream
format. SST fails and generates an error when another format, such as tar
, is used.
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 the SSL-based encryption mode (value 4
for the encrypt
option).
ssl-ca¶
Example: ssl-ca=/etc/ssl/certs/mycert.crt
Specifies the absolute path to the certificate authority (CA) file
for socat
encryption based on OpenSSL.
ssl-cert¶
Example: ssl-cert=/etc/ssl/certs/mycert.pem
Specifies the full path to the certificate file in the PEM format
for socat
encryption based on OpenSSL.
Note
For more information about ssl-ca
and ssl-cert
,
see https://www.dest-unreach.org/socat/doc/socat-openssltunnel.html.
The ssl-ca
is essentially a self-signed certificate in that example,
and ssl-cert
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.
ssl-key¶
Example: ssl-key=/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, 4 |
Default: | 4 |
Match: | Yes |
Enables SST encryption mode in Percona XtraBackup:
-
Set
encrypt=0
to disable SST encryption. -
Set
encrypt=4
for SST encryption with SSL files generated by MySQL. This is the recommended value.
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
For more information, see Encrypting PXC Traffic.
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).
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.
cpat¶
Default: '.\*\\.pem$\\|.\*init\\.ok$\\|.\*galera\\.cache$\\|.\*sst_in_progress$\\|.\*\\.sst$\\|.\*gvwstate\\.dat$\\|.\*grastate\\.dat$\\|.\*\\.err$\\|.\*\\.log$\\|.\*RPM_UPGRADE_MARKER$\\|.\*RPM_UPGRADE_HISTORY$'
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=’zstd -T0 -2’ |
decompressor¶
Parameter | Description |
---|---|
Default: | not set (disabled) |
Example: | decompressor=’zstd -T0 -dc’ |
Stream-based compression and decompression are performed on the stream, in contrast to performing decompression after streaming to disk, which involves additional I/O. The savings are considerable, up to half the I/O on the JOINER node.
You can use any compression utility which works on stream:
gzip
, pigz
, zstd
, and others. The pigz
or zstd
options are multi-threaded. At a minimum, the compressor must be set on the DONOR and the decompressor on JOINER.
You must install the related binaries, otherwise SST aborts.
compressor=’pigz’ decompressor=’pigz -dc’
compressor=’gzip’ decompressor=’gzip -dc’
To revert to the XtraBackup-based compression,
set compress
under [xtrabackup]
. You can define both the compressor and the decompressor, although you will be wasting CPU cycles.
[xtrabackup]
compress
-- compact has led to some crashes
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 |
---|---|
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 rechecks the data size after the default value, 120 seconds, 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 |
---|---|
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
This option can be used on joiner node 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
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 formysqld
and the SST script -
Under
[sst]
it enables debug logging for the SST script only
encrypt_threads¶
Parameter | Description |
---|---|
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 |
---|---|
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 frommy.cnf
. Provided by the server package. -
openbsd-netcat
orsocat
for transfer.socat
is a direct dependency of Percona XtraDB Cluster and it is the default. -
xbstream
ortar
for streaming.xbstream
is the default. -
pv
is required forprogress
andrlimit
. -
mkfifo
is required forprogress
. Provided bycoreutils
. -
mktemp
is required. Provided bycoreutils
. -
which
is required.
XtraBackup-based encryption¶
Settings related to XtraBackup-based Encryption are no longer allowed in PXC 8.4 when used for SST. If it is detected that XtraBackup-based Encryption is enabled, PXC will produce an error.
The XtraBackup-based Encryption is enabled when you specify any of the following options
under [xtrabackup]
in my.cnf
:
-
encrypt
-
encrypt-key
-
encrypt-key-file
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