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.

Prerequisites

• The MariaDB server has TLS support enabled.
• If the certificate authority (CA) that issued the server’s certificate is not trusted by RHEL, the CA certificate has been copied to the client.
• If the MariaDB server runs RHEL 9.2 or later and the FIPS mode is enabled, this client supports the Extended Master Secret (EMS) extension or uses 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 on RHEL 9.2 and later.

Verification

• Connect to the server using the hostname, and display the server status:

  # mysql -u root -p -h server.example.com -e status
  ...
  SSL:        Cipher in use is TLS_AES_256_GCM_SHA384

  If the SSL entry contains Cipher in use is…​, the connection is encrypted.

  Note that the user you use in this command has permissions to authenticate remotely.

  If the hostname you connect to does not match the hostname in the TLS certificate of the server, the ssl-verify-server-cert parameter causes the connection to fail. For example, if you connect to localhost:

  # mysql -u root -p -h localhost -e status
  ERROR 2026 (HY000): SSL connection error: Validation of SSL server certificate failed

• 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

• To dump multiple databases at once, run:

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

• To dump all databases, run:

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

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

  # mariadb < backup-file.sql

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

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

• 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

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

  # mariadb db_name < backup-file.sql

  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

Prerequisites

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

  # dnf install mariadb-backup

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

  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

  3. Perform the backup:

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

• --copy-back allows you to keep the original backup files.
• --move-back moves the backup files to the data directory and removes the original backup files.

Prerequisites

• Verify that the mariadb service is not running:

  # systemctl stop mariadb.service

• Verify that the data directory is empty.
• Users of Mariabackup must have the RELOAD, LOCK TABLES, and REPLICATION CLIENT privileges.

• 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

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

• 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

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.