Chapter 1. Using MariaDB

Prerequisites

• The following files in Privacy Enhanced Mail (PEM) format have been copied to the server:

  • The private key of the server: server.example.com.key.pem
  • The server certificate: server.example.com.crt.pem
  • The Certificate Authority (CA) certificate: ca.crt.pem

  For details about creating a private key and certificate signing request (CSR), as well as about requesting a certificate from a CA, see your CA’s documentation.

Prerequisites

• You installed the MariaDB server.
• The mariadb service is running.

• The following files in Privacy Enhanced Mail (PEM) format exist on the server and are readable by the mysql user:

  • The private key of the server: /etc/pki/tls/private/server.example.com.key.pem
  • The server certificate: /etc/pki/tls/certs/server.example.com.crt.pem
  • The Certificate Authority (CA) certificate /etc/pki/tls/certs/ca.crt.pem
• The subject distinguished name (DN) or the subject alternative name (SAN) field in the server certificate matches the server’s host name.
• If the FIPS mode is enabled, clients must either support the Extended Master Secret (EMS) extension or use TLS 1.3. TLS 1.2 connections without EMS fail. For more information, see the Red Hat Knowledgebase solution TLS extension "Extended Master Secret" enforced enforced on RHEL 9.2 and later.

Prerequisites

• The MariaDB server has TLS support enabled.
• The user you configure to require secure transport exists.

• Back up one or more selected databases
• Back up all databases
• Back up a subset of tables from one database

Procedure

• To dump a single database, run:

  # mariadb-dump [options] --databases db_name > backup-file.sql

  Copy to Clipboard Toggle word wrap

• To dump multiple databases at once, run:

  # mariadb-dump [options] --databases db_name1 [db_name2 ...] > backup-file.sql

  Copy to Clipboard Toggle word wrap

• To dump all databases, run:

  # mariadb-dump [options] --all-databases > backup-file.sql

  Copy to Clipboard Toggle word wrap

• To load one or more dumped full databases back into a server, run:

  # mariadb < backup-file.sql

  Copy to Clipboard Toggle word wrap

• To load a database to a remote MariaDB server, run:

  # mariadb --host=remote_host < backup-file.sql

  Copy to Clipboard Toggle word wrap

• To dump a subset of tables from one database, add a list of the chosen tables at the end of the mariadb-dump command:

  # mariadb-dump [options] db_name [tbl_name ...​] > backup-file.sql

  Copy to Clipboard Toggle word wrap

• To load a subset of tables dumped from one database, run:

  # mariadb db_name < backup-file.sql

  Copy to Clipboard Toggle word wrap
  Note

  The db_name database must exist at this point.

• To see a list of the options that mariadb-dump supports, run:

  $ mariadb-dump --help

  Copy to Clipboard Toggle word wrap

Prerequisites

• The mariadb-backup package is installed on the system:
• You must provide mariabackup with credentials for the user under which the backup will be run. You can provide the credentials either on the command line or by a configuration file.
• Users of mariabackup must have the RELOAD, LOCK TABLES, and REPLICATION CLIENT privileges.

Procedure

• Use one of the following options to create a backup:

  • To create a backup while providing credentials on the command line, enter:

    $ mariabackup --backup --target-dir <backup_directory> --user <backup_user> --password <backup_passwd>

    Copy to Clipboard Toggle word wrap

    The target-dir option defines the directory where the backup files are stored. If you want to perform a full backup, the target directory must be empty or not exist.

    The user and password options allow you to configure the user name and the password.

  • To create a backup with credentials set in a configuration file:

    1. Create a configuration file in the /etc/my.cnf.d/ directory, for example, /etc/my.cnf.d/mariabackup.cnf.

    2. Add the following content to the file:

       [mysqld]
       user=<backup_username>
       password=<password>

       Copy to Clipboard Toggle word wrap

    3. Perform the backup:

       $ mariabackup --backup --target-dir <backup_directory>

       Copy to Clipboard Toggle word wrap

Prerequisites

• The mariadb service is stopped.
• The data directory is empty.
• Users of mariabackup must have the RELOAD, LOCK TABLES, and REPLICATION CLIENT privileges.

• 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

  Copy to Clipboard Toggle word wrap

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     |
  +--------------------+-------+

  Copy to Clipboard Toggle word wrap

• 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 |
  +----------------------+---------+

  Copy to Clipboard Toggle word wrap

  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 |
  +---------------------------+--------+

  Copy to Clipboard Toggle word wrap

  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    |
  +---------------+-------+

  Copy to Clipboard Toggle word wrap

  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    |
  +-----------------+-------+

  Copy to Clipboard Toggle word wrap

  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 |
  +----------------------------+-------+

  Copy to Clipboard Toggle word wrap

  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     |
  +---------------------------+-------+

  Copy to Clipboard Toggle word wrap

  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     |
  +--------------------------+-------+

  Copy to Clipboard Toggle word wrap

  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"

  Copy to Clipboard Toggle word wrap

  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.