This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Database Servers (PostgreSQL and MariaDB) on AlmaLinux 9

Database Servers (PostgreSQL and MariaDB) on AlmaLinux 9

This Document is actively being developed as a part of ongoing AlmaLinux learning efforts. Chapters will be added periodically.

AlmaLinux 9: Database Servers (PostgreSQL and MariaDB)

1 - How to Install PostgreSQL on AlmaLinux

In this guide, we’ll walk you through the process of installing and setting up PostgreSQL on AlmaLinux

PostgreSQL, often referred to as Postgres, is a powerful, open-source, object-relational database management system (RDBMS) widely used for modern web applications. Its robust feature set, scalability, and adherence to SQL standards make it a top choice for developers and businesses.

In this guide, we’ll walk you through the process of installing and setting up PostgreSQL on AlmaLinux, a popular, stable Linux distribution that’s a downstream fork of CentOS. By the end, you’ll have a fully operational PostgreSQL installation ready to handle database operations.

Table of Contents

  1. Introduction to PostgreSQL
  2. Prerequisites
  3. Step-by-Step Installation Guide
  4. Post-Installation Configuration
  5. Connecting to PostgreSQL
  6. Securing and Optimizing PostgreSQL
  7. Conclusion

1. Introduction to PostgreSQL

PostgreSQL is known for its advanced features like JSON/JSONB support, full-text search, and strong ACID compliance. It is ideal for applications that require complex querying, data integrity, and scalability.

Key Features:

  • Multi-Version Concurrency Control (MVCC)
  • Support for advanced data types and indexing
  • Extensibility through plugins and custom procedures
  • High availability and replication capabilities

2. Prerequisites

Before starting the installation process, ensure the following:

  1. AlmaLinux server with a sudo-enabled user or root access.
  2. Access to the internet for downloading packages.
  3. Basic knowledge of Linux commands.

Update the System

Begin by updating the system to the latest packages:

sudo dnf update -y

3. Step-by-Step Installation Guide

PostgreSQL can be installed from the default AlmaLinux repositories or directly from the official PostgreSQL repositories for newer versions.

Step 1: Enable the PostgreSQL Repository

The PostgreSQL Global Development Group maintains official repositories for the latest versions of PostgreSQL. To enable the repository:

  1. Install the PostgreSQL repository package:

    sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

  2. Disable the default PostgreSQL module in AlmaLinux (it often contains an older version):

    sudo dnf -qy module disable postgresql

Step 2: Install PostgreSQL

Install the desired version of PostgreSQL. For this example, we’ll install PostgreSQL 15 (replace 15 with another version if needed):

sudo dnf install -y postgresql15 postgresql15-server

Step 3: Initialize the PostgreSQL Database

After installing PostgreSQL, initialize the database cluster:

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

This command creates the necessary directories and configures the database for first-time use.

Step 4: Start and Enable PostgreSQL

To ensure PostgreSQL starts automatically on boot:

sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

Verify the service is running:

sudo systemctl status postgresql-15

You should see a message indicating that PostgreSQL is active and running.

4. Post-Installation Configuration

Step 1: Update PostgreSQL Authentication Methods

By default, PostgreSQL uses the peer authentication method, which allows only the system user postgres to connect. If you want to enable password-based access for remote or local connections:

  1. Edit the pg_hba.conf file:

    sudo nano /var/lib/pgsql/15/data/pg_hba.conf

  2. Look for the following lines and change peer or ident to md5 for password-based authentication:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5
host    all             all             127.0.0.1/32           md5
host    all             all             ::1/128                md5

  3. Save and exit the file, then reload PostgreSQL to apply changes:

    sudo systemctl reload postgresql-15

Step 2: Set a Password for the postgres User

Switch to the postgres user and open the PostgreSQL command-line interface (psql):

sudo -i -u postgres
psql

Set a password for the postgres database user:

ALTER USER postgres PASSWORD 'your_secure_password';

Exit the psql shell:

\q

Exit the postgres system user:

exit

5. Connecting to PostgreSQL

You can connect to PostgreSQL using the psql command-line tool or a graphical client like pgAdmin.

Local Connection

For local connections, use the following command:

psql -U postgres -h 127.0.0.1 -W
  • -U: Specifies the database user.
  • -h: Specifies the host (127.0.0.1 for localhost).
  • -W: Prompts for a password.

Remote Connection

To allow remote connections:

  1. Edit the postgresql.conf file to listen on all IP addresses:

    sudo nano /var/lib/pgsql/15/data/postgresql.conf

    Find and update the listen_addresses parameter:

    listen_addresses = '*'

  2. Save the file and reload PostgreSQL:

    sudo systemctl reload postgresql-15

  3. Ensure the firewall allows traffic on PostgreSQL’s default port (5432):

    sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload

You can now connect to PostgreSQL remotely using a tool like pgAdmin or a client application.

6. Securing and Optimizing PostgreSQL

Security Best Practices

  1. Use Strong Passwords: Ensure all database users have strong passwords.

  2. Restrict Access: Limit connections to trusted IP addresses in the pg_hba.conf file.

  3. Regular Backups: Use tools like pg_dump or pg_basebackup to create backups.

    Example backup command:

    pg_dump -U postgres dbname > dbname_backup.sql

  4. Enable SSL: Secure remote connections by configuring SSL for PostgreSQL.

Performance Optimization

  1. Tune Memory Settings: Adjust memory-related parameters in postgresql.conf for better performance. For example:

    shared_buffers = 256MB
work_mem = 64MB
maintenance_work_mem = 128MB

  2. Monitor Performance: Use the pg_stat_activity view to monitor active queries and database activity:

    SELECT * FROM pg_stat_activity;

  3. Analyze and Vacuum: Periodically run ANALYZE and VACUUM to optimize database performance:

    VACUUM ANALYZE;

7. Conclusion

PostgreSQL is a robust database system that pairs seamlessly with AlmaLinux for building scalable and secure applications. This guide has covered everything from installation to basic configuration and optimization. Whether you’re using PostgreSQL for web applications, data analytics, or enterprise solutions, you now have a solid foundation to get started.

By enabling password authentication, securing remote connections, and fine-tuning PostgreSQL, you can ensure your database environment is both secure and efficient. Take advantage of PostgreSQL’s advanced features and enjoy the stability AlmaLinux offers for a dependable server experience.

2 - How to Make Settings for Remote Connection on PostgreSQL on AlmaLinux

This guide will focus on configuring remote connections for PostgreSQL on AlmaLinux.

PostgreSQL, often referred to as Postgres, is a powerful, open-source relational database system that offers extensibility and SQL compliance. Setting up a remote connection to PostgreSQL is a common task for developers and system administrators, enabling them to interact with the database from remote machines. This guide will focus on configuring remote connections for PostgreSQL on AlmaLinux, a popular CentOS replacement that’s gaining traction in enterprise environments.

Table of Contents

  1. Introduction to PostgreSQL and AlmaLinux
  2. Prerequisites
  3. Installing PostgreSQL on AlmaLinux
  4. Configuring PostgreSQL for Remote Access
    • Editing the postgresql.conf File
    • Modifying the pg_hba.conf File
  5. Allowing PostgreSQL Through the Firewall
  6. Testing the Remote Connection
  7. Common Troubleshooting Tips
  8. Conclusion

1. Introduction to PostgreSQL and AlmaLinux

AlmaLinux, a community-driven Linux distribution, is widely regarded as a reliable replacement for CentOS. Its compatibility with Red Hat Enterprise Linux (RHEL) makes it a strong candidate for database servers running PostgreSQL. Remote access to PostgreSQL is especially useful in distributed systems or development environments where multiple clients need database access.

2. Prerequisites

Before diving into the setup process, ensure the following:

  • AlmaLinux is installed and updated.
  • PostgreSQL is installed on the server (we’ll cover installation in the next section).
  • You have root or sudo access to the AlmaLinux system.
  • Basic knowledge of PostgreSQL commands and SQL.

3. Installing PostgreSQL on AlmaLinux

If PostgreSQL isn’t already installed, follow these steps:

  1. Enable the PostgreSQL repository: AlmaLinux uses the PostgreSQL repository for the latest version. Install it using:

    sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

  2. Disable the default PostgreSQL module:

    sudo dnf -qy module disable postgresql

  3. Install PostgreSQL: Replace 15 with your desired version:

    sudo dnf install -y postgresql15-server

  4. Initialize the database:

    sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

  5. Enable and start PostgreSQL:

    sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

At this stage, PostgreSQL is installed and running on your AlmaLinux system.

4. Configuring PostgreSQL for Remote Access

PostgreSQL is configured to listen only to localhost by default for security reasons. To allow remote access, you need to modify a few configuration files.

Editing the postgresql.conf File

  1. Open the configuration file:

    sudo nano /var/lib/pgsql/15/data/postgresql.conf

  2. Locate the listen_addresses parameter. By default, it looks like this:

    listen_addresses = 'localhost'

  3. Change it to include the IP address you want PostgreSQL to listen on, or use * to listen on all available interfaces:

    listen_addresses = '*'

  4. Save and exit the file.

Modifying the pg_hba.conf File

The pg_hba.conf file controls client authentication. You need to add entries to allow connections from specific IP addresses.

  1. Open the file:

    sudo nano /var/lib/pgsql/15/data/pg_hba.conf

  2. Add the following line at the end of the file to allow connections from a specific IP range (replace 192.168.1.0/24 with your network range):

    host    all             all             192.168.1.0/24          md5

    Alternatively, to allow connections from all IPs (not recommended for production), use:

    host    all             all             0.0.0.0/0              md5

  3. Save and exit the file.

  4. Restart PostgreSQL to apply changes:

    sudo systemctl restart postgresql-15

5. Allowing PostgreSQL Through the Firewall

By default, AlmaLinux uses firewalld as its firewall management tool. You need to open the PostgreSQL port (5432) to allow remote connections.

  1. Add the port to the firewall rules:

    sudo firewall-cmd --permanent --add-port=5432/tcp

  2. Reload the firewall to apply changes:

    sudo firewall-cmd --reload

6. Testing the Remote Connection

To test the remote connection:

  1. From a remote machine, use the psql client or any database management tool that supports PostgreSQL.

  2. Run the following command, replacing the placeholders with appropriate values:

    psql -h <server_ip> -U <username> -d <database_name>

  3. Enter the password when prompted. If everything is configured correctly, you should see the psql prompt.

7. Common Troubleshooting Tips

If you encounter issues, consider the following:

  • Firewall Issues: Ensure the firewall on both the server and client allows traffic on port 5432.

  • Incorrect Credentials: Double-check the username, password, and database name.

  • IP Restrictions: Ensure the client’s IP address falls within the range specified in pg_hba.conf.

  • Service Status: Verify that the PostgreSQL service is running:

    sudo systemctl status postgresql-15

  • Log Files: Check PostgreSQL logs for errors:

    sudo tail -f /var/lib/pgsql/15/data/log/postgresql-*.log

8. Conclusion

Setting up remote connections for PostgreSQL on AlmaLinux involves modifying configuration files, updating firewall rules, and testing the setup. While the process requires a few careful steps, it enables you to use PostgreSQL in distributed environments effectively. Always prioritize security by limiting access to trusted IP ranges and enforcing strong authentication methods.

By following this guide, you can confidently configure PostgreSQL for remote access, ensuring seamless database management and operations. For advanced use cases, consider additional measures such as SSL/TLS encryption and database-specific roles for enhanced security.

3 - How to Configure PostgreSQL Over SSL/TLS on AlmaLinux

This guide provides a detailed walkthrough to configure PostgreSQL over SSL/TLS on AlmaLinux.

PostgreSQL is a robust and open-source relational database system renowned for its reliability and advanced features. One critical aspect of database security is ensuring secure communication between the server and clients. Configuring PostgreSQL to use SSL/TLS (Secure Sockets Layer / Transport Layer Security) on AlmaLinux is a vital step in safeguarding data in transit against eavesdropping and tampering.

This guide provides a detailed walkthrough to configure PostgreSQL over SSL/TLS on AlmaLinux. By the end of this article, you’ll have a secure PostgreSQL setup capable of encrypted communication with its clients.

Table of Contents

  1. Understanding SSL/TLS in PostgreSQL
  2. Prerequisites
  3. Installing PostgreSQL on AlmaLinux
  4. Generating SSL Certificates
  5. Configuring PostgreSQL for SSL/TLS
  6. Enabling the PostgreSQL Client to Use SSL/TLS
  7. Testing SSL/TLS Connections
  8. Troubleshooting Common Issues
  9. Best Practices for SSL/TLS in PostgreSQL
  10. Conclusion

1. Understanding SSL/TLS in PostgreSQL

SSL/TLS is a protocol designed to provide secure communication over a network. In PostgreSQL, enabling SSL/TLS ensures that the data exchanged between the server and its clients is encrypted. This is particularly important for databases exposed over the internet or in environments where sensitive data is transferred.

Key benefits include:

  • Data Integrity: Protects against data tampering during transmission.
  • Confidentiality: Encrypts sensitive information such as login credentials and query data.
  • Authentication: Verifies the identity of the server and optionally the client.

2. Prerequisites

Before proceeding, ensure the following:

  • AlmaLinux is installed and up-to-date.
  • PostgreSQL is installed on the server.
  • Access to a root or sudo-enabled user.
  • Basic knowledge of SSL/TLS concepts.

3. Installing PostgreSQL on AlmaLinux

If PostgreSQL isn’t already installed, follow these steps:

  1. Enable the PostgreSQL repository:

    sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

  2. Disable the default PostgreSQL module:

    sudo dnf -qy module disable postgresql

  3. Install PostgreSQL:

    sudo dnf install -y postgresql15-server

  4. Initialize and start PostgreSQL:

    sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

4. Generating SSL Certificates

PostgreSQL requires a valid SSL certificate and key to enable SSL/TLS. These can be self-signed for internal use or obtained from a trusted certificate authority (CA).

Step 1: Create a Self-Signed Certificate

  1. Install OpenSSL:

    sudo dnf install -y openssl

  2. Generate a private key:

    openssl genrsa -out server.key 2048

  3. Set secure permissions for the private key:

    chmod 600 server.key

  4. Create a certificate signing request (CSR):

    openssl req -new -key server.key -out server.csr

    Provide the required information during the prompt (e.g., Common Name should match your server’s hostname or IP).

  5. Generate the self-signed certificate:

    openssl x509 -req -in server.csr -signkey server.key -out server.crt -days 365

Step 2: Place the Certificates in the PostgreSQL Directory

Move the generated certificate and key to PostgreSQL’s data directory:

sudo mv server.crt server.key /var/lib/pgsql/15/data/

Ensure the files have the correct permissions:

sudo chown postgres:postgres /var/lib/pgsql/15/data/server.*

5. Configuring PostgreSQL for SSL/TLS

Step 1: Enable SSL in postgresql.conf

  1. Open the configuration file:

    sudo nano /var/lib/pgsql/15/data/postgresql.conf

  2. Locate the ssl parameter and set it to on:

    ssl = on

  3. Save and exit the file.

Step 2: Configure Client Authentication in pg_hba.conf

  1. Open the pg_hba.conf file:

    sudo nano /var/lib/pgsql/15/data/pg_hba.conf

  2. Add the following line to require SSL for all connections (adjust host parameters as needed):

    hostssl    all    all    0.0.0.0/0    md5

  3. Save and exit the file.

Step 3: Restart PostgreSQL

Restart the service to apply changes:

sudo systemctl restart postgresql-15

6. Enabling the PostgreSQL Client to Use SSL/TLS

To connect securely, the PostgreSQL client must trust the server’s certificate.

  1. Copy the server’s certificate (server.crt) to the client machine.

  2. Place the certificate in a trusted directory, e.g., ~/.postgresql/.

  3. Use the sslmode option when connecting:

    psql "host=<server_ip> dbname=<database_name> user=<username> sslmode=require"

7. Testing SSL/TLS Connections

  1. Check PostgreSQL logs: Verify that SSL is enabled by inspecting the logs:

    sudo tail -f /var/lib/pgsql/15/data/log/postgresql-*.log

  2. Connect using psql: Use the sslmode parameter to enforce SSL:

    psql -h <server_ip> -U <username> -d <database_name> --sslmode=require

  3. If the connection succeeds, confirm encryption using:

    SHOW ssl;

    The result should display on.

8. Troubleshooting Common Issues

Issue: SSL Connection Fails

  • Cause: Incorrect certificate or permissions.
  • Solution: Ensure server.key has 600 permissions and is owned by the postgres user.

Issue: sslmode Mismatch

  • Cause: Client not configured for SSL.
  • Solution: Verify the client’s sslmode configuration.

Issue: Firewall Blocks SSL Port

  • Cause: PostgreSQL port (default 5432) is blocked.

  • Solution: Open the port in the firewall:

    sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

9. Best Practices for SSL/TLS in PostgreSQL

  • Use certificates signed by a trusted CA for production environments.
  • Rotate certificates periodically to minimize the risk of compromise.
  • Enforce sslmode=verify-full for clients to ensure server identity.
  • Restrict IP ranges in pg_hba.conf to minimize exposure.

10. Conclusion

Configuring PostgreSQL over SSL/TLS on AlmaLinux is a crucial step in enhancing the security of your database infrastructure. By encrypting client-server communications, you protect sensitive data from unauthorized access. This guide walked you through generating SSL certificates, configuring PostgreSQL for SSL/TLS, and testing secure connections.

With proper setup and adherence to best practices, you can ensure a secure and reliable PostgreSQL deployment capable of meeting modern security requirements.

4 - How to Backup and Restore PostgreSQL Database on AlmaLinux

This blog post provides a comprehensive guide on how to back up and restore PostgreSQL databases on AlmaLinux.

PostgreSQL, a powerful open-source relational database system, is widely used in modern applications for its robustness, scalability, and advanced features. However, one of the most critical aspects of database management is ensuring data integrity through regular backups and the ability to restore databases efficiently. On AlmaLinux, a popular CentOS replacement, managing PostgreSQL backups is straightforward when following the right procedures.

This blog post provides a comprehensive guide on how to back up and restore PostgreSQL databases on AlmaLinux, covering essential commands, tools, and best practices.

Table of Contents

  1. Why Backups Are Essential
  2. Prerequisites for Backup and Restore
  3. Common Methods of Backing Up PostgreSQL Databases
    • Logical Backups Using pg_dump
    • Logical Backups of Entire Clusters Using pg_dumpall
    • Physical Backups Using pg_basebackup
  4. Backing Up a PostgreSQL Database on AlmaLinux
    • Using pg_dump
    • Using pg_dumpall
    • Using pg_basebackup
  5. Restoring a PostgreSQL Database
    • Restoring a Single Database
    • Restoring an Entire Cluster
    • Restoring from Physical Backups
  6. Scheduling Automatic Backups with Cron Jobs
  7. Best Practices for PostgreSQL Backup and Restore
  8. Troubleshooting Common Issues
  9. Conclusion

1. Why Backups Are Essential

Backups are the backbone of any reliable database management strategy. They ensure:

  • Data Protection: Safeguard against accidental deletion, corruption, or hardware failures.
  • Disaster Recovery: Facilitate rapid recovery in the event of system crashes or data loss.
  • Testing and Development: Enable replication of production data for testing purposes.

Without a reliable backup plan, you risk losing critical data and potentially facing significant downtime.

2. Prerequisites for Backup and Restore

Before proceeding, ensure you have the following:

  1. AlmaLinux Environment: A running AlmaLinux instance with PostgreSQL installed.
  2. PostgreSQL Access: Administrative privileges (e.g., postgres user).
  3. Sufficient Storage: Ensure enough disk space for backups.
  4. Required Tools: Ensure PostgreSQL utilities (pg_dump, pg_dumpall, pg_basebackup) are installed.

3. Common Methods of Backing Up PostgreSQL Databases

PostgreSQL offers two primary types of backups:

  • Logical Backups: Capture the database schema and data in a logical format, ideal for individual databases or tables.
  • Physical Backups: Clone the entire database cluster directory for faster restoration, suitable for large-scale setups.

4. Backing Up a PostgreSQL Database on AlmaLinux

Using pg_dump

The pg_dump utility is used to back up individual databases.

  1. Basic Command:

    pg_dump -U postgres -d database_name > database_name.sql

  2. Compress the Backup File:

    pg_dump -U postgres -d database_name | gzip > database_name.sql.gz

  3. Custom Format for Faster Restores:

    pg_dump -U postgres -F c -d database_name -f database_name.backup

    The -F c option generates a custom binary format that is faster for restoring.

Using pg_dumpall

For backing up all databases in a PostgreSQL cluster, use pg_dumpall:

  1. Backup All Databases:

    pg_dumpall -U postgres > all_databases.sql

  2. Include Global Roles and Configuration:

    pg_dumpall -U postgres --globals-only > global_roles.sql

Using pg_basebackup

For physical backups, pg_basebackup creates a binary copy of the entire database cluster.

  1. Run the Backup:

    pg_basebackup -U postgres -D /path/to/backup_directory -F tar -X fetch
    • -D: Specifies the backup directory.
    • -F tar: Creates a tar archive.
    • -X fetch: Ensures transaction logs are included.

5. Restoring a PostgreSQL Database

Restoring a Single Database

  1. Using psql:

    psql -U postgres -d database_name -f database_name.sql

  2. From a Custom Backup Format: Use pg_restore for backups created with pg_dump -F c:

    pg_restore -U postgres -d database_name database_name.backup

Restoring an Entire Cluster

For cluster-wide backups taken with pg_dumpall:

  1. Restore the Entire Cluster:

    psql -U postgres -f all_databases.sql

  2. Restore Global Roles:

    psql -U postgres -f global_roles.sql

Restoring from Physical Backups

For physical backups created with pg_basebackup:

  1. Stop the PostgreSQL service:

    sudo systemctl stop postgresql-15

  2. Replace the cluster directory:

    rm -rf /var/lib/pgsql/15/data/*
cp -r /path/to/backup_directory/* /var/lib/pgsql/15/data/

  3. Set proper ownership and permissions:

    chown -R postgres:postgres /var/lib/pgsql/15/data/

  4. Start the PostgreSQL service:

    sudo systemctl start postgresql-15

6. Scheduling Automatic Backups with Cron Jobs

Automate backups using cron jobs to ensure regular and consistent backups.

  1. Open the crontab editor:

    crontab -e

  2. Add a cron job for daily backups:

    0 2 * * * pg_dump -U postgres -d database_name | gzip > /path/to/backup_directory/database_name_$(date +\%F).sql.gz

    This command backs up the database every day at 2 AM.

7. Best Practices for PostgreSQL Backup and Restore

  1. Test Your Backups: Regularly test restoring backups to ensure reliability.
  2. Automate Backups: Use cron jobs or backup scripts to reduce manual intervention.
  3. Store Backups Securely: Encrypt sensitive backups and store them in secure locations.
  4. Retain Multiple Backups: Maintain several backup copies in different locations to prevent data loss.
  5. Monitor Disk Usage: Ensure adequate disk space to avoid failed backups.

8. Troubleshooting Common Issues

Backup Fails with “Permission Denied”

  • Solution: Ensure the postgres user has write access to the backup directory.

Restore Fails with “Role Does Not Exist”

  • Solution: Restore global roles using:

    psql -U postgres -f global_roles.sql

Incomplete Backups

  • Solution: Monitor the process for errors and ensure sufficient disk space.

9. Conclusion

Backing up and restoring PostgreSQL databases on AlmaLinux is crucial for maintaining data integrity and ensuring business continuity. By leveraging tools like pg_dump, pg_dumpall, and pg_basebackup, you can efficiently handle backups and restores tailored to your requirements. Combining these with automation and best practices ensures a robust data management strategy.

With this guide, you’re equipped to implement a reliable PostgreSQL backup and restore plan, safeguarding your data against unforeseen events.

5 - How to Set Up Streaming Replication on PostgreSQL on AlmaLinux

In this article, we’ll guide you through setting up streaming replication on PostgreSQL running on AlmaLinux.

PostgreSQL, an advanced open-source relational database system, supports robust replication features that allow high availability, scalability, and fault tolerance. Streaming replication, in particular, is widely used for maintaining a near-real-time replica of the primary database. In this article, we’ll guide you through setting up streaming replication on PostgreSQL running on AlmaLinux, a reliable RHEL-based distribution.

Table of Contents

  1. Introduction to Streaming Replication
  2. Prerequisites for Setting Up Streaming Replication
  3. Understanding the Primary and Standby Roles
  4. Installing PostgreSQL on AlmaLinux
  5. Configuring the Primary Server for Streaming Replication
  6. Setting Up the Standby Server
  7. Testing the Streaming Replication Setup
  8. Monitoring Streaming Replication
  9. Common Issues and Troubleshooting
  10. Conclusion

1. Introduction to Streaming Replication

Streaming replication in PostgreSQL provides a mechanism where changes made to the primary database are streamed in real-time to one or more standby servers. These standby servers can act as hot backups or read-only servers for query load balancing. This feature is critical for:

  • High Availability: Ensuring minimal downtime during server failures.
  • Data Redundancy: Preventing data loss in case of primary server crashes.
  • Scalability: Offloading read operations to standby servers.

2. Prerequisites for Setting Up Streaming Replication

Before diving into the setup, ensure you have the following:

  1. Two AlmaLinux Servers: One for the primary database and one for the standby database.
  2. PostgreSQL Installed: Both servers should have PostgreSQL installed and running.
  3. Network Connectivity: Both servers should be able to communicate with each other.
  4. Sufficient Storage: Ensure adequate storage for the WAL (Write-Ahead Logging) files and database data.
  5. User Privileges: Access to the PostgreSQL administrative user (postgres) and sudo privileges on both servers.

3. Understanding the Primary and Standby Roles

  • Primary Server: The main PostgreSQL server where all write operations occur.
  • Standby Server: A replica server that receives changes from the primary server.

Streaming replication works by continuously streaming WAL files from the primary server to the standby server.

4. Installing PostgreSQL on AlmaLinux

If PostgreSQL is not installed, follow these steps on both the primary and standby servers:

  1. Enable PostgreSQL Repository:

    sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

  2. Disable the Default PostgreSQL Module:

    sudo dnf -qy module disable postgresql

  3. Install PostgreSQL:

    sudo dnf install -y postgresql15-server

  4. Initialize and Start PostgreSQL:

    sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

5. Configuring the Primary Server for Streaming Replication

Step 1: Edit postgresql.conf

Modify the configuration file to enable replication and allow connections from the standby server:

  1. Open the file:

    sudo nano /var/lib/pgsql/15/data/postgresql.conf

  2. Update the following parameters:

    listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_size = 128MB
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/15/archive/%f'

  3. Save and exit the file.

Step 2: Edit pg_hba.conf

Allow the standby server to connect to the primary server for replication.

  1. Open the file:

    sudo nano /var/lib/pgsql/15/data/pg_hba.conf

  2. Add the following line, replacing <standby_ip> with the standby server’s IP:

    host    replication     all     <standby_ip>/32     md5

  3. Save and exit the file.

Step 3: Create a Replication Role

Create a user with replication privileges:

  1. Log in to the PostgreSQL shell:

    sudo -u postgres psql

  2. Create the replication user:

    CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'yourpassword';

  3. Exit the PostgreSQL shell:

    \q

Step 4: Restart PostgreSQL

Restart the PostgreSQL service to apply changes:

sudo systemctl restart postgresql-15

6. Setting Up the Standby Server

Step 1: Stop PostgreSQL Service

Stop the PostgreSQL service on the standby server:

sudo systemctl stop postgresql-15

Step 2: Synchronize Data from the Primary Server

Use pg_basebackup to copy the data directory from the primary server to the standby server:

pg_basebackup -h <primary_ip> -D /var/lib/pgsql/15/data -U replicator -Fp -Xs -P
  • Replace <primary_ip> with the primary server’s IP address.
  • Provide the replicator user password when prompted.

Step 3: Configure Recovery Settings

  1. Create a recovery.conf file in the PostgreSQL data directory:

    sudo nano /var/lib/pgsql/15/data/recovery.conf

  2. Add the following lines:

    standby_mode = 'on'
primary_conninfo = 'host=<primary_ip> port=5432 user=replicator password=yourpassword'
restore_command = 'cp /var/lib/pgsql/15/archive/%f %p'
trigger_file = '/tmp/failover.trigger'

  3. Save and exit the file.

Step 4: Adjust Permissions

Set the correct permissions for the recovery.conf file:

sudo chown postgres:postgres /var/lib/pgsql/15/data/recovery.conf

Step 5: Start PostgreSQL Service

Start the PostgreSQL service on the standby server:

sudo systemctl start postgresql-15

7. Testing the Streaming Replication Setup

  1. Verify Streaming Status on the Primary Server: Log in to the PostgreSQL shell on the primary server and check the replication status:

    SELECT * FROM pg_stat_replication;

    Look for the standby server’s details in the output.

  2. Perform a Test Write: On the primary server, create a test table and insert data:

    CREATE TABLE replication_test (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO replication_test (name) VALUES ('Replication works!');

  3. Verify the Data on the Standby Server: Connect to the standby server and check if the table exists:

    SELECT * FROM replication_test;

    The data should match the primary server’s table.

8. Monitoring Streaming Replication

Use the following tools and commands to monitor replication:

  • Check Replication Lag:

    SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn() AS replication_lag;

  • View WAL Sender and Receiver Status:

    SELECT * FROM pg_stat_replication;

  • Logs: Check PostgreSQL logs for replication-related messages:

    sudo tail -f /var/lib/pgsql/15/data/log/postgresql-*.log

9. Common Issues and Troubleshooting

  • Connection Refused: Ensure the primary server’s pg_hba.conf and postgresql.conf files are configured correctly.
  • Data Directory Errors: Verify that the standby server’s data directory is an exact copy of the primary server’s directory.
  • Replication Lag: Check the network performance and adjust the wal_keep_size parameter as needed.

10. Conclusion

Setting up streaming replication in PostgreSQL on AlmaLinux ensures database high availability, scalability, and disaster recovery. By following this guide, you can configure a reliable replication environment that is secure and efficient. Regularly monitor replication health and test failover scenarios to maintain a robust database infrastructure.

6 - How to Install MariaDB on AlmaLinux

In this comprehensive guide, we’ll walk you through the steps to install MariaDB on AlmaLinux, configure it for production use, and verify its operation.

MariaDB, an open-source relational database management system, is a widely popular alternative to MySQL. Known for its performance, scalability, and reliability, MariaDB is a favored choice for web applications, data warehousing, and analytics. AlmaLinux, a CentOS replacement, offers a stable and secure platform for hosting MariaDB databases.

In this comprehensive guide, we’ll walk you through the steps to install MariaDB on AlmaLinux, configure it for production use, and verify its operation. Whether you’re a beginner or an experienced system administrator, this tutorial has everything you need to get started.

Table of Contents

  1. Introduction to MariaDB and AlmaLinux
  2. Prerequisites for Installation
  3. Installing MariaDB on AlmaLinux
    • Installing from Default Repositories
    • Installing the Latest Version
  4. Configuring MariaDB
    • Securing the Installation
    • Editing Configuration Files
  5. Starting and Managing MariaDB Service
  6. Testing the MariaDB Installation
  7. Creating a Database and User
  8. Best Practices for MariaDB on AlmaLinux
  9. Troubleshooting Common Issues
  10. Conclusion

1. Introduction to MariaDB and AlmaLinux

MariaDB originated as a fork of MySQL and has since gained popularity for its enhanced features, community-driven development, and open-source commitment. AlmaLinux, a RHEL-based distribution, provides an excellent platform for hosting MariaDB, whether for small-scale projects or enterprise-level applications.

2. Prerequisites for Installation

Before installing MariaDB on AlmaLinux, ensure the following:

  • A running AlmaLinux instance with root or sudo access.

  • The system is up-to-date:

    sudo dnf update -y

  • A basic understanding of Linux commands and database management.

3. Installing MariaDB on AlmaLinux

There are two main approaches to installing MariaDB on AlmaLinux: using the default repositories or installing the latest version from the official MariaDB repositories.

Installing from Default Repositories

  1. Install MariaDB: The default AlmaLinux repositories often include MariaDB. To install it, run:

    sudo dnf install -y mariadb-server

  2. Verify Installation: Check the installed version:

    mariadb --version

    Output example:

    mariadb 10.3.29

Installing the Latest Version

If you require the latest version, follow these steps:

  1. Add the Official MariaDB Repository: Visit the MariaDB repository page to find the latest repository for your AlmaLinux version. Create a repository file:

    sudo nano /etc/yum.repos.d/mariadb.repo

    Add the following contents (replace 10.11 with the desired version):

    [mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.11/rhel8-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

    Save and exit the file.

  2. Install MariaDB:

    sudo dnf install -y MariaDB-server MariaDB-client

  3. Verify Installation:

    mariadb --version

4. Configuring MariaDB

After installation, some configuration steps are required to secure and optimize MariaDB.

Securing the Installation

Run the security script to improve MariaDB’s security:

sudo mysql_secure_installation

The script will prompt you to:

  • Set the root password.
  • Remove anonymous users.
  • Disallow root login remotely.
  • Remove the test database.
  • Reload privilege tables.

Answer “yes” to these prompts to ensure optimal security.

Editing Configuration Files

The MariaDB configuration file is located at /etc/my.cnf. You can customize settings based on your requirements.

  1. Edit the File:

    sudo nano /etc/my.cnf

  2. Optimize Basic Settings: Add or modify the following for better performance:

    [mysqld]
bind-address = 0.0.0.0
max_connections = 150
query_cache_size = 16M
    • bind-address: Allows remote connections. Change to the server’s IP for security.
    • max_connections: Adjust based on expected traffic.
    • query_cache_size: Optimizes query performance.

  3. Save and Restart MariaDB:

    sudo systemctl restart mariadb

5. Starting and Managing MariaDB Service

MariaDB runs as a service, which you can manage using systemctl.

  • Start MariaDB:

    sudo systemctl start mariadb

  • Enable MariaDB to Start on Boot:

    sudo systemctl enable mariadb

  • Check Service Status:

    sudo systemctl status mariadb

6. Testing the MariaDB Installation

  1. Log in to the MariaDB Shell:

    sudo mysql -u root -p

    Enter the root password set during the mysql_secure_installation process.

  2. Check Server Status: Inside the MariaDB shell, run:

    SHOW VARIABLES LIKE "%version%";

    This displays the server’s version and environment details.

  3. Exit the Shell:

    EXIT;

7. Creating a Database and User

  1. Log in to MariaDB:

    sudo mysql -u root -p

  2. Create a New Database:

    CREATE DATABASE my_database;

  3. Create a User and Grant Permissions:

    CREATE USER 'my_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'%';
FLUSH PRIVILEGES;

  4. Exit the Shell:

    EXIT;

8. Best Practices for MariaDB on AlmaLinux

  1. Regular Updates: Keep MariaDB and AlmaLinux updated:

    sudo dnf update -y

  2. Automate Backups: Use tools like mysqldump or mariabackup for regular backups:

    mysqldump -u root -p my_database > my_database_backup.sql

  3. Secure Remote Connections: Use SSL/TLS for encrypted connections to the database.

  4. Monitor Performance: Utilize monitoring tools like MySQLTuner to optimize the database’s performance:

    perl mysqltuner.pl

  5. Set Resource Limits: Configure resource usage to avoid overloading the system.

9. Troubleshooting Common Issues

  • MariaDB Fails to Start:

    • Check the logs for errors:

      sudo tail -f /var/log/mariadb/mariadb.log

    • Verify the configuration file syntax.

  • Access Denied Errors:

    • Ensure proper user privileges and authentication:

      SHOW GRANTS FOR 'my_user'@'%';

  • Remote Connection Issues:

    • Verify bind-address in /etc/my.cnf is set correctly.

    • Ensure the firewall allows MariaDB traffic:

      sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

10. Conclusion

Installing MariaDB on AlmaLinux is a straightforward process, whether you use the default repositories or opt for the latest version. Once installed, securing and configuring MariaDB is essential to ensure optimal performance and security. By following this guide, you now have a functional MariaDB setup on AlmaLinux, ready for use in development or production environments. Regular maintenance, updates, and monitoring will help you keep your database system running smoothly for years to come.

7 - How to Set Up MariaDB Over SSL/TLS on AlmaLinux

This guide provides a comprehensive walkthrough to set up MariaDB over SSL/TLS on AlmaLinux.

Securing database connections is a critical aspect of modern database administration. Using SSL/TLS (Secure Sockets Layer / Transport Layer Security) to encrypt connections between MariaDB servers and their clients is essential to protect sensitive data in transit. AlmaLinux, a stable and secure RHEL-based distribution, is an excellent platform for hosting MariaDB with SSL/TLS enabled.

This guide provides a comprehensive walkthrough to set up MariaDB over SSL/TLS on AlmaLinux. By the end, you’ll have a secure MariaDB setup capable of encrypted client-server communication.

Table of Contents

  1. Introduction to SSL/TLS in MariaDB
  2. Prerequisites
  3. Installing MariaDB on AlmaLinux
  4. Generating SSL/TLS Certificates
  5. Configuring MariaDB for SSL/TLS
  6. Configuring Clients for SSL/TLS
  7. Testing the SSL/TLS Configuration
  8. Enforcing SSL/TLS Connections
  9. Troubleshooting Common Issues
  10. Conclusion

1. Introduction to SSL/TLS in MariaDB

SSL/TLS ensures secure communication between MariaDB servers and clients by encrypting data in transit. This prevents eavesdropping, data tampering, and man-in-the-middle attacks. Key benefits include:

  • Data Integrity: Ensures data is not tampered with during transmission.
  • Confidentiality: Encrypts sensitive data such as credentials and query results.
  • Authentication: Verifies the server and optionally the client’s identity.

2. Prerequisites

Before starting, ensure you have:

  1. AlmaLinux Installed: A running instance of AlmaLinux with root or sudo access.

  2. MariaDB Installed: MariaDB server installed and running on AlmaLinux.

  3. Basic Knowledge: Familiarity with Linux commands and MariaDB operations.

  4. OpenSSL Installed: Used to generate SSL/TLS certificates:

    sudo dnf install -y openssl

3. Installing MariaDB on AlmaLinux

If MariaDB is not already installed, follow these steps:

  1. Install MariaDB:

    sudo dnf install -y mariadb-server mariadb

  2. Start and Enable the Service:

    sudo systemctl start mariadb
sudo systemctl enable mariadb

  3. Secure MariaDB Installation:

    sudo mysql_secure_installation

    Follow the prompts to set a root password, remove anonymous users, and disallow remote root login.

4. Generating SSL/TLS Certificates

To enable SSL/TLS, MariaDB requires server and client certificates. These can be self-signed or issued by a Certificate Authority (CA).

Step 1: Create a Directory for Certificates

Create a directory to store the certificates:

sudo mkdir /etc/mysql/ssl
sudo chmod 700 /etc/mysql/ssl

Step 2: Generate a Private Key for the Server

openssl genrsa -out /etc/mysql/ssl/server-key.pem 2048

Step 3: Create a Certificate Signing Request (CSR)

openssl req -new -key /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-csr.pem

Provide the required information (e.g., Common Name should match the server’s hostname).

Step 4: Generate the Server Certificate

openssl x509 -req -in /etc/mysql/ssl/server-csr.pem -signkey /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-cert.pem -days 365

Step 5: Create the CA Certificate

Generate a CA certificate to sign client certificates:

openssl req -newkey rsa:2048 -nodes -keyout /etc/mysql/ssl/ca-key.pem -x509 -days 365 -out /etc/mysql/ssl/ca-cert.pem

Step 6: Set Permissions

Ensure the certificates and keys are owned by the MariaDB user:

sudo chown -R mysql:mysql /etc/mysql/ssl
sudo chmod 600 /etc/mysql/ssl/*.pem

5. Configuring MariaDB for SSL/TLS

Step 1: Edit the MariaDB Configuration File

Modify /etc/my.cnf to enable SSL/TLS:

sudo nano /etc/my.cnf

Add the following under the [mysqld] section:

[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Step 2: Restart MariaDB

Restart MariaDB to apply the changes:

sudo systemctl restart mariadb

6. Configuring Clients for SSL/TLS

To connect securely, MariaDB clients must trust the server’s certificate and optionally present their own.

  1. Copy the ca-cert.pem file to the client machine:

    scp /etc/mysql/ssl/ca-cert.pem user@client-machine:/path/to/ca-cert.pem

  2. Use the mysql client to connect securely:

    mysql --host=<server_ip> --user=<username> --password --ssl-ca=/path/to/ca-cert.pem

7. Testing the SSL/TLS Configuration

  1. Check SSL Status on the Server: Log in to MariaDB and verify SSL is enabled:

    SHOW VARIABLES LIKE 'have_ssl';

    Output:

    +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+

  2. Verify Connection Encryption: Use the following query to check if the connection is encrypted:

    SHOW STATUS LIKE 'Ssl_cipher';

    A non-empty result confirms encryption.

8. Enforcing SSL/TLS Connections

To enforce SSL/TLS, update the user privileges:

  1. Log in to MariaDB:

    sudo mysql -u root -p

  2. Require SSL for a User:

    GRANT ALL PRIVILEGES ON *.* TO 'secure_user'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

  3. Test the Configuration: Try connecting without SSL. It should fail.

9. Troubleshooting Common Issues

SSL Handshake Error

  • Cause: Incorrect certificate or key permissions.

  • Solution: Verify ownership and permissions:

    sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/*.pem

Connection Refused

  • Cause: Firewall blocking MariaDB’s port.

  • Solution: Open the port in the firewall:

    sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

Client Cannot Verify Certificate

  • Cause: Incorrect CA certificate on the client.
  • Solution: Ensure the client uses the correct ca-cert.pem.

10. Conclusion

Setting up MariaDB over SSL/TLS on AlmaLinux enhances the security of your database by encrypting all communications between the server and its clients. With this guide, you’ve learned to generate SSL certificates, configure MariaDB for secure connections, and enforce SSL/TLS usage. Regularly monitor and update certificates to maintain a secure database environment.

By following these steps, you can confidently deploy a secure MariaDB instance, safeguarding your data against unauthorized access and network-based threats.

8 - How to Create MariaDB Backup on AlmaLinux

This guide walks you through different methods to create MariaDB backups on AlmaLinux, covering both logical and physical backups, and provides insights into best practices to ensure data integrity and security.

Backing up your database is a critical task for any database administrator. Whether for disaster recovery, migration, or simply safeguarding data, a robust backup strategy ensures the security and availability of your database. MariaDB, a popular open-source database, provides multiple tools and methods to back up your data effectively. AlmaLinux, a reliable and secure Linux distribution, serves as an excellent platform for hosting MariaDB and managing backups.

This guide walks you through different methods to create MariaDB backups on AlmaLinux, covering both logical and physical backups, and provides insights into best practices to ensure data integrity and security.

Table of Contents

  1. Why Backups Are Essential
  2. Prerequisites
  3. Backup Types in MariaDB
    • Logical Backups
    • Physical Backups
  4. Tools for MariaDB Backups
    • mysqldump
    • mariabackup
    • File-System Level Backups
  5. Creating MariaDB Backups
    • Using mysqldump
    • Using mariabackup
    • Using File-System Level Backups
  6. Automating Backups with Cron Jobs
  7. Verifying and Restoring Backups
  8. Best Practices for MariaDB Backups
  9. Troubleshooting Common Backup Issues
  10. Conclusion

1. Why Backups Are Essential

A backup strategy ensures that your database remains resilient against data loss due to hardware failures, human errors, malware attacks, or other unforeseen events. Regular backups allow you to:

  • Recover data during accidental deletions or corruption.
  • Protect against ransomware attacks.
  • Safeguard business continuity during system migrations or upgrades.
  • Support auditing or compliance requirements by archiving historical data.

2. Prerequisites

Before creating MariaDB backups on AlmaLinux, ensure you have:

  1. MariaDB Installed: A working MariaDB setup.
  2. Sufficient Disk Space: Adequate storage for backup files.
  3. User Privileges: Administrative privileges (root or equivalent) to access and back up databases.
  4. Backup Directory: A dedicated directory to store backups.

3. Backup Types in MariaDB

MariaDB offers two primary types of backups:

Logical Backups

  • Export database schemas and data as SQL statements.
  • Ideal for small to medium-sized databases.
  • Can be restored on different MariaDB or MySQL versions.

Physical Backups

  • Copy the database files directly at the file system level.
  • Suitable for large databases or high-performance use cases.
  • Includes metadata and binary logs for consistency.

4. Tools for MariaDB Backups

mysqldump

  • A built-in tool for logical backups.
  • Exports databases to SQL files.

mariabackup

  • A robust tool for physical backups.
  • Ideal for large databases with transaction log support.

File-System Level Backups

  • Directly copies database files.
  • Requires MariaDB to be stopped during the backup process.

5. Creating MariaDB Backups

Using mysqldump

Step 1: Back Up a Single Database

mysqldump -u root -p database_name > /backup/database_name.sql

Step 2: Back Up Multiple Databases

mysqldump -u root -p --databases db1 db2 db3 > /backup/multiple_databases.sql

Step 3: Back Up All Databases

mysqldump -u root -p --all-databases > /backup/all_databases.sql

Step 4: Compressed Backup

mysqldump -u root -p database_name | gzip > /backup/database_name.sql.gz

Using mariabackup

mariabackup is a powerful tool for creating consistent physical backups.

Step 1: Install mariabackup

sudo dnf install -y MariaDB-backup

Step 2: Perform a Full Backup

mariabackup --backup --target-dir=/backup/full_backup --user=root --password=yourpassword

Step 3: Prepare the Backup for Restoration

mariabackup --prepare --target-dir=/backup/full_backup

Step 4: Incremental Backups

  • First, take a full backup as a base:

    mariabackup --backup --target-dir=/backup/base_backup --user=root --password=yourpassword

  • Then, create incremental backups:

    mariabackup --backup --incremental-basedir=/backup/base_backup --target-dir=/backup/incremental_backup --user=root --password=yourpassword

Using File-System Level Backups

File-system level backups are simple but require downtime.

Step 1: Stop MariaDB

sudo systemctl stop mariadb

Step 2: Copy the Data Directory

sudo cp -r /var/lib/mysql /backup/mysql_backup

Step 3: Start MariaDB

sudo systemctl start mariadb

6. Automating Backups with Cron Jobs

You can automate backups using cron jobs to ensure consistency and reduce manual effort.

Step 1: Open the Cron Editor

crontab -e

Step 2: Add a Daily Backup Job

0 2 * * * mysqldump -u root -p'yourpassword' --all-databases | gzip > /backup/all_databases_$(date +\%F).sql.gz

Step 3: Save and Exit

7. Verifying and Restoring Backups

Verify Backup Integrity

  • Check the size of backup files:

    ls -lh /backup/

  • Test restoration in a staging environment.

Restore Logical Backups

  1. Restore a single database:

    mysql -u root -p database_name < /backup/database_name.sql

  2. Restore all databases:

    mysql -u root -p < /backup/all_databases.sql

Restore Physical Backups

  1. Stop MariaDB:

    sudo systemctl stop mariadb

  2. Replace the data directory:

    sudo cp -r /backup/mysql_backup/* /var/lib/mysql/
sudo chown -R mysql:mysql /var/lib/mysql/

  3. Start MariaDB:

    sudo systemctl start mariadb

8. Best Practices for MariaDB Backups

  1. Schedule Regular Backups:

    • Use cron jobs for daily or weekly backups.

  2. Verify Backups:

    • Regularly test restoration to ensure backups are valid.

  3. Encrypt Sensitive Data:

    • Use tools like gpg to encrypt backup files.

  4. Store Backups Off-Site:

    • Use cloud storage or external drives for disaster recovery.

  5. Monitor Backup Status:

    • Use monitoring tools or scripts to ensure backups run as expected.

9. Troubleshooting Common Backup Issues

Backup Fails with “Access Denied”

  • Ensure the backup user has sufficient privileges:

    GRANT ALL PRIVILEGES ON *.* TO 'backup_user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Storage Issues

  • Check disk space using:

    df -h

Slow Backups

  • Optimize the mysqldump command with parallel exports:

    mysqldump --single-transaction --quick --lock-tables=false

10. Conclusion

Creating regular MariaDB backups on AlmaLinux is an essential practice to ensure data availability and security. Whether using logical backups with mysqldump, physical backups with mariabackup, or file-system level copies, the right method depends on your database size and recovery requirements. By automating backups, verifying their integrity, and adhering to best practices, you can maintain a resilient database system capable of recovering from unexpected disruptions.

With this guide, you’re equipped to implement a reliable backup strategy for MariaDB on AlmaLinux, safeguarding your valuable data for years to come.

9 - How to Create MariaDB Replication on AlmaLinux

This guide provides a step-by-step walkthrough to configure MariaDB replication on AlmaLinux.

MariaDB, an open-source relational database management system, provides powerful replication features that allow you to maintain copies of your databases on separate servers. Replication is crucial for ensuring high availability, load balancing, and disaster recovery in production environments. By using AlmaLinux, a robust and secure RHEL-based Linux distribution, you can set up MariaDB replication for an efficient and resilient database infrastructure.

This guide provides a step-by-step walkthrough to configure MariaDB replication on AlmaLinux, helping you create a Main-Replica setup where changes on the Main database are mirrored on one or more Replica servers.

Table of Contents

  1. What is MariaDB Replication?
  2. Prerequisites
  3. Understanding Main-Replica Replication
  4. Installing MariaDB on AlmaLinux
  5. Configuring the Main Server
  6. Configuring the Replica Server
  7. Testing the Replication Setup
  8. Monitoring and Managing Replication
  9. Troubleshooting Common Issues
  10. Conclusion

1. What is MariaDB Replication?

MariaDB replication is a process that enables one database server (the Main) to replicate its data to one or more other servers (the Replicas). Common use cases include:

  • High Availability: Minimize downtime by using Replicas as failover systems.
  • Load Balancing: Distribute read operations to Replica servers to reduce the Main server’s load.
  • Data Backup: Maintain an up-to-date copy of the database for backup or recovery.

2. Prerequisites

Before setting up MariaDB replication on AlmaLinux, ensure the following:

  1. AlmaLinux Installed: At least two servers (Main and Replica) running AlmaLinux.
  2. MariaDB Installed: MariaDB installed on both the Main and Replica servers.
  3. Network Connectivity: Both servers can communicate with each other over the network.
  4. User Privileges: Access to root or sudo privileges on both servers.
  5. Firewall Configured: Allow MariaDB traffic on port 3306.

3. Understanding Main-Replica Replication

  • Main: Handles all write operations and logs changes in a binary log file.
  • Replica: Reads the binary log from the Main and applies the changes to its own database.

Replication can be asynchronous (default) or semi-synchronous, depending on the configuration.

4. Installing MariaDB on AlmaLinux

Install MariaDB on both the Main and Replica servers:

  1. Add the MariaDB Repository:

    sudo dnf install -y https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo mariadb_repo_setup --mariadb-server-version=10.11

  2. Install MariaDB:

    sudo dnf install -y mariadb-server mariadb

  3. Enable and Start MariaDB:

    sudo systemctl enable mariadb
sudo systemctl start mariadb

  4. Secure MariaDB: Run the security script:

    sudo mysql_secure_installation

    Follow the prompts to set a root password, remove anonymous users, and disallow remote root login.

5. Configuring the Main Server

Step 1: Enable Binary Logging

  1. Open the MariaDB configuration file:

    sudo nano /etc/my.cnf

  2. Add the following lines under the [mysqld] section:

    [mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
    • server-id=1: Assigns a unique ID to the Main server.
    • log-bin: Enables binary logging for replication.
    • binlog-format=ROW: Recommended format for replication.

  3. Save and exit the file, then restart MariaDB:

    sudo systemctl restart mariadb

Step 2: Create a Replication User

  1. Log in to the MariaDB shell:

    sudo mysql -u root -p

  2. Create a replication user with appropriate privileges:

    CREATE USER 'replicator'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

  3. Check the binary log position:

    SHOW MASTER STATUS;

    Output example:

    +------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      120 |              |                  |
+------------------+----------+--------------+------------------+

    Note the File and Position values; they will be used in the Replica configuration.

6. Configuring the Replica Server

Step 1: Set Up Replica Configuration

  1. Open the MariaDB configuration file:

    sudo nano /etc/my.cnf

  2. Add the following lines under the [mysqld] section:

    [mysqld]
server-id=2
relay-log=mysql-relay-bin
    • server-id=2: Assigns a unique ID to the Replica server.
    • relay-log: Stores the relay logs for replication.

  3. Save and exit the file, then restart MariaDB:

    sudo systemctl restart mariadb

Step 2: Connect the Replica to the Main

  1. Log in to the MariaDB shell:

    sudo mysql -u root -p

  2. Configure the replication parameters:

    CHANGE MASTER TO
    MASTER_HOST='master_server_ip',
    MASTER_USER='replicator',
    MASTER_PASSWORD='secure_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=120;

    Replace:

    • master_server_ip with the IP of the main server.
    • MASTER_LOG_FILE and MASTER_LOG_POS with the values from the Main.

  3. Start the replication process:

    START SLAVE;

  4. Verify the replication status:

    SHOW SLAVE STATUS\G;

    Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes.

7. Testing the Replication Setup

  1. Create a Test Database on the Main:

    CREATE DATABASE replication_test;

  2. Verify on the Replica: Check if the database appears on the Replica:

    SHOW DATABASES;

    The replication_test database should be present.

8. Monitoring and Managing Replication

Monitor Replication Status

  • On the Replica server, check the replication status:

    SHOW SLAVE STATUS\G;

Pause or Resume Replication

  • Pause replication:

    STOP SLAVE;

  • Resume replication:

    START SLAVE;

Resynchronize a Replica

  • Rebuild the Replica by copying the Main’s data using mysqldump or mariabackup and reconfigure replication.

9. Troubleshooting Common Issues

Replica Not Connecting to Main

  • Check Firewall Rules: Ensure the Main allows MariaDB traffic on port 3306:

    sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

Replication Lag

  • Monitor the Seconds_Behind_Master value in the Replica status and optimize the Main’s workload if needed.

Binary Log Not Enabled

  • Verify the log-bin parameter is set in the Main’s configuration file.

10. Conclusion

MariaDB replication on AlmaLinux is a powerful way to enhance database performance, scalability, and reliability. By setting up a Main-Replica replication, you can distribute database operations efficiently, ensure high availability, and prepare for disaster recovery scenarios. Regular monitoring and maintenance of the replication setup will keep your database infrastructure robust and resilient.

With this guide, you’re equipped to implement MariaDB replication on AlmaLinux, enabling a reliable and scalable database system for your organization.

10 - How to Create a MariaDB Galera Cluster on AlmaLinux

In this guide, we’ll walk you through the process of setting up a MariaDB Galera Cluster on AlmaLinux.

MariaDB Galera Cluster is a powerful solution for achieving high availability, scalability, and fault tolerance in your database environment. By creating a Galera Cluster, you enable a multi-master replication setup where all nodes in the cluster can process both read and write requests. This eliminates the single point of failure and provides real-time synchronization across nodes.

AlmaLinux, a community-driven RHEL-based Linux distribution, is an excellent platform for hosting MariaDB Galera Cluster due to its reliability, security, and performance.

In this guide, we’ll walk you through the process of setting up a MariaDB Galera Cluster on AlmaLinux, ensuring a robust database infrastructure capable of meeting high-availability requirements.

Table of Contents

  1. What is a Galera Cluster?
  2. Benefits of Using MariaDB Galera Cluster
  3. Prerequisites
  4. Installing MariaDB on AlmaLinux
  5. Configuring the First Node
  6. Adding Additional Nodes to the Cluster
  7. Starting the Cluster
  8. Testing the Cluster
  9. Best Practices for Galera Cluster Management
  10. Troubleshooting Common Issues
  11. Conclusion

1. What is a Galera Cluster?

A Galera Cluster is a synchronous multi-master replication solution for MariaDB. Unlike traditional master-slave setups, all nodes in a Galera Cluster are equal, and changes on one node are instantly replicated to the others.

Key features:

  • High Availability: Ensures continuous availability of data.
  • Scalability: Distributes read and write operations across multiple nodes.
  • Data Consistency: Synchronous replication ensures data integrity.

2. Benefits of Using MariaDB Galera Cluster

  • Fault Tolerance: If one node fails, the cluster continues to operate without data loss.
  • Load Balancing: Spread database traffic across multiple nodes for improved performance.
  • Real-Time Updates: Changes are immediately replicated to all nodes.
  • Ease of Management: Single configuration for all nodes simplifies administration.

3. Prerequisites

Before proceeding, ensure the following:

  1. AlmaLinux Instances: At least three servers running AlmaLinux for redundancy.
  2. MariaDB Installed: The same version of MariaDB installed on all nodes.
  3. Network Configuration: All nodes can communicate with each other over a private network.
  4. Firewall Rules: Allow MariaDB traffic on the required ports:
    • 3306: MariaDB service.
    • 4567: Galera replication traffic.
    • 4568: Incremental State Transfer (IST) traffic.
    • 4444: State Snapshot Transfer (SST) traffic.

Update and configure all servers:

sudo dnf update -y
sudo hostnamectl set-hostname <hostname>

4. Installing MariaDB on AlmaLinux

Install MariaDB on all nodes:

  1. Add the MariaDB Repository:

    sudo dnf install -y https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo mariadb_repo_setup --mariadb-server-version=10.11

  2. Install MariaDB Server:

    sudo dnf install -y mariadb-server

  3. Enable and Start MariaDB:

    sudo systemctl enable mariadb
sudo systemctl start mariadb

  4. Secure MariaDB: Run the security script:

    sudo mysql_secure_installation

    Follow the prompts to set a root password, remove anonymous users, and disable remote root login.

5. Configuring the First Node

  1. Edit the MariaDB Configuration File: Open the configuration file:

    sudo nano /etc/my.cnf.d/galera.cnf

  2. Add the Galera Configuration: Replace <node_ip> and <cluster_name> with your values:

    [galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name="my_galera_cluster"
wsrep_cluster_address="gcomm://<node1_ip>,<node2_ip>,<node3_ip>"
wsrep_node_name="node1"
wsrep_node_address="<node1_ip>"
wsrep_sst_method=rsync

    Key parameters:

    • wsrep_on: Enables Galera replication.
    • wsrep_provider: Specifies the Galera library.
    • wsrep_cluster_name: Sets the name of your cluster.
    • wsrep_cluster_address: Lists the IP addresses of all cluster nodes.
    • wsrep_node_name: Specifies the node’s name.
    • wsrep_sst_method: Determines the synchronization method (e.g., rsync).

  3. Allow Galera Ports in the Firewall:

    sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --permanent --add-port=4567/tcp
sudo firewall-cmd --permanent --add-port=4568/tcp
sudo firewall-cmd --permanent --add-port=4444/tcp
sudo firewall-cmd --reload

6. Adding Additional Nodes to the Cluster

Repeat the same steps for the other nodes, with slight modifications:

  1. Edit /etc/my.cnf.d/galera.cnf on each node.
  2. Update the wsrep_node_name and wsrep_node_address parameters for each node.

For example, on the second node:

wsrep_node_name="node2"
wsrep_node_address="<node2_ip>"

On the third node:

wsrep_node_name="node3"
wsrep_node_address="<node3_ip>"

7. Starting the Cluster

  1. Bootstrap the First Node: On the first node, start the Galera Cluster:

    sudo galera_new_cluster

    Check the logs to verify the cluster has started:

    sudo journalctl -u mariadb

  2. Start MariaDB on Other Nodes: On the second and third nodes, start MariaDB normally:

    sudo systemctl start mariadb

  3. Verify Cluster Status: Log in to MariaDB on any node and check the cluster size:

    SHOW STATUS LIKE 'wsrep_cluster_size';

    Output example:

    +--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

8. Testing the Cluster

  1. Create a Test Database: On any node, create a test database:

    CREATE DATABASE galera_test;

  2. Check Replication: Log in to other nodes and verify the database exists:

    SHOW DATABASES;

9. Best Practices for Galera Cluster Management

  1. Use an Odd Number of Nodes: To avoid split-brain scenarios, use an odd number of nodes (e.g., 3, 5).

  2. Monitor Cluster Health: Use SHOW STATUS to monitor variables like wsrep_cluster_status and wsrep_cluster_size.

  3. Back Up Data: Regularly back up your data using tools like mysqldump or mariabackup.

  4. Avoid Large Transactions: Large transactions can slow down synchronization.

  5. Secure Communication: Use SSL/TLS to encrypt Galera replication traffic.

10. Troubleshooting Common Issues

Cluster Fails to Start

  • Check Logs: Look at /var/log/mariadb/mariadb.log for errors.
  • Firewall Rules: Ensure required ports are open on all nodes.

Split-Brain Scenarios

  • Reboot the cluster with a quorum node as the bootstrap:

    sudo galera_new_cluster

Slow Synchronization

  • Use rsync or xtrabackup for faster state snapshot transfers (SST).

11. Conclusion

Setting up a MariaDB Galera Cluster on AlmaLinux is a powerful way to achieve high availability, scalability, and fault tolerance in your database environment. By following the steps in this guide, you can create a robust multi-master replication cluster capable of handling both read and write traffic seamlessly.

With proper monitoring, backup strategies, and security configurations, your MariaDB Galera Cluster will provide a reliable and resilient foundation for your applications.

11 - How to Install phpMyAdmin on MariaDB on AlmaLinux

This comprehensive guide walks you through the process of installing and configuring phpMyAdmin on AlmaLinux with a MariaDB database server.

phpMyAdmin is a popular web-based tool that simplifies the management of MySQL and MariaDB databases. It provides an intuitive graphical user interface (GUI) for performing tasks such as creating, modifying, and deleting databases, tables, and users without the need to execute SQL commands manually. If you are running MariaDB on AlmaLinux, phpMyAdmin can significantly enhance your database administration workflow.

This comprehensive guide walks you through the process of installing and configuring phpMyAdmin on AlmaLinux with a MariaDB database server.

Table of Contents

  1. Introduction to phpMyAdmin
  2. Prerequisites
  3. Installing MariaDB on AlmaLinux
  4. Installing phpMyAdmin
  5. Configuring phpMyAdmin
  6. Securing phpMyAdmin
  7. Accessing phpMyAdmin
  8. Troubleshooting Common Issues
  9. Best Practices for phpMyAdmin on AlmaLinux
  10. Conclusion

1. Introduction to phpMyAdmin

phpMyAdmin is a PHP-based tool designed to manage MariaDB and MySQL databases through a web browser. It allows database administrators to perform a variety of tasks, such as:

  • Managing databases, tables, and users.
  • Running SQL queries.
  • Importing and exporting data.
  • Setting permissions and privileges.

2. Prerequisites

Before installing phpMyAdmin, ensure the following:

  1. AlmaLinux Server: A working AlmaLinux instance with root or sudo access.
  2. MariaDB Installed: A functioning MariaDB server.
  3. LAMP Stack Installed: Apache, MariaDB, and PHP are required for phpMyAdmin to work.
  4. Basic Knowledge: Familiarity with Linux commands and MariaDB administration.

3. Installing MariaDB on AlmaLinux

If MariaDB is not already installed, follow these steps:

  1. Add the MariaDB Repository:

    sudo dnf install -y https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo mariadb_repo_setup --mariadb-server-version=10.11

  2. Install MariaDB Server:

    sudo dnf install -y mariadb-server

  3. Start and Enable MariaDB:

    sudo systemctl start mariadb
sudo systemctl enable mariadb

  4. Secure MariaDB Installation:

    sudo mysql_secure_installation

    Follow the prompts to set a root password, remove anonymous users, and disable remote root login.

4. Installing phpMyAdmin

Step 1: Install Apache and PHP

If you don’t have Apache and PHP installed:

  1. Install Apache:

    sudo dnf install -y httpd
sudo systemctl start httpd
sudo systemctl enable httpd

  2. Install PHP and Required Extensions:

    sudo dnf install -y php php-mysqlnd php-json php-mbstring
sudo systemctl restart httpd

Step 2: Install phpMyAdmin

  1. Add the EPEL Repository: phpMyAdmin is included in the EPEL repository:

    sudo dnf install -y epel-release

  2. Install phpMyAdmin:

    sudo dnf install -y phpMyAdmin

5. Configuring phpMyAdmin

Step 1: Configure Apache for phpMyAdmin

  1. Open the phpMyAdmin Apache configuration file:

    sudo nano /etc/httpd/conf.d/phpMyAdmin.conf

  2. By default, phpMyAdmin is restricted to localhost. To allow access from other IP addresses, modify the file:

    Replace:

    Require ip 127.0.0.1
Require ip ::1

    With:

    Require all granted

  3. Save and exit the file.

Step 2: Restart Apache

After modifying the configuration, restart Apache:

sudo systemctl restart httpd

6. Securing phpMyAdmin

Step 1: Set Up Firewall Rules

To allow access to the Apache web server, open port 80 (HTTP) or port 443 (HTTPS):

sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --permanent --add-service=https
sudo firewall-cmd --reload

Step 2: Configure Additional Authentication

You can add an extra layer of security by enabling basic HTTP authentication:

  1. Create a password file:

    sudo htpasswd -c /etc/phpMyAdmin/.htpasswd admin

  2. Edit the phpMyAdmin configuration file to include authentication:

    sudo nano /etc/httpd/conf.d/phpMyAdmin.conf

    Add the following lines:

    <Directory "/usr/share/phpMyAdmin">
    AuthType Basic
    AuthName "Restricted Access"
    AuthUserFile /etc/phpMyAdmin/.htpasswd
    Require valid-user
</Directory>

  3. Restart Apache:

    sudo systemctl restart httpd

Step 3: Use SSL/TLS for Secure Connections

To encrypt communication, enable SSL:

  1. Install the mod_ssl module:

    sudo dnf install -y mod_ssl

  2. Restart Apache:

    sudo systemctl restart httpd

7. Accessing phpMyAdmin

To access phpMyAdmin:

  1. Open a web browser and navigate to:

    http://<server-ip>/phpMyAdmin

    Replace <server-ip> with your server’s IP address.

  2. Log in using your MariaDB credentials.

8. Troubleshooting Common Issues

Issue: Access Denied for Root User

  • Cause: By default, phpMyAdmin prevents root login for security.
  • Solution: Use a dedicated database user with the necessary privileges.

Issue: phpMyAdmin Not Loading

  • Cause: PHP extensions might be missing.

  • Solution: Ensure required extensions are installed:

    sudo dnf install -y php-mbstring php-json php-xml
sudo systemctl restart httpd

Issue: Forbidden Access Error

  • Cause: Apache configuration restricts access.
  • Solution: Verify the phpMyAdmin configuration file and adjust Require directives.

9. Best Practices for phpMyAdmin on AlmaLinux

  1. Restrict Access: Limit access to trusted IP addresses in /etc/httpd/conf.d/phpMyAdmin.conf.
  2. Create a Dedicated User: Avoid using the root account for database management.
  3. Regular Updates: Keep phpMyAdmin, MariaDB, and Apache updated to address vulnerabilities.
  4. Enable SSL: Always use HTTPS to secure communication.
  5. Backup Configuration Files: Regularly back up your database and phpMyAdmin configuration.

10. Conclusion

Installing phpMyAdmin on AlmaLinux with a MariaDB database provides a powerful yet user-friendly way to manage databases through a web interface. By following the steps in this guide, you’ve set up phpMyAdmin, secured it with additional layers of protection, and ensured it runs smoothly on your AlmaLinux server.

With phpMyAdmin, you can efficiently manage your MariaDB databases, perform administrative tasks, and improve your productivity. Regular maintenance and adherence to best practices will keep your database environment secure and robust for years to come.