Chapter 9. Database servers

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: If you run MariaDB 10.5.2 or later, you can reject connection attempts without encryption. To enable this feature, append:

      require_secure_transport = on

   4. Optional: If you run MariaDB 10.4.6 or later, you can 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 using a TLS-encrypted connection:

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

Verification

1. Connect to the server as the example user 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. If RHEL does not trust the CA that issued the server’s certificate:

   1. Copy the CA certificate to the /etc/pki/ca-trust/source/anchors/ directory:

      # cp <path>/ca.crt.pem /etc/pki/ca-trust/source/anchors/

   2. Set permissions that enable all users to read the CA certificate file:

      # chmod 644 /etc/pki/ca-trust/source/anchors/ca.crt.pem

   3. Rebuild the CA trust database:

      # update-ca-trust

2. Create the /etc/my.cnf.d/mariadb-client-tls.cnf file with the following content:

   [client-mariadb]
   ssl
   ssl-verify-server-cert

   These settings define that the MariaDB client uses TLS encryption (ssl) and that the client compares the hostname with the CN in the server certificate (ssl-verify-server-cert).

Procedure

1. Run the mariabackup command:

   • To restore data and keep the original backup files, use the --copy-back option:

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

   • To restore data and remove the original backup files, use the --move-back option:

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

2. Fix the file permissions.

   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. After restoring a backup, you may need to adjust the owner of the data directory to match the user and group for the MariaDB server, typically mysql for both.

   For example, to recursively change ownership of the files to the mysql user and group:

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

3. Start the mariadb service:

   # systemctl start mariadb.service

Procedure

1. Stop the mariadb service:

   # systemctl stop mariadb.service

2. Copy the data files to the required location:

   # cp -r /var/lib/mysql /backup-location

3. Optionally, copy the configuration files to the required location:

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

4. Optionally, copy the log files to the required location:

   # cp /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. Ensure that the mariadb-server package is installed on the RHEL 8 system:

   # yum install mariadb-server

2. Ensure that the mariadb service is not running on either of the source and target systems at the time of copying data:

   # systemctl stop mariadb.service

3. Copy the data from the source location to the /var/lib/mysql/ directory on the RHEL 8 target system.

4. Set the appropriate permissions and SELinux context for copied files on the target system:

   # restorecon -vr /var/lib/mysql

5. Start the MariaDB server on the target system:

   # systemctl start mariadb.service

6. Run the mysql_upgrade command to check and repair internal tables:

   $ mysql_upgrade

7. When the upgrade is complete, verify that all configuration files within the /etc/my.cnf.d/ directory include only options valid for MariaDB 10.3.

Procedure

1. Stop the MariaDB server:

   # systemctl stop mariadb.service

2. Execute the following command to determine if your system is prepared for switching to a later stream:

   # yum distro-sync

   This command must finish with the message Nothing to do. Complete! For more information, see Switching to a later stream.

3. Reset the mariadb module on your system:

   # yum module reset mariadb

4. Enable the new mariadb:10.5 module stream:

   # yum module enable mariadb:10.5

5. Synchronize installed packages to perform the change between streams:

   # yum distro-sync

   This will update all installed MariaDB packages.

6. Adjust configuration so that option files located in /etc/my.cnf.d/ include only options valid for MariaDB 10.5. For details, see upstream documentation for MariaDB 10.4 and MariaDB 10.5.

7. Start the MariaDB server.

   • When upgrading a database running standalone:

     # systemctl start mariadb.service

   • When upgrading a Galera cluster node:

     # galera_new_cluster

     The mariadb service will be started automatically.

8. Execute the mariadb-upgrade utility to check and repair internal tables.

   • When upgrading a database running standalone:

     # mariadb-upgrade

   • When upgrading a Galera cluster node:

     # mariadb-upgrade --skip-write-binlog

Procedure

1. 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 (mysqld) 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

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

Procedure

1. Stop the mysqld service:

   # systemctl stop mysqld.service

2. Copy the data files to the required location:

   # cp -r /var/lib/mysql /backup-location

3. Optionally, copy the configuration files to the required location:

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

4. Optionally, copy the log files to the required location:

   # cp /var/log/mysql/* /backup-location/logs

5. Start the mysqld service:

   # systemctl start mysqld.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. Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section:

   • bind-address=source_ip_adress

     This option is required for connections made from replicas to the source.

   • server-id=id

     The id must be unique.

   • log_bin=path_to_source_server_log

     This option defines a path to the binary log file of the MySQL source server. For example: log_bin=/var/log/mysql/mysql-bin.log.

   • gtid_mode=ON

     This option enables global transaction identifiers (GTIDs) on the server.

   • enforce-gtid-consistency=ON

     The server enforces GTID consistency by allowing execution of only statements that can be safely logged using a GTID.

   • Optional: binlog_do_db=db_name

     Use this option if you want to replicate only selected databases. To replicate more than one selected database, specify each of the databases separately:

     binlog_do_db=db_name1
     binlog_do_db=db_name2
     binlog_do_db=db_name3

   • Optional: binlog_ignore_db=db_name

     Use this option to exclude a specific database from replication.

2. Restart the mysqld service:

   # systemctl restart mysqld.service

Procedure

1. Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section:

   • server-id=id

     The id must be unique.

   • relay-log=path_to_replica_server_log

     The relay log is a set of log files created by the MySQL replica server during replication.

   • log_bin=path_to_replica_sever_log

     This option defines a path to the binary log file of the MySQL replica server. For example: log_bin=/var/log/mysql/mysql-bin.log.

     This option is not required in a replica but strongly recommended.

   • gtid_mode=ON

     This option enables global transaction identifiers (GTIDs) on the server.

   • enforce-gtid-consistency=ON

     The server enforces GTID consistency by allowing execution of only statements that can be safely logged using a GTID.

   • log-replica-updates=ON

     This option ensures that updates received from the source server are logged in the replica’s binary log.

   • skip-replica-start=ON

     This option ensures that the replica server does not start the replication threads when the replica server starts.

   • Optional: binlog_do_db=db_name

     Use this option if you want to replicate only certain databases. To replicate more than one database, specify each of the databases separately:

     binlog_do_db=db_name1
     binlog_do_db=db_name2
     binlog_do_db=db_name3

   • Optional: binlog_ignore_db=db_name

     Use this option to exclude a specific database from replication.

2. Restart the mysqld service:

   # systemctl restart mysqld.service

Procedure

1. Create a replication user:

   mysql> CREATE USER 'replication_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

2. Grant the user replication permissions:

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replica_server_ip';

3. Reload the grant tables in the MySQL database:

   mysql> FLUSH PRIVILEGES;

4. Set the source server to read-only state:

   mysql> SET @@GLOBAL.read_only = ON;

Procedure

1. Set the replica server to read-only state:

   mysql> SET @@GLOBAL.read_only = ON;

2. Configure the replication source:

   mysql> CHANGE REPLICATION SOURCE TO
       -> SOURCE_HOST='source_ip_address',
       -> SOURCE_USER='replication_user',
       -> SOURCE_PASSWORD='password',
       -> SOURCE_AUTO_POSITION=1;

3. Start the replica thread in the MySQL replica server:

   mysql> START REPLICA;

4. Unset the read-only state on both the source and replica servers:

   mysql> SET @@GLOBAL.read_only = OFF;

5. Optional: Inspect the status of the replica server for debugging purposes:

   mysql> SHOW REPLICA STATUS\G;

   Note

   If the replica server fails to start or connect, you can skip a certain number of events following the binary log file position displayed in the output of the SHOW MASTER STATUS command. For example, skip the first event from the defined position:

   mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

   and try to start the replica server again.

6. Optional: Stop the replica thread in the replica server:

   mysql> STOP REPLICA;

1. Create an example database on the source server:

   mysql> CREATE DATABASE test_db_name;

2. Verify that the test_db_name database replicates on the replica server.

3. Display status information about the binary log files of the MySQL server by executing the following command on either of the source or replica servers:

   mysql> SHOW MASTER STATUS;

   The Executed_Gtid_Set column, which shows a set of GTIDs for transactions executed on the source, must not be empty.

   Note

   The same set of GTIDs is displayed in the Executed_Gtid_Set row when you use the SHOW SLAVE STATUS on the replica server.

Procedure

1. On the RHEL 8 system, enable the stream (version) to which you want to migrate:

   # yum module enable postgresql:stream

   Replace stream with the selected version of the PostgreSQL server.

   You can omit this step if you want to use the default stream, which provides PostgreSQL 10.

2. On the RHEL 8 system, install the postgresql-server and postgresql-upgrade packages:

   # yum install postgresql-server postgresql-upgrade

   Optionally, if you used any PostgreSQL server modules on RHEL 7, install them also on the RHEL 8 system in two versions, compiled both against PostgreSQL 9.2 (installed as the postgresql-upgrade package) and the target version of PostgreSQL (installed as the postgresql-server package). If you need to compile a third-party PostgreSQL server module, build it both against the postgresql-devel and postgresql-upgrade-devel packages.

3. Check the following items:

   • Basic configuration: On the RHEL 8 system, check whether your server uses the default /var/lib/pgsql/data directory and the database is correctly initialized and enabled. In addition, the data files must be stored in the same path as mentioned in the /usr/lib/systemd/system/postgresql.service file.
   • PostgreSQL servers: Your system can run multiple PostgreSQL servers. Ensure that the data directories for all these servers are handled independently.
   • PostgreSQL server modules: Ensure that the PostgreSQL server modules that you used on RHEL 7 are installed on your RHEL 8 system as well. Note that plug-ins are installed in the /usr/lib64/pgsql/ directory (or in the /usr/lib/pgsql/ directory on 32-bit systems).

4. Ensure that the postgresql service is not running on either of the source and target systems at the time of copying data.

   # systemctl stop postgresql.service

5. Copy the database files from the source location to the /var/lib/pgsql/data/ directory on the RHEL 8 system.

6. Perform the upgrade process by running the following command as the PostgreSQL user:

   # postgresql-setup --upgrade

   This launches the pg_upgrade process in the background.

   In case of failure, postgresql-setup provides an informative error message.

7. Copy the prior configuration from /var/lib/pgsql/data-old to the new cluster.

   Note that the fast upgrade does not reuse the prior configuration in the newer data stack and the configuration is generated from scratch. If you want to combine the old and new configurations manually, use the *.conf files in the data directories.

8. Start the new PostgreSQL server:

   # systemctl start postgresql.service

9. Analyze the new database cluster.

   • For PostgreSQL 13 or earlier:

     su postgres -c '~/analyze_new_cluster.sh'

   • For PostgreSQL 15:

     su postgres -c 'vacuumdb --all --analyze-in-stages'

10. If you want the new PostgreSQL server to be automatically started on boot, run:

    # systemctl enable postgresql.service

Procedure

1. On your RHEL 7 system, start the PostgreSQL 9.2 server:

   # systemctl start postgresql.service

2. On the RHEL 7 system, dump all databases contents into the pgdump_file.sql file:

   su - postgres -c "pg_dumpall > ~/pgdump_file.sql"

3. Verify that the databases were dumped correctly:

   su - postgres -c 'less "$HOME/pgdump_file.sql"'

   As a result, the path to the dumped sql file is displayed: /var/lib/pgsql/pgdump_file.sql.

4. On the RHEL 8 system, enable the stream (version) to which you wish to migrate:

   # yum module enable postgresql:stream

   Replace stream with the selected version of the PostgreSQL server.

   You can omit this step if you want to use the default stream, which provides PostgreSQL 10.

5. On the RHEL 8 system, install the postgresql-server package:

   # yum install postgresql-server

   Optionally, if you used any PostgreSQL server modules on RHEL 7, install them also on the RHEL 8 system. If you need to compile a third-party PostgreSQL server module, build it against the postgresql-devel package.

6. On the RHEL 8 system, initialize the data directory for the new PostgreSQL server:

   # postgresql-setup --initdb

7. On the RHEL 8 system, copy the pgdump_file.sql into the PostgreSQL home directory, and check that the file was copied correctly:

   su - postgres -c 'test -e "$HOME/pgdump_file.sql" && echo exists'

8. Copy the configuration files from the RHEL 7 system:

   su - postgres -c 'ls -1 $PGDATA/*.conf'

   The configuration files to be copied are:

   • /var/lib/pgsql/data/pg_hba.conf
   • /var/lib/pgsql/data/pg_ident.conf
   • /var/lib/pgsql/data/postgresql.conf

9. On the RHEL 8 system, start the new PostgreSQL server:

   # systemctl start postgresql.service

10. On the RHEL 8 system, import data from the dumped sql file:

    su - postgres -c 'psql -f ~/pgdump_file.sql postgres'