🗄️ AlmaLinux Database Server Setup: Complete MySQL & PostgreSQL Guide

Welcome to the powerful world of database servers on AlmaLinux! 🎉 Think of databases as the digital libraries of the modern world - they store, organize, and protect all the valuable information that makes applications work! Whether you’re building a simple blog, complex web application, or enterprise system, mastering database server setup is absolutely essential! 📚

Database servers might seem complex at first, but they’re actually quite logical and incredibly rewarding to work with! 💪 From installing your first database to configuring high-availability clusters, we’ll learn everything step by step. Get ready to become a database expert and unlock the power of data management! ✨

🤔 Why are Database Servers Important?

Database servers are the backbone of modern applications! Here’s why you should master them:

• 💾 Data Storage: Safely store and organize vast amounts of information
• 🔍 Data Retrieval: Quickly find and access specific data when needed
• 🛡️ Data Security: Protect sensitive information with advanced security features
• 🔄 ACID Compliance: Ensure data integrity with transactions and consistency
• 📈 Scalability: Handle growing data volumes and user loads
• 🚀 Performance: Optimize queries for lightning-fast data access
• 🔐 Access Control: Manage who can see and modify different data
• 💼 Business Intelligence: Enable analytics and reporting for better decisions

🎯 What You Need

Before we start setting up database servers, make sure you have:

✅ AlmaLinux 8 or 9 installed and running ✅ Root or sudo access to install and configure database software ✅ At least 2GB RAM for database operations (4GB+ recommended) ✅ Sufficient disk space for data storage (plan for growth) ✅ Basic SQL knowledge (helpful but we’ll cover basics) ✅ Understanding of networking (ports, connections) ✅ Backup strategy for protecting database content

📝 Understanding Database Concepts

Let’s start by understanding database fundamentals! 🎓

Database Types and Use Cases

# MySQL/MariaDB characteristics:
echo "MySQL/MariaDB:"
echo "- Most popular open-source database"
echo "- Great for web applications"
echo "- Excellent performance for read operations"
echo "- Strong community support"
echo "- Compatible with many applications"

echo ""

# PostgreSQL characteristics:
echo "PostgreSQL:"
echo "- Advanced open-source database"
echo "- ACID compliant with advanced features"
echo "- Excellent for complex queries"
echo "- Strong data integrity"
echo "- JSON and NoSQL capabilities"

# Check system resources for databases
free -h
df -h
# Output: Shows available memory and disk space

Database Server Planning

# Plan database storage
echo "Database storage planning:"
echo "- Data directory: /var/lib/mysql or /var/lib/postgresql"
echo "- Log files: /var/log/mysql or /var/log/postgresql"
echo "- Configuration: /etc/mysql or /etc/postgresql"
echo "- Backup location: /backup/databases"

# Check current processes
ps aux | grep -E "(mysql|postgres)"
# Output: Shows if any database processes are running

# Check network ports
sudo netstat -tlnp | grep -E ':3306|:5432'
# Output: Shows if database ports are in use

🔧 Installing MySQL/MariaDB

MariaDB Installation and Setup

# Install MariaDB server
sudo dnf install mariadb-server mariadb -y
# Output: Installs MariaDB database server

# Start and enable MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb
# Output: Starts MariaDB and enables it at boot

# Check MariaDB status
sudo systemctl status mariadb
# Output: Shows MariaDB service status

# Secure MariaDB installation
sudo mysql_secure_installation
# Output: Interactive security configuration

# Configure firewall for MySQL
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload
# Output: Opens MySQL port (3306)

# Test MariaDB connection
mysql -u root -p -e "SELECT VERSION();"
# Output: Shows MariaDB version

MySQL Configuration

# View MySQL configuration
sudo nano /etc/my.cnf.d/mariadb-server.cnf

# Basic MySQL optimization settings:
[mysqld]
# Basic settings
bind-address = 127.0.0.1
port = 3306
socket = /var/lib/mysql/mysql.sock

# Performance settings
max_connections = 200
key_buffer_size = 256M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

# InnoDB settings
innodb_buffer_pool_size = 1G
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1

# Logging
log_error = /var/log/mariadb/mariadb.log
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
long_query_time = 2

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Create log directory
sudo mkdir -p /var/log/mariadb
sudo chown mysql:mysql /var/log/mariadb

# Restart MariaDB to apply configuration
sudo systemctl restart mariadb
# Output: Restarts MariaDB with new configuration

# Verify configuration
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# Output: Shows current configuration values

🌟 MySQL Database Management

Creating Databases and Users

# Connect to MySQL as root
mysql -u root -p

# Create a new database
CREATE DATABASE webapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# Output: Creates database with UTF-8 support

# Create a database user
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
# Output: Creates new database user

# Grant privileges to user
GRANT ALL PRIVILEGES ON webapp_db.* TO 'webapp_user'@'localhost';
FLUSH PRIVILEGES;
# Output: Grants full access to the database

# Create read-only user
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'ReadOnlyPass456!';
GRANT SELECT ON webapp_db.* TO 'readonly_user'@'localhost';
FLUSH PRIVILEGES;
# Output: Creates user with read-only access

# Show databases and users
SHOW DATABASES;
SELECT User, Host FROM mysql.user;
# Output: Lists databases and users

# Exit MySQL
EXIT;

MySQL Backup and Restore

# Create backup directory
sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql

# Create full database backup
mysqldump -u root -p --all-databases --routines --triggers > /backup/mysql/full_backup_$(date +%Y%m%d_%H%M%S).sql
# Output: Creates complete database backup

# Create specific database backup
mysqldump -u root -p webapp_db > /backup/mysql/webapp_db_backup_$(date +%Y%m%d).sql
# Output: Backs up specific database

# Create compressed backup
mysqldump -u root -p webapp_db | gzip > /backup/mysql/webapp_db_$(date +%Y%m%d).sql.gz
# Output: Creates compressed backup

# Restore database from backup
mysql -u root -p webapp_db < /backup/mysql/webapp_db_backup_20250917.sql
# Output: Restores database from backup file

# Create automated backup script
sudo nano /usr/local/bin/mysql-backup.sh

# Add this content:
#!/bin/bash
# MySQL backup script
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql-backup.log"
MYSQL_USER="root"
RETENTION_DAYS=7

# Function to log messages
log_message() {
    echo "$(date): $1" >> "$LOG_FILE"
}

log_message "Starting MySQL backup"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Full backup
mysqldump -u "$MYSQL_USER" -p --all-databases --routines --triggers \
    --single-transaction --lock-tables=false > "$BACKUP_DIR/full_backup_$DATE.sql"

if [ $? -eq 0 ]; then
    log_message "Full backup completed successfully"

    # Compress backup
    gzip "$BACKUP_DIR/full_backup_$DATE.sql"
    log_message "Backup compressed"

    # Remove old backups
    find "$BACKUP_DIR" -name "full_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    log_message "Old backups cleaned up"
else
    log_message "Backup failed"
fi

log_message "MySQL backup script completed"

# Make script executable
sudo chmod +x /usr/local/bin/mysql-backup.sh

# Schedule daily backups
echo "0 2 * * * /usr/local/bin/mysql-backup.sh" | sudo crontab -

✅ Installing PostgreSQL

PostgreSQL Installation

# Install PostgreSQL
sudo dnf install postgresql-server postgresql-contrib -y
# Output: Installs PostgreSQL server and additional modules

# Initialize PostgreSQL database
sudo postgresql-setup --initdb
# Output: Initializes PostgreSQL data directory

# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql
# Output: Starts PostgreSQL and enables it at boot

# Check PostgreSQL status
sudo systemctl status postgresql
# Output: Shows PostgreSQL service status

# Configure firewall for PostgreSQL
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
# Output: Opens PostgreSQL port (5432)

# Set password for postgres user
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'SecurePassword123!';"
# Output: Sets password for PostgreSQL superuser

PostgreSQL Configuration

# Configure PostgreSQL authentication
sudo nano /var/lib/pgsql/data/pg_hba.conf

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

# Configure PostgreSQL server settings
sudo nano /var/lib/pgsql/data/postgresql.conf

# Key configuration settings:
listen_addresses = 'localhost'
port = 5432
max_connections = 200

# Memory settings
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

# WAL settings
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Query planner
random_page_cost = 1.1
effective_io_concurrency = 200

# Logging
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_min_duration_statement = 1000

# Restart PostgreSQL to apply changes
sudo systemctl restart postgresql
# Output: Restarts PostgreSQL with new configuration

# Test PostgreSQL connection
sudo -u postgres psql -c "SELECT version();"
# Output: Shows PostgreSQL version

🔧 PostgreSQL Database Management

Creating Databases and Users

# Switch to postgres user
sudo -u postgres psql

# Create a new database
CREATE DATABASE webapp_db WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';
# Output: Creates database with UTF-8 encoding

# Create database user
CREATE USER webapp_user WITH PASSWORD 'StrongPassword789!';
# Output: Creates new database user

# Grant privileges to user
GRANT ALL PRIVILEGES ON DATABASE webapp_db TO webapp_user;
# Output: Grants full access to database

# Create read-only user
CREATE USER readonly_user WITH PASSWORD 'ReadOnlyPass321!';
GRANT CONNECT ON DATABASE webapp_db TO readonly_user;
\c webapp_db
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
# Output: Creates user with read-only access

# List databases and users
\l
\du
# Output: Shows databases and users

# Exit PostgreSQL
\q

PostgreSQL Backup and Restore

# Create backup directory
sudo mkdir -p /backup/postgresql
sudo chown postgres:postgres /backup/postgresql

# Create database backup
sudo -u postgres pg_dump webapp_db > /backup/postgresql/webapp_db_backup_$(date +%Y%m%d).sql
# Output: Creates database backup

# Create compressed backup
sudo -u postgres pg_dump webapp_db | gzip > /backup/postgresql/webapp_db_$(date +%Y%m%d).sql.gz
# Output: Creates compressed backup

# Create all databases backup
sudo -u postgres pg_dumpall > /backup/postgresql/all_databases_$(date +%Y%m%d).sql
# Output: Backs up all databases

# Restore database from backup
sudo -u postgres psql webapp_db < /backup/postgresql/webapp_db_backup_20250917.sql
# Output: Restores database from backup

# Create PostgreSQL backup script
sudo nano /usr/local/bin/postgresql-backup.sh

# Add this content:
#!/bin/bash
# PostgreSQL backup script
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/postgresql-backup.log"
RETENTION_DAYS=7

# Function to log messages
log_message() {
    echo "$(date): $1" >> "$LOG_FILE"
}

log_message "Starting PostgreSQL backup"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Create all databases backup
sudo -u postgres pg_dumpall > "$BACKUP_DIR/all_databases_$DATE.sql"

if [ $? -eq 0 ]; then
    log_message "Full backup completed successfully"

    # Compress backup
    gzip "$BACKUP_DIR/all_databases_$DATE.sql"
    log_message "Backup compressed"

    # Individual database backups
    for db in $(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';"); do
        db=$(echo $db | xargs)  # Remove whitespace
        sudo -u postgres pg_dump "$db" | gzip > "$BACKUP_DIR/${db}_$DATE.sql.gz"
        log_message "Backed up database: $db"
    done

    # Remove old backups
    find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
    log_message "Old backups cleaned up"
else
    log_message "Backup failed"
fi

log_message "PostgreSQL backup script completed"

# Make script executable and schedule
sudo chmod +x /usr/local/bin/postgresql-backup.sh
echo "0 3 * * * /usr/local/bin/postgresql-backup.sh" | sudo crontab -

🎮 Quick Examples

Example 1: Complete LAMP Stack with MySQL

# Set up complete LAMP stack
sudo dnf install httpd php php-mysqlnd mariadb-server -y

# Start services
sudo systemctl start httpd mariadb
sudo systemctl enable httpd mariadb

# Secure MySQL
sudo mysql_secure_installation

# Create web application database
mysql -u root -p << 'EOF'
CREATE DATABASE ecommerce_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'ecommerce_user'@'localhost' IDENTIFIED BY 'EcommercePass123!';
GRANT ALL PRIVILEGES ON ecommerce_db.* TO 'ecommerce_user'@'localhost';
FLUSH PRIVILEGES;

USE ecommerce_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO products (name, description, price, stock_quantity) VALUES
('Laptop Computer', 'High-performance laptop for work and gaming', 999.99, 10),
('Wireless Mouse', 'Ergonomic wireless mouse with long battery life', 29.99, 50),
('Mechanical Keyboard', 'RGB mechanical keyboard for gaming', 149.99, 25);

EXIT;
EOF

# Create PHP application
sudo nano /var/www/html/app.php

# Add this content:
<?php
$servername = "localhost";
$username = "ecommerce_user";
$password = "EcommercePass123!";
$dbname = "ecommerce_db";

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "<h1>E-commerce Application</h1>";
    echo "<h2>Products</h2>";

    $stmt = $pdo->query("SELECT * FROM products");
    echo "<table border='1'>";
    echo "<tr><th>ID</th><th>Name</th><th>Description</th><th>Price</th><th>Stock</th></tr>";

    while ($row = $stmt->fetch()) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['name'] . "</td>";
        echo "<td>" . $row['description'] . "</td>";
        echo "<td>$" . $row['price'] . "</td>";
        echo "<td>" . $row['stock_quantity'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

    echo "<h2>Database Statistics</h2>";
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM products");
    $count = $stmt->fetch();
    echo "<p>Total Products: " . $count['count'] . "</p>";

} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

# Test the application
curl http://localhost/app.php
# Output: Shows e-commerce application with product data

Example 2: PostgreSQL Data Analytics Setup

# Set up PostgreSQL for analytics
sudo -u postgres psql << 'EOF'
CREATE DATABASE analytics_db WITH ENCODING 'UTF8';
CREATE USER analytics_user WITH PASSWORD 'AnalyticsPass456!';
GRANT ALL PRIVILEGES ON DATABASE analytics_db TO analytics_user;
\c analytics_db

CREATE TABLE website_visits (
    id SERIAL PRIMARY KEY,
    visitor_ip INET,
    page_url TEXT,
    user_agent TEXT,
    visit_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    session_duration INTERVAL
);

CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product_id INTEGER,
    customer_id INTEGER,
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    total_amount DECIMAL(10,2),
    sale_date DATE DEFAULT CURRENT_DATE,
    region VARCHAR(50)
);

CREATE INDEX idx_visits_time ON website_visits(visit_time);
CREATE INDEX idx_sales_date ON sales_data(sale_date);
CREATE INDEX idx_sales_region ON sales_data(region);

-- Insert sample data
INSERT INTO website_visits (visitor_ip, page_url, user_agent, session_duration) VALUES
('192.168.1.100', '/home', 'Mozilla/5.0 Chrome/91.0', '00:02:30'),
('192.168.1.101', '/products', 'Mozilla/5.0 Firefox/89.0', '00:05:15'),
('192.168.1.102', '/checkout', 'Mozilla/5.0 Safari/14.1', '00:03:45');

INSERT INTO sales_data (product_id, customer_id, quantity, unit_price, total_amount, region) VALUES
(1, 101, 2, 999.99, 1999.98, 'North America'),
(2, 102, 1, 29.99, 29.99, 'Europe'),
(3, 103, 1, 149.99, 149.99, 'Asia');

-- Create analytics views
CREATE VIEW daily_sales AS
SELECT
    sale_date,
    region,
    COUNT(*) as transaction_count,
    SUM(total_amount) as daily_revenue,
    AVG(total_amount) as avg_transaction_value
FROM sales_data
GROUP BY sale_date, region
ORDER BY sale_date DESC;

CREATE VIEW visitor_analytics AS
SELECT
    DATE(visit_time) as visit_date,
    COUNT(*) as unique_visitors,
    AVG(EXTRACT(EPOCH FROM session_duration)) as avg_session_seconds
FROM website_visits
GROUP BY DATE(visit_time)
ORDER BY visit_date DESC;

\q
EOF

# Create analytics query script
cat > /home/$(whoami)/analytics_queries.sql << 'EOF'
-- Daily Sales Report
SELECT * FROM daily_sales WHERE sale_date >= CURRENT_DATE - INTERVAL '7 days';

-- Top Performing Regions
SELECT
    region,
    SUM(total_amount) as total_revenue,
    COUNT(*) as total_transactions
FROM sales_data
GROUP BY region
ORDER BY total_revenue DESC;

-- Website Traffic Analysis
SELECT * FROM visitor_analytics WHERE visit_date >= CURRENT_DATE - INTERVAL '7 days';

-- Revenue Trend Analysis
SELECT
    TO_CHAR(sale_date, 'YYYY-MM') as month,
    SUM(total_amount) as monthly_revenue
FROM sales_data
GROUP BY TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY month;
EOF

# Run analytics queries
sudo -u postgres psql -d analytics_db -f /home/$(whoami)/analytics_queries.sql
# Output: Shows analytics results

Example 3: Database High Availability Setup

# Set up MySQL master-slave replication
# On Master Server:

# Configure master MySQL
sudo nano /etc/my.cnf.d/master.cnf

# Add this content:
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = replication_db

# Restart MySQL
sudo systemctl restart mariadb

# Create replication user
mysql -u root -p << 'EOF'
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'ReplicationPass789!';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

CREATE DATABASE replication_db;
USE replication_db;

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
EOF

# On Slave Server (configure similarly):
sudo nano /etc/my.cnf.d/slave.cnf

# Add this content:
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1

# Create monitoring script for replication
sudo nano /usr/local/bin/replication-monitor.sh

# Add this content:
#!/bin/bash
# MySQL replication monitoring script
LOG_FILE="/var/log/replication-monitor.log"
EMAIL="[email protected]"

# Function to log messages
log_message() {
    echo "$(date): $1" >> "$LOG_FILE"
}

# Check master status
MASTER_STATUS=$(mysql -u root -p -e "SHOW MASTER STATUS\G" 2>/dev/null)
if [ $? -eq 0 ]; then
    log_message "Master server is operational"
else
    log_message "ERROR: Master server connection failed"
    echo "MySQL Master server is down" | mail -s "Database Alert" "$EMAIL"
fi

# Check slave status (run on slave server)
SLAVE_STATUS=$(mysql -u root -p -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Slave_IO_Running")
if echo "$SLAVE_STATUS" | grep -q "Yes"; then
    log_message "Slave replication is running"
else
    log_message "ERROR: Slave replication stopped"
    echo "MySQL Slave replication has stopped" | mail -s "Database Alert" "$EMAIL"
fi

# Check replication lag
LAG=$(mysql -u root -p -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$LAG" -gt 60 ]; then
    log_message "WARNING: Replication lag is $LAG seconds"
    echo "MySQL replication lag is $LAG seconds" | mail -s "Database Warning" "$EMAIL"
fi

log_message "Replication monitoring completed"

# Make script executable and schedule
sudo chmod +x /usr/local/bin/replication-monitor.sh
echo "*/5 * * * * /usr/local/bin/replication-monitor.sh" | crontab -

🚨 Fix Common Problems

Problem 1: Database Won’t Start

Symptoms: Database service fails to start or stops unexpectedly

Solution:

# Check service status and logs
sudo systemctl status mariadb  # For MySQL/MariaDB
sudo systemctl status postgresql  # For PostgreSQL

# View detailed error logs
sudo journalctl -u mariadb -n 50
sudo journalctl -u postgresql -n 50

# Check database error logs
sudo tail -f /var/log/mariadb/mariadb.log
sudo tail -f /var/lib/pgsql/data/log/postgresql-*.log

# Check disk space
df -h /var/lib/mysql
df -h /var/lib/pgsql

# Check file permissions
ls -la /var/lib/mysql/
ls -la /var/lib/pgsql/

# Fix permissions if needed
sudo chown -R mysql:mysql /var/lib/mysql
sudo chown -R postgres:postgres /var/lib/pgsql

# Check configuration syntax
sudo mysqld --help --verbose --validate-config  # MySQL
sudo -u postgres postgres --check-config  # PostgreSQL

# Reset database if necessary (DANGER: data loss)
sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
sudo systemctl start mariadb

Problem 2: Cannot Connect to Database

Symptoms: Connection refused or authentication failures

Solution:

# Check if database is listening
sudo netstat -tlnp | grep :3306  # MySQL
sudo netstat -tlnp | grep :5432  # PostgreSQL

# Test local connection
mysql -u root -p  # MySQL
sudo -u postgres psql  # PostgreSQL

# Check firewall settings
sudo firewall-cmd --list-services
sudo firewall-cmd --list-ports

# Open database ports if needed
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

# Check authentication configuration
sudo cat /etc/my.cnf.d/mariadb-server.cnf  # MySQL
sudo cat /var/lib/pgsql/data/pg_hba.conf  # PostgreSQL

# Reset root password (MySQL)
sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables &
mysql -u root
UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
FLUSH PRIVILEGES;
sudo systemctl restart mariadb

# Reset postgres password (PostgreSQL)
sudo -u postgres psql
ALTER USER postgres PASSWORD 'newpassword';
\q

Problem 3: Database Performance Issues

Symptoms: Slow queries, high CPU usage, or memory problems

Solution:

# Check database performance metrics
mysql -u root -p -e "SHOW PROCESSLIST;"  # MySQL
sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"  # PostgreSQL

# Check slow query log
sudo tail -f /var/log/mariadb/slow.log  # MySQL
sudo -u postgres psql -c "SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;"  # PostgreSQL

# Analyze table performance
mysql -u root -p -e "SHOW TABLE STATUS FROM database_name;"
sudo -u postgres psql -d database_name -c "SELECT * FROM pg_stat_user_tables;"

# Check database size and usage
mysql -u root -p -e "SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'DB Size in MB' FROM information_schema.tables GROUP BY table_schema;"

# Optimize MySQL tables
mysql -u root -p -e "OPTIMIZE TABLE table_name;"

# Analyze PostgreSQL tables
sudo -u postgres psql -d database_name -c "ANALYZE;"
sudo -u postgres psql -d database_name -c "VACUUM ANALYZE;"

# Check and adjust configuration
# MySQL - increase buffer pool size, optimize query cache
# PostgreSQL - adjust shared_buffers, work_mem, effective_cache_size

# Monitor system resources
htop
iostat -x 1

📋 Simple Commands Summary

💡 Tips for Success

Here are proven strategies to master database server setup! 🌟

Best Practices

• 📊 Monitor Performance: Regularly check database performance metrics and slow queries
• 🛡️ Security First: Always secure databases with strong passwords and proper authentication
• 💾 Regular Backups: Implement automated backup strategies with retention policies
• 📈 Plan for Growth: Design databases with scalability and future needs in mind
• 🔧 Optimize Queries: Use indexes effectively and analyze query performance
• 📝 Document Everything: Keep detailed records of database schema and configurations
• 🧪 Test Thoroughly: Test backup/restore procedures and disaster recovery plans
• 🔄 Regular Maintenance: Perform routine maintenance like table optimization and statistics updates

Performance Optimization

• Use appropriate indexes for frequently queried columns 📊
• Configure memory settings based on available system RAM 💾
• Implement connection pooling for high-traffic applications 🔄
• Monitor and optimize slow queries regularly 🔍
• Use read replicas to distribute read workload ⚖️
• Implement proper database normalization 📋
• Regular maintenance and statistics updates 🧹
• Consider partitioning for very large tables 📈

🏆 What You Learned

Congratulations! You’ve mastered database server setup on AlmaLinux! 🎉 Here’s what you can now do:

✅ Install Database Servers: Set up MySQL/MariaDB and PostgreSQL on AlmaLinux ✅ Configure Databases: Optimize settings for performance and security ✅ Manage Users and Permissions: Create databases, users, and set appropriate access controls ✅ Implement Backup Strategies: Set up automated backup and restore procedures ✅ Monitor Performance: Track database performance and identify bottlenecks ✅ Troubleshoot Issues: Diagnose and fix common database problems ✅ Secure Databases: Implement security best practices and access controls ✅ Scale Operations: Configure replication and high availability setups

🎯 Why This Matters

Mastering database server setup is fundamental for modern applications! 🚀 With these skills, you can:

• Build Scalable Applications: Create robust backends that handle growing data demands 📈
• Ensure Data Security: Protect sensitive information with proper database security 🛡️
• Enable Business Intelligence: Support analytics and reporting for better decisions 📊
• Maintain High Availability: Keep critical systems running with minimal downtime ⏰
• Optimize Performance: Deliver fast, responsive applications with efficient data access ⚡
• Support Growth: Scale database systems as businesses and applications expand 🌱

Database servers are the foundation of the digital economy! Whether you’re supporting a small application or enterprise systems, these skills will serve you throughout your career. Remember, data is the new oil, and databases are the refineries that make it useful! ⭐

Excellent work on mastering database server setup on AlmaLinux! You now have the power to build and manage robust, scalable, and secure database systems! 🙌