Physical State Snapshot¶
There are two back-end methods available for Physical State Snapshots: rsync and xtrabackup.
Starting with version 8.0.22 also clone method is available for Galera Cluster for MySQL
The Physical State Transfer Method has the following advantages:
- These transfers physically copy the data from one node to the disk of the other, and as such do not need to interact with the database server at either end.
- These transfers do not require the database to be in working condition, as the Donor Node overwrites what was previously on the joining node disk.
- These transfers are faster.
The Physical State Transfer Method has the following disadvantages:
These transfers require the joining node to have the same data directory layout and the same storage engine configuration as the donor node. For example, you must use the same file-per-table, compression, log file size and similar settings for InnoDB.
These transfers are not accepted by servers with initialized storage engines.
What this means is that when your node requires a state snapshot transfer, the database server must restart to apply the changes. The database server remains inaccessible to the client until the state snapshot transfer is complete, since it cannot perform authentication without the storage engines.
rsync
The fastest back-end method for State Snapshot Transfers is rsync. It carries all the advantages and disadvantages of of the Physical Snapshot Transfer. While it does block the donor node during transfer, rsync does not require database configuration or root access, which makes it easier to configure.
When using terabyte-scale databases, rsync is considerably faster, (1.5 to 2 times faster), than xtrabackup. This translates to a reduction in transfer times by several hours.
rsync also features the rsync-wan modification, which engages the rsync delta transfer algorithm. However, given that this makes it more I/O intensive, you should only use it when the network throughput is the bottleneck, which is usually the case in WAN deployments.
Note
The most common issue encountered with this method is due to incompatibilities between the various versions of rsync on the donor and joining nodes.
The rsync script runs on both donor and joining nodes. On the joiner, it starts rsync in server-mode and waits for a connection from the donor. On the donor, it starts rsync in client-mode and sends the contents of the data directory to the joining node.
wsrep_sst_method = rsync
For more information about rsync, see the rsync Documentation.
xtrabackup
The most popular back-end method for State Snapshot Transfers is xtrabackup. It carries all the advantages and disadvantages of a Physical State Snapshot, but is virtually non-blocking on the donor node.
xtrabackup only blocks the donor for the short period of time it takes to copy the MyISAM tables, (for instance, the system tables). If these tables are small, the blocking time remains very short. However, this comes at the cost of speed: a state snapshot transfer that uses xtrabackup can be considerably slower than one that uses rsync.
Given that xtrabackup copies a large amount of data in the shortest possible time, it may also noticeably degrade donor performance.
Note
The most common issue encountered with this method is due to its configuration. xtrabackup requires that you set certain options in the configuration file, which means having local root access to the donor server.
[mysqld]
wsrep_sst_auth = <SST user>:<SST password>
wsrep_sst_method = xtrabackup-v2
datadir = /path/to/datadir
Minimal setup for the xtrabackup SST user:
mysql> CREATE USER '<SST user>'@'localhost' IDENTIFIED BY '<SST password>';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD ON *.* TO '<SST user>'@'localhost';
mysql> GRANT SELECT ON performance_schema.keyring_component_status TO '<SST user>'@'localhost' ;
mysql> GRANT SELECT ON performance_schema.log_status TO '<SST user>'@'localhost' ;
For more information on xtrabackup, see the Percona XtraBackup User Manual and XtraBackup SST Configuration.
clone
Starting with version 8.0.22 clone SST method is available for Galera
Cluster for MySQL. It is based on the native MySQL clone plugin. As of 8.4.2, it is the default value for SST methods. It
proved to be much faster than xtrabackup, however it will block Donor
node on DDL execution if that happens during the transfer.
As of MySQL-wsrep 8.0.27-26.9, progress reporting is also available for the clone SST method. See also Scriptable State Snapshot Transfers.
Basic configuration for clone SST on Joiner:
[mysqld]
wsrep_sst_method=clone
Basic server configuration for clone SST on Donor:
[mysqld]
wsrep_sst_auth=<SST user>:<SST password>
Minimal setup for the clone SST user:
mysql> CREATE USER '<SST user>'@'localhost' IDENTIFIED BY '<SST password>';
mysql> GRANT CREATE USER, SUPER ON *.* TO '<SST user>'@'localhost';
mysql> GRANT INSERT, DELETE ON mysql.plugin TO '<SST user>'@'localhost';
mysql> GRANT UPDATE ON performance_schema.setup_instruments TO '<SST user>'@'localhost';
mysql> GRANT UPDATE ON performance_schema.setup_consumers TO '<SST user>'@'localhost';
mysql> GRANT BACKUP_ADMIN ON *.* TO '<SST user>'@'localhost' WITH GRANT OPTION;
mysql> GRANT EXECUTE ON *.* TO '<SST user>'@'localhost' WITH GRANT OPTION;
mysql> GRANT SELECT ON performance_schema.* TO '<SST user>'@'localhost' WITH GRANT OPTION;
Optionally plugin_dir variable needs to be configured if MySQL plugins are not in the default location.
