Chapter 1. Using MariaDB

Procedure

1. Store the CA and server certificates in the /etc/pki/tls/certs/ directory:

   # mv <path>/server.example.com.crt.pem /etc/pki/tls/certs/
   # mv <path>/ca.crt.pem /etc/pki/tls/certs/

2. Set permissions on the CA and server certificate that enable the MariaDB server to read the files:

   # chmod 644 /etc/pki/tls/certs/server.example.com.crt.pem /etc/pki/tls/certs/ca.crt.pem

   Because certificates are part of the communication before a secure connection is established, any client can retrieve them without authentication. Therefore, you do not need to set strict permissions on the CA and server certificate files.

3. Store the server’s private key in the /etc/pki/tls/private/ directory:

   # mv <path>/server.example.com.key.pem /etc/pki/tls/private/

4. Set secure permissions on the server’s private key:

   # chmod 640 /etc/pki/tls/private/server.example.com.key.pem
   # chgrp mysql /etc/pki/tls/private/server.example.com.key.pem

   If unauthorized users have access to the private key, connections to the MariaDB server are no longer secure.

5. Restore the SELinux context:

   #  restorecon -Rv /etc/pki/tls/

Procedure

1. Create the /etc/my.cnf.d/mariadb-server-tls.cnf file:

   1. Add the following content to configure the paths to the private key, server and CA certificate:

      [mariadb]
      ssl_key = /etc/pki/tls/private/server.example.com.key.pem
      ssl_cert = /etc/pki/tls/certs/server.example.com.crt.pem
      ssl_ca = /etc/pki/tls/certs/ca.crt.pem

   2. If you have a Certificate Revocation List (CRL), configure the MariaDB server to use it:

      ssl_crl = /etc/pki/tls/certs/example.crl.pem

   3. Optional: Reject connection attempts without encryption. To enable this feature, append:

      require_secure_transport = on

   4. Optional: Set the TLS versions the server should support. For example, to support TLS 1.2 and TLS 1.3, append:

      tls_version = TLSv1.2,TLSv1.3

      By default, the server supports TLS 1.1, TLS 1.2, and TLS 1.3.

2. Restart the mariadb service:

   # systemctl restart mariadb

1. Verify that MariaDB now has TLS encryption enabled:

   # mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'have_ssl';"
   +---------------+-----------------+
   | Variable_name | Value           |
   +---------------+-----------------+
   | have_ssl      | YES             |
   +---------------+-----------------+

   If the have_ssl variable is set to yes, TLS encryption is enabled.

2. If you configured the MariaDB service to only support specific TLS versions, display the tls_version variable:

   # mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'tls_version';"
   +---------------+-----------------+
   | Variable_name | Value           |
   +---------------+-----------------+
   | tls_version   | TLSv1.2,TLSv1.3 |
   +---------------+-----------------+

Procedure

1. Connect as an administrative user to the MariaDB server:

   # mysql -u root -p -h server.example.com

   If your administrative user has no permissions to access the server remotely, perform the command on the MariaDB server and connect to localhost.

2. Use the REQUIRE SSL clause to enforce that a user must connect by using a TLS-encrypted connection:

   MariaDB [(none)]> ALTER USER 'example'@'%' REQUIRE SSL;

Verification

1. Connect to the server as the example user by using TLS encryption:

   # mysql -u example -p -h server.example.com --ssl
   ...
   MariaDB [(none)]>

   If no error is shown and you have access to the interactive MariaDB console, the connection with TLS succeeds.

2. Attempt to connect as the example user with TLS disabled:

   # mysql -u example -p -h server.example.com --skip-ssl
   ERROR 1045 (28000): Access denied for user 'example'@'server.example.com' (using password: YES)

   The server rejected the login attempt because TLS is required for this user but disabled (--skip-ssl).

Procedure

1. Use one of the following options to restore the data:

   • To restore data from the backup in the /var/mariadb/backup/ directory and keep the original backup files, enter:

     $ mariabackup --copy-back --target-dir=/var/mariadb/backup/

   • To restore data from the backup in the /var/mariadb/backup/ directory and remove the original backup files, enter:

     $ mariabackup --move-back --target-dir=/var/mariadb/backup/

2. Fix the file permissions. For example, to recursively change ownership of the files to the mysql user and group, enter:

   # chown -R mysql:mysql /var/lib/mysql/

   When restoring a database, mariabackup preserves the file and directory privileges of the backup. However, mariabackup writes the files to disk as the user and group restoring the database. Therefore, after restoring a backup, you must adjust the owner of the data directory to match the user and group for the MariaDB server.

3. Start the mariadb service:

   # systemctl start mariadb.service

Procedure

1. Stop the mariadb service:

   # systemctl stop mariadb.service

2. Copy the data files:

   # cp -rp /var/lib/mysql/ /backup-location/data/

3. Copy the configuration files:

   # cp -rp /etc/my.cnf /etc/my.cnf.d/ /backup-location/configuration/

4. Optional: Copy the log files:

   # cp -p /var/log/mariadb/* /backup-location/logs/

5. Start the mariadb service:

   # systemctl start mariadb.service

6. When loading the backed up data from the backup location to the /var/lib/mysql directory, ensure that mysql:mysql is an owner of all data in /var/lib/mysql:

   # chown -R mysql:mysql /var/lib/mysql/

Procedure

1. Install the MariaDB Galera Cluster packages:

   # dnf install mariadb-server-galera

   As a result, the following packages are installed together with their dependencies:

   • mariadb-server-galera
   • mariadb-server

   • galera

     For more information about these packages required to build MariaDB Galera Cluster, see Components to build MariaDB Cluster.

2. Update the MariaDB server replication configuration before the system is added to a cluster for the first time. The default configuration is distributed in the /etc/my.cnf.d/galera.cnf file. Before deploying MariaDB Galera Cluster, set the wsrep_cluster_address option in the /etc/my.cnf.d/galera.cnf file on all nodes to start with the following string:

   gcomm://

   • For the initial node, it is possible to set wsrep_cluster_address as an empty list:

     wsrep_cluster_address="gcomm://"

   • For all other nodes, set wsrep_cluster_address to include an address to any node which is already a part of the running cluster. For example:

     wsrep_cluster_address="gcomm://10.0.0.10"

     For more information about how to set Galera Cluster address, see Galera Cluster Address.

3. Enable the wsrep API on every node by setting the wsrep_on=1 option in the /etc/my.cnf.d/galera.cnf configuration file.

4. Add the wsrep_provider_options variable to the Galera configuration file with the TLS keys and certificates. For example:

   wsrep_provider_options="socket.ssl_cert=/etc/pki/tls/certs/source.crt;socket.ssl_key=/etc/pki/tls/private/source.key;socket.ssl_ca=/etc/pki/tls/certs/ca.crt”

5. Bootstrap a first node of a new cluster by running the following wrapper on that node:

   # galera_new_cluster

   This wrapper ensures that the MariaDB server daemon (mariadbd) runs with the --wsrep-new-cluster option. This option provides the information that there is no existing cluster to connect to. Therefore, the node creates a new UUID to identify the new cluster.

   Note

   The mariadb service supports a systemd method for interacting with multiple MariaDB server processes. Therefore, in cases with multiple running MariaDB servers, you can bootstrap a specific instance by specifying the instance name as a suffix:

   # galera_new_cluster mariadb@node1

6. Connect other nodes to the cluster by running the following command on each of the nodes:

   # systemctl start mariadb

   As a result, the node connects to the cluster, and synchronizes itself with the state of the cluster.