Shulou(Shulou.com)06/01 Report--

This article will explain in detail the example analysis of Percona XtraDB Cluster high availability and status snapshot transfer PXC 5.7. the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Percona XtraDB Cluster (hereinafter referred to as PXC) high availability cluster supports restart, upgrade or unexpected downtime of any node during operation, that is, it solves the problem of single point of failure. So during this unexpected downtime or restart, how can the data lost by the node be synchronized again? This paper introduces how to achieve high availability in the event of node failure and restart PXC and the advantages and disadvantages of several methods of state snapshot transmission.

1. High availability

In a basic setup with three nodes, if you shut down any nodes, the Percona XtraDB cluster will continue to run. At any time, you can shut down any node to perform maintenance or make configuration changes. Even in unplanned situations, such as a node crash or unavailable over the network, the Percona XtraDB cluster will continue to work and you will be able to run queries on the worker node.

If the data changes during a node shutdown, the node has two options when it joins the cluster again:

The    status snapshot transfers State Snapshot Transfer (SST), that is, when all data is copied from one node to another.

   typically uses SST when a new node joins the cluster and receives all data from existing nodes. There are three SST methods available in the Percona XtraDB cluster:

     mysqldump

     rsync

     xtrabackup

The disadvantage of    mysqldump and rsync is that when the data is being replicated, your cluster state will become read-only (these two SST methods use FLUSH TABLES WITH READ LOCK)

   uses the SST xtrabackup method, which does not require a read lock (READ LOCK) during the entire synchronization process, but is only used to synchronize .frm files (the same as regular backups).

   incremental state transfer Incremental State Transfer (IST) is when only incremental changes are copied from one node to another.

Even if    does not lock the cluster in a read-only state, SST may affect and interfere with the normal operation of the service. IST can avoid this situation. If a node shuts down in a short period of time, it can get changes only at the time of failure. IST is implemented using a caching mechanism on the node. Each node contains a cache, a ring buffer that changes the last N times (the size is configurable), and the node is able to transmit part of the cache. Obviously, IST can be completed only when the amount of change required for transmission is less than N. If it exceeds N, the joining node must perform a SST.

You can use the following command to monitor the current status of a node:

SHOW STATUS LIKE 'wsrep_local_state_comment'

When a node is in state Synced (6), it is part of the cluster and can provide services

II. Status snapshot transmission

Status snapshot transfer (SST) is a complete copy of data from a node (donor) to a participating node (participant). Use it when a new node joins the cluster. In order to synchronize with the cluster, the new node must receive data from nodes that are already part of the cluster.

There are three SST methods available in the Percona XtraDB cluster:

   mysqldump

   rsync

   xtrabackup

The disadvantage of mysqldump and rsync is that the donor node becomes READ-ONLY when the data is copied. On the other hand, Xtrabackup SST uses backup locks, which means that the Galera provider does not pause like FTWRL (refresh tables with read locks). The SST method can be configured using the wsrep_sst_method variable.

Be careful

If the gcs.sync_donor variable is set to Yes (default is No), then if the donated node is blocked by SST, the entire cluster will be blocked.

1. Select SST donation node

If no nodes are available to securely perform incremental state transfer (IST), the cluster defaults to SST.

If a node is available to perform IST, the cluster prefers the local node on the remote node as the donor node.

If no local node is available to perform the IST, the cluster selects a remote node as the donation node.

If there are multiple local and remote nodes that can perform IST, the cluster selects the node with the highest seqno as the donation node.

2. Use Percona Xtrabackup

The default SST method is to use Percona XtraBackup's xtrabackup-v2. This is the least blocking method to take advantage of backup locks. XtraBackup runs locally on the donation node, so it is important to set up the correct user credentials on the donation node. In order for the Percona XtraDB cluster to perform SST using XtraBackup, you need to set the certificate used to connect to the donor node in the wsrep_sst_auth variable. In addition to the credentials, you need to specify datadir in the server configuration file my.cnf, otherwise the transfer process will fail.

For more information about the required credentials, refer to the XtraBackup manual.

To test whether the credentials are available, run innobackupex on the donor node with the user name and password specified in the wsrep_sst_auth variable. For example, if the value wsrep_sst_auth is root:Passw0rd, the innobackupex command should look like this:

Innobackupex-user = root-password = Passw0rd / tmp/

More information about this method is provided in the Percona XtraBackup SST configuration documentation.

3. Use mysqldump

This method uses the standard mysqldump utility to dump all databases from the donated node and import them into the join node. For this method to be effective, wsrep_sst_auth needs to set the variable using the root certificate. This method is the slowest and performs global locking during SST, which prevents writes to the donor node.

The script for this method is / usr/bin/wsrep_sst_mysqldump, which is included in the Percona XtraDB Cluster binary package.

4. Use rsync

This method uses rsync to copy files from the donor node to the joining node. In some cases, this may be faster than using XtraBackup, but it requires a global data lock, which prevents writes to the donor node. This method does not need to set the root credential in the wsrep_sst_auth variable.

The script for this method is / usr/bin/wsrep_sst_rsync, which is included in the Percona XtraDB Cluster binary package.

5. SST of tables whose tablespaces are not in the data directory

For example:

CREATE TABLE T1 (C1 INT PRIMARY KEY) DATA DIRECTORY ='/ alternative/directory'

The result depends on the SST method:

SST uses rsync

SST will report success, but the table's data will not be copied because rsync is just copying files. You will not be able to access the table on the joiner node:

Mysql > select * from T1

ERROR 1812 (HY000): Tablespace is missing for table sbtest.t1.

SST uses mysqldump

Work as expected. If the file does not exist, it will be created. Otherwise, it will try to use the file (if the file does not have the expected format, an error will be returned).

SST using Percona XtraBackup

XtraBackup restores the table to the same location on the joiner node. If the target directory does not exist, it will be created. If the target file already exists, an error is returned because XtraBackup cannot clean up tablespaces that are not in the data directory.

This is the end of the sample analysis of Percona XtraDB Cluster High availability and status Snapshot transfer PXC 5.7. I hope the above content can be of some help and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.