Chapter 2. 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 server runs RHEL 9.2 or later and 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:

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

  Copy to Clipboard Toggle word wrap

• To dump multiple databases at once, run:

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

  Copy to Clipboard Toggle word wrap

• To dump all databases, run:

  # mysqldump [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:

  # mysql < backup-file.sql

  Copy to Clipboard Toggle word wrap

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

  # mysql --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 mysqldump command:

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

  Copy to Clipboard Toggle word wrap

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

  # mysql 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 mysqldump supports, run:

  $ mysqldump --help

  Copy to Clipboard Toggle word wrap

Prerequisites

• The mariadb-backup package is installed on the system:

  # dnf install mariadb-backup

  Copy to Clipboard Toggle word wrap

• 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

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

  $ 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 lines into the [xtrabackup] or [mysqld] section of the new file:

     [xtrabackup]
     user=myuser
     password=mypassword

     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 9 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.

• MariaDB now uses the unix_socket authentication plugin by default. The plugin enables users to use operating system credentials when connecting to MariaDB through the local UNIX socket file.
• MariaDB adds mariadb-* named binaries and mysql* symbolic links pointing to the mariadb-* binaries. For example, the mysqladmin, mysqlaccess, and mysqlshow symlinks point to the mariadb-admin, mariadb-access, and mariadb-show binaries, respectively.
• The SUPER privilege has been split into several privileges to better align with each user role. As a result, certain statements have changed required privileges.
• In parallel replication, the slave_parallel_mode now defaults to optimistic.
• In the InnoDB storage engine, defaults of the following variables have been changed: innodb_adaptive_hash_index to OFF and innodb_checksum_algorithm to full_crc32.

• MariaDB now uses the libedit implementation of the underlying software managing the MariaDB command history (the .mysql_history file) instead of the previously used readline library. This change impacts users working directly with the .mysql_history file. Note that .mysql_history is a file managed by the MariaDB or MySQL applications, and users should not work with the file directly. The human-readable appearance is coincidental.

  Note

  To increase security, you can consider not maintaining a history file. To disable the command history recording:

  1. Remove the .mysql_history file if it exists.

  2. Use either of the following approaches:

     • Set the MYSQL_HISTFILE variable to /dev/null and include this setting in any of your shell’s startup files.

     • Change the .mysql_history file to a symbolic link to /dev/null:

       $ ln -s /dev/null $HOME/.mysql_history

       Copy to Clipboard Toggle word wrap

• Galera adds a new streaming replication feature, which supports replicating transactions of unlimited size. During an execution of streaming replication, a cluster replicates a transaction in small fragments.
• Galera now fully supports Global Transaction ID (GTID).
• The default value for the wsrep_on option in the /etc/my.cnf.d/galera.cnf file has changed from 1 to 0 to prevent end users from starting wsrep replication without configuring required additional options.

• MariaDB 10.5 adds a new version of the Pluggable Authentication Modules (PAM) plugin. The PAM plugin version 2.0 performs PAM authentication using a separate setuid root helper binary, which enables MariaDB to use additional PAM modules.
• The helper binary can be executed only by users in the mysql group. By default, the group contains only the mysql user. Red Hat recommends that administrators do not add more users to the mysql group to prevent password-guessing attacks without throttling or logging through this helper utility.
• In MariaDB 10.5, the Pluggable Authentication Modules (PAM) plugin and its related files have been moved to a new package, mariadb-pam. As a result, no new setuid root binary is introduced on systems that do not use PAM authentication for MariaDB.
• The mariadb-pam package contains both PAM plugin versions: version 2.0 is the default, and version 1.0 is available as the auth_pam_v1 shared object library.
• The mariadb-pam package is not installed by default with the MariaDB server. To make the PAM authentication plugin available in MariaDB 10.5, install the mariadb-pam package manually.

Prerequisites

• Before performing the upgrade, back up all your data stored in the MariaDB databases.

• A new sys_schema feature is a collection of views, functions, and procedures to provide information about database usage.
• The CREATE TABLE, ALTER TABLE, RENAME TABLE, DROP TABLE, DROP DATABASE, and related Data Definition Language (DDL) statements are now atomic. The statement must be fully completed, otherwise the changes are reverted. Note that when deleting multiple tables with DROP TABLE, only each individual drop is atomic, not the full list of tables.
• A new GRANT …​ TO PUBLIC privilege is available.
• The SUPER and READ ONLY ADMIN privileges are now separate.
• You can now store universally unique identifiers in the new UUID database data type.
• MariaDB now supports the Secure Socket Layer (SSL) protocol version 3.
• The MariaDB server now requires correctly configured SSL to start. Previously, MariaDB silently disabled SSL and used insecure connections in case of misconfigured SSL.
• MariaDB now supports the natural sort order through the natural_sort_key() function.
• A new SFORMAT function is now available for arbitrary text formatting.
• The utf8 character set (and related collations) is now by default an alias for utf8mb3.
• MariaDB supports the Unicode Collation Algorithm (UCA) 14 collations.
• systemd socket activation files for MariaDB are now available in the /usr/share/ directory. Note that they are not a part of the default configuration in RHEL as opposed to upstream.
• Error messages now contain the MariaDB string instead of MySQL.
• Error messages are now available in the Chinese language.
• The default logrotate file has changed significantly. Review your configuration before migrating to MariaDB 10.11.
• For MariaDB and MySQL clients, the connection property specified on the command line (for example, --port=3306), now forces the protocol type of communication between the client and the server, such as tcp, socket, pipe, or memory. Previously, for example, the specified port was ignored if a MariaDB client connected through a UNIX socket.

Prerequisites

• Before performing the upgrade, back up all your data stored in the MariaDB databases.