1.8. Replicating MariaDB with Galera

• Synchronous replication
• Active-active multi-source topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• Parallel replication on row level
• Direct client connections: users can log on to the cluster nodes, and work with the nodes directly while the replication runs

• No delay in propagation of the changes between particular cluster nodes
• All cluster nodes are always consistent
• The latest changes are not lost if one of the cluster nodes crashes
• Transactions on all cluster nodes are executed in parallel
• Causality across the whole cluster

• mariadb-server-galera - contains support files and scripts for MariaDB Galera Cluster.
• mariadb-server - is patched by MariaDB upstream to include the write set replication API (wsrep API). This API provides the interface between Galera replication and MariaDB.

• galera - is patched by MariaDB upstream to add full support for MariaDB. The galera package contains the following:

  • Galera Replication Library provides the whole replication functionality.
  • The Galera Arbitrator utility can be used as a cluster member that participates in voting in split-brain scenarios. However, Galera Arbitrator cannot participate in the actual replication.
  • Galera Systemd service and Galera wrapper script which are used for deploying the Galera Arbitrator utility. RHEL 10 provides the upstream version of these files, located at /usr/lib/systemd/system/garbd.service and /usr/sbin/garb-systemd.

Prerequisites

• All of the nodes in the cluster have TLS set up.

• All certificates on all nodes must have the Extended Key Usage field set to:

  TLS Web Server Authentication, TLS Web Client Authentication

Verification

• See Checking the status of a MariaDB Galera cluster.

• Display the number of nodes in the cluster:

  # mysql -u root -p -e 'show status like "wsrep_cluster_size";'
  +--------------------+-------+
  | Variable_name      | Value |
  +--------------------+-------+
  | wsrep_cluster_size | 4     |
  +--------------------+-------+

• Display the node’s cluster component status:

  # mysql -u root -p -e 'show status like "wsrep_cluster_status";'
  +----------------------+---------+
  | Variable_name        | Value   |
  +----------------------+---------+
  | wsrep_cluster_status | Primary |
  +----------------------+---------+

  The value of the wsrep_cluster_status variable indicates the status of the cluster component the current node belongs to. Possible values are:

  • Primary: The cluster is functioning normally. A quorum is present. In a healthy cluster, all nodes report Primary.
  • Non-primary: The node has lost the connection to the primary component of the cluster and is no longer part of the active cluster. However, the node still can serve read queries but cannot process write operations.
  • Disconnected: The node is not connected to any cluster component. Consequently, it cannot accept queries and is not replicating any data.

• Display the node’s status:

  # mysql -u root -p -e 'show status like "wsrep_local_state_comment";'
  +---------------------------+--------+
  | Variable_name             | Value  |
  +---------------------------+--------+
  | wsrep_local_state_comment | Synced |
  +---------------------------+--------+

  The following are frequent values of the wsrep_local_state_comment variable:

  • Synced: The node is fully synchronized within the cluster and actively participating in replication.
  • Desynced: The node is still part of the cluster but it is primarily busy with the state transfer.
  • Joining: The node is in the process of joining a cluster.
  • Joined: The node has successfully joined a cluster. It can receive and apply write sets from the cluster.
  • Donor: The node currently provides a State Snapshot Transfer (SST) to a joining node. When a new node joins and requires a full state transfer, the cluster selects an existing node to send the necessary data.

• Check whether the node accepts write sets from the cluster:

  # mysql -u root -p -e 'show status like "wsrep_ready";'
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | wsrep_ready   | ON    |
  +---------------+-------+

  When the wsrep_ready variable is ON, the node has successfully initialized its components and is connected to a cluster. Additionally, the node is synchronized or has reached a state where it can serve queries.

• Check whether the node has network connectivity with other hosts:

  # mysql -u root -p -e 'show status like "wsrep_connected";'
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | wsrep_connected | ON    |
  +-----------------+-------+

  The ON value means that node has connectivity to at least one member in the cluster.

• Display the average size of the local received queue for write sets since the last FLUSH STATUS command or since the server started:

  # mysql -u root -p -e 'show status like "wsrep_local_recv_queue_avg";'
  +----------------------------+-------+
  | Variable_name              | Value |
  +----------------------------+-------+
  | wsrep_local_recv_queue_avg | 0.012 |
  +----------------------------+-------+

  A value near 0 is the ideal state and indicates that the node continues applying write sets as they are received. A persistently high or growing value can be an indicator of performance bottlenecks, such as slow disk I/O.

• Display the flow control status:

  # mysql -u root -p -e 'show status like "wsrep_flow_control_paused";'
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | wsrep_flow_control_paused | 0     |
  +---------------------------+-------+

  This variable represents the fraction of time a node has been paused and is unable to process new incoming transactions because its local receive queue was too full, triggering flow control. A value close to 0 indicates the node continues with the replication workload efficiently. A value approaching 1.0 means that the node frequently or constantly encounters difficulty in applying write sets and can be a bottleneck for the cluster.

  If the node is frequently pausing, you can adjust the wsrep_slave_threads parameter in the /etc/my.cnf.d/galera.cnf file.

• Display the average distance between the lowest and highest sequence numbers the node can apply in parallel:

  # mysql -u root -p -e 'show status like "wsrep_cert_deps_distance";'
  +--------------------------+-------+
  | Variable_name            | Value |
  +--------------------------+-------+
  | wsrep_cert_deps_distance | 1     |
  +--------------------------+-------+

  A higher value indicates a greater degree of parallelism. It is the optimal value you can use in the wsrep_slave_threads parameter in the /etc/my.cnf.d/galera.cnf file.

Procedure

• On the particular node, provide an address to one or more existing cluster members in the wsrep_cluster_address option within the [mariadb] section of the /etc/my.cnf.d/galera.cnf configuration file :

  [mariadb]
  wsrep_cluster_address="gcomm://192.168.0.1"

  When a new node connects to one of the existing cluster nodes, it is able to see all nodes in the cluster.

  However, preferably list all nodes of the cluster in wsrep_cluster_address.

  As a result, any node can join a cluster by connecting to any other cluster node, even if one or more cluster nodes are down. When all members agree on the membership, the cluster’s state is changed. If the new node’s state is different from the state of the cluster, the new node requests either an Incremental State Transfer (IST) or a State Snapshot Transfer (SST) to ensure consistency with the other nodes.