MariaDB LAMP Stack Setup - Complete Guide

⚡ Quick Start - One Line Install

                    sudo dnf install -y mariadb-server mariadb php-mysqlnd python3-PyMySQL perl-DBD-MySQL && sudo systemctl enable --now mariadb && sudo mysql_secure_installation
                

📋 System Requirements

Target Systems

Oracle Enterprise Linux 8 (OEL8)
Red Hat Enterprise Linux 8 (RHEL8)
CentOS 8 / Rocky Linux 8 / AlmaLinux 8

📦 Required Packages

MariaDB Core

mariadb-server MariaDB server daemon

mariadb MariaDB client tools (mysql, mysqldump, etc.)

mariadb-common Common files for server and client

mariadb-libs Shared libraries for MariaDB

PHP Integration

php-mysqlnd MySQL Native Driver for PHP (preferred)

php-pdo PHP Data Objects (PDO) interface

Python Integration

python3-PyMySQL Pure Python MySQL client library

python3-mysql Alternative MySQL connector (optional)

Perl Integration

perl-DBD-MySQL Perl DBI driver for MySQL/MariaDB

perl-DBI Database independent interface (dependency)

Optional but Recommended

mariadb-backup MariaDB Backup (mariabackup) tool

mariadb-devel Development files for compiling against MariaDB

mariadb-gssapi-server GSSAPI authentication plugin

🔧 Installation Steps

Step 1: Update System

System Update
sudo dnf update -y

Step 2: Install MariaDB and Language Connectors

Complete Installation Command

                        sudo dnf install -y \
    mariadb-server \
    mariadb \
    mariadb-common \
    mariadb-backup \
    php-mysqlnd \
    php-pdo \
    python3-PyMySQL \
    perl-DBD-MySQL \
    perl-DBI
                    

Step 3: Enable and Start MariaDB

Service Management
# Enable MariaDB to start on boot
sudo systemctl enable mariadb

# Start MariaDB service
sudo systemctl start mariadb

# Check status
sudo systemctl status mariadb

# Verify MariaDB is listening
sudo ss -tlnp | grep 3306

Step 4: Secure Installation

Security Hardening
sudo mysql_secure_installation

💡 Security Recommendations

Set root password: YES
Remove anonymous users: YES
Disallow root login remotely: YES (unless needed)
Remove test database: YES
Reload privilege tables: YES

Step 5: Verify Installation

Version Check
# Check MariaDB version
mysql --version

# Or login and check
mysql -u root -p -e "SELECT VERSION();"

⚙️ Configuration Files

Main Configuration File /etc/my.cnf.d/mariadb-server.cnf

                    # MariaDB Server Configuration
# Best practices for web applications

[mysqld]
# Basic Settings
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

# Character Set (UTF-8 for international support)
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Connection Settings
max_connections=150
max_allowed_packet=64M
connect_timeout=10
wait_timeout=28800
interactive_timeout=28800

# Buffer Pool (adjust based on available RAM)
# Rule of thumb: 70-80% of available RAM for dedicated DB server
# For shared LAMP server: 25-40% of RAM
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1

# InnoDB Settings
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=256M
innodb_log_buffer_size=16M

# Query Cache (deprecated but still useful for read-heavy workloads)
query_cache_type=1
query_cache_size=64M
query_cache_limit=2M

# Logging (disable in production for performance)
general_log=0
slow_query_log=1
slow_query_log_file=/var/log/mariadb/slow-query.log
long_query_time=2

# Binary Logging (for replication and point-in-time recovery)
log_bin=/var/log/mariadb/mysql-bin
expire_logs_days=7
max_binlog_size=100M

# Performance Schema (monitoring)
performance_schema=ON

# Table Cache
table_open_cache=2000
table_definition_cache=1000

# Thread Cache
thread_cache_size=50

# Temporary Tables
tmp_table_size=64M
max_heap_table_size=64M

# Networking
bind-address=127.0.0.1    # Change to 0.0.0.0 for remote access
port=3306

[mysql]
# Client defaults
default-character-set=utf8mb4

[client]
port=3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8mb4
                

⚠️ Configuration Notes

Adjust innodb_buffer_pool_size based on your server's RAM
Keep bind-address=127.0.0.1 for security unless remote access is required
Disable general_log in production (significant performance impact)
After changing config, restart MariaDB: sudo systemctl restart mariadb

💾 Initial Database Setup

Create Database and User

SQL Commands
-- Login as root
mysql -u root -p

-- Create database
CREATE DATABASE webapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user (localhost only)
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- Grant privileges
GRANT ALL PRIVILEGES ON webapp_db.* TO 'webapp_user'@'localhost';

-- For remote access (specific IP)
CREATE USER 'webapp_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON webapp_db.* TO 'webapp_user'@'192.168.1.%';

-- Flush privileges
FLUSH PRIVILEGES;

-- Verify users
SELECT User, Host FROM mysql.user;

-- Exit
exit;

🔌 Language Integration Examples

PHP Connection Examples

MySQLi (Object-Oriented) db_connect_mysqli.php

                    <?php
// MySQLi Object-Oriented Connection
$servername = "localhost";
$username = "webapp_user";
$password = "StrongPassword123!";
$database = "webapp_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully using MySQLi\n";

// Set charset to UTF-8
$conn->set_charset("utf8mb4");

// Example query
$sql = "SELECT VERSION() as version";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $row = $result->fetch_assoc();
    echo "MariaDB Version: " . $row["version"] . "\n";
}

// Close connection
$conn->close();
?>
                

PDO (Recommended for Modern PHP) db_connect_pdo.php

                    <?php
// PDO Connection (Best Practice)
$host = 'localhost';
$db = 'webapp_db';
$user = 'webapp_user';
$pass = 'StrongPassword123!';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully using PDO\n";
    
    // Example prepared statement (secure)
    $stmt = $pdo->query('SELECT VERSION() as version');
    $version = $stmt->fetch();
    echo "MariaDB Version: " . $version['version'] . "\n";
    
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>
                

Python Connection Examples

PyMySQL Example db_connect.py

                    #!/usr/bin/env python3
# PyMySQL Connection Example

import pymysql
from pymysql.cursors import DictCursor

# Connection parameters
config = {
    'host': 'localhost',
    'user': 'webapp_user',
    'password': 'StrongPassword123!',
    'database': 'webapp_db',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}

try:
    # Create connection
    connection = pymysql.connect(**config)
    print("Connected successfully using PyMySQL")
    
    with connection:
        with connection.cursor() as cursor:
            # Example query
            cursor.execute("SELECT VERSION() as version")
            result = cursor.fetchone()
            print(f"MariaDB Version: {result['version']}")
            
            # Example INSERT with parameters (secure)
            sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
            cursor.execute(sql, ('John Doe', 'john@example.com'))
        
        # Commit changes
        connection.commit()
        
except pymysql.Error as e:
    print(f"Error: {e}")
finally:
    if connection:
        connection.close()
        print("Connection closed")
                

Context Manager Pattern (Best Practice) db_context.py

                    #!/usr/bin/env python3
# Context Manager for Database Connections

import pymysql
from contextlib import contextmanager

@contextmanager
def get_db_connection():
    connection = pymysql.connect(
        host='localhost',
        user='webapp_user',
        password='StrongPassword123!',
        database='webapp_db',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    try:
        yield connection
    finally:
        connection.close()

# Usage
with get_db_connection() as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM users LIMIT 10")
        users = cursor.fetchall()
        for user in users:
            print(user)
                

Perl Connection Examples

DBI Connection Example db_connect.pl

                    #!/usr/bin/perl
# Perl DBI MariaDB Connection Example

use strict;
use warnings;
use DBI;

# Connection parameters
my $host = "localhost";
my $database = "webapp_db";
my $user = "webapp_user";
my $password = "StrongPassword123!";
my $dsn = "DBI:mysql:database=$database;host=$host;charset=utf8mb4";

# Connect to database
my $dbh = DBI->connect($dsn, $user, $password, {
    RaiseError => 1,
    AutoCommit => 1,
    PrintError => 0,
    mysql_enable_utf8mb4 => 1,
}) or die "Could not connect: $DBI::errstr";

print "Connected successfully using DBI\n";

# Example query
my $sth = $dbh->prepare("SELECT VERSION() as version");
$sth->execute();

while (my $row = $sth->fetchrow_hashref()) {
    print "MariaDB Version: $row->{version}\n";
}

# Example prepared statement (secure)
my $insert = $dbh->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$insert->execute('Jane Doe', 'jane@example.com');

# Disconnect
$sth->finish();
$dbh->disconnect();
print "Connection closed\n";
                

Error Handling Best Practices db_error_handling.pl

                    #!/usr/bin/perl
# Robust Error Handling Example

use strict;
use warnings;
use DBI;

my $dsn = "DBI:mysql:database=webapp_db;host=localhost;charset=utf8mb4";
my $user = "webapp_user";
my $password = "StrongPassword123!";

my $dbh;
eval {
    $dbh = DBI->connect($dsn, $user, $password, {
        RaiseError => 1,
        AutoCommit => 0,  # Transaction support
        PrintError => 0,
        mysql_enable_utf8mb4 => 1,
    });
    
    # Begin transaction
    $dbh->begin_work();
    
    # Your queries here
    my $sth = $dbh->prepare("SELECT * FROM users WHERE id = ?");
    $sth->execute(1);
    
    while (my $row = $sth->fetchrow_hashref()) {
        print "User: $row->{name}\n";
    }
    
    # Commit transaction
    $dbh->commit();
    print "Transaction committed\n";
};

if ($@) {
    # Error occurred, rollback
    print "Error: $@\n";
    eval { $dbh->rollback() } if $dbh;
}

# Always disconnect
$dbh->disconnect() if $dbh;
                

🧪 Test Connection Scripts

Quick Connection Test

test_all_connections.sh
#!/bin/bash
# Test all language connections to MariaDB

echo "Testing MariaDB Connections..."
echo "================================"
echo

# Test PHP MySQLi
echo "Testing PHP MySQLi..."
php -r '
$conn = new mysqli("localhost", "webapp_user", "StrongPassword123!", "webapp_db");
if ($conn->connect_error) die("Failed: " . $conn->connect_error);
echo "✓ PHP MySQLi: Connected\n";
$conn->close();
'

# Test PHP PDO
echo "Testing PHP PDO..."
php -r '
try {
    $pdo = new PDO("mysql:host=localhost;dbname=webapp_db", "webapp_user", "StrongPassword123!");
    echo "✓ PHP PDO: Connected\n";
} catch (PDOException $e) {
    echo "✗ PHP PDO Failed: " . $e->getMessage() . "\n";
}
'

# Test Python
echo "Testing Python PyMySQL..."
python3 -c "
import pymysql
try:
    conn = pymysql.connect(host='localhost', user='webapp_user', password='StrongPassword123!', database='webapp_db')
    print('✓ Python PyMySQL: Connected')
    conn.close()
except Exception as e:
    print(f'✗ Python Failed: {e}')
"

# Test Perl
echo "Testing Perl DBI..."
perl -MDBI -e '
my $dbh = DBI->connect("DBI:mysql:database=webapp_db;host=localhost", "webapp_user", "StrongPassword123!", {RaiseError=>1});
if ($dbh) { print "✓ Perl DBI: Connected\n"; $dbh->disconnect(); }
'

echo
echo "Connection tests complete!"

🔥 Firewall Configuration

For Remote Access (if needed)

Firewall Rules
# Allow MariaDB through firewall (port 3306)
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

# Or allow specific port
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# Verify
sudo firewall-cmd --list-all

⚠️ Security Warning

Only open port 3306 if you need remote database access. For web applications, the database should typically only be accessible locally (127.0.0.1).

💾 Backup & Maintenance

Backup Scripts

backup_database.sh
#!/bin/bash
# MariaDB Backup Script

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="webapp_db"
DB_USER="webapp_user"
DB_PASS="StrongPassword123!"

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup using mysqldump
mysqldump -u $DB_USER -p$DB_PASS \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Keep only last 7 days of backups
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"

Automated Backup with Cron

Setup Daily Backup
# Edit crontab
sudo crontab -e

# Add this line for daily 2 AM backup
0 2 * * * /usr/local/bin/backup_database.sh >> /var/log/mysql_backup.log 2>&1

⚡ Performance Tuning

Monitor Performance

Performance Monitoring Commands
# Show current connections
mysql -u root -p -e "SHOW PROCESSLIST;"

# Show status variables
mysql -u root -p -e "SHOW STATUS;"

# Show InnoDB status
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

# Check slow queries
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query%';"

# View current configuration
mysql -u root -p -e "SHOW VARIABLES;"

📊 Key Performance Metrics to Monitor

Threads_connected: Current connections
Threads_running: Active queries
Questions: Total queries executed
Slow_queries: Queries exceeding long_query_time
Innodb_buffer_pool_read_requests: Read requests
Innodb_buffer_pool_reads: Disk reads (should be low)

🔍 Troubleshooting

Common Issues and Solutions

Issue	Solution
Can't connect to local MySQL server	`sudo systemctl status mariadb` Check if service is running
Access denied for user	Verify credentials and host permissions `SELECT User,Host FROM mysql.user;`
Too many connections	Increase `max_connections` in config Check for connection leaks in application
Slow queries	Enable slow query log Analyze and add indexes `EXPLAIN` your queries
Out of memory	Reduce `innodb_buffer_pool_size` Check `tmp_table_size` and `max_heap_table_size`

Log Files Location

Important Log Files
# Error log
/var/log/mariadb/mariadb.log

# Slow query log
/var/log/mariadb/slow-query.log

# Binary logs
/var/log/mariadb/mysql-bin.*

# View recent errors
sudo tail -f /var/log/mariadb/mariadb.log

# Check log file size
sudo du -sh /var/log/mariadb/*

📚 Additional Resources

Useful Commands Reference

                    # Service management
sudo systemctl start mariadb
sudo systemctl stop mariadb
sudo systemctl restart mariadb
sudo systemctl reload mariadb
sudo systemctl status mariadb

# Reset root password (if forgotten)
sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
exit;
sudo systemctl restart mariadb

# Import/Export databases
# Export
mysqldump -u username -p database_name > backup.sql

# Import
mysql -u username -p database_name < backup.sql

# Optimize all tables
mysqlcheck -u root -p --optimize --all-databases

# Repair tables
mysqlcheck -u root -p --repair --all-databases
                

💡 Best Practices Summary

Always use prepared statements to prevent SQL injection
Use UTF-8 (utf8mb4) character encoding
Regular backups are essential (automate with cron)
Monitor slow query log regularly
Keep MariaDB updated with security patches
Use connection pooling in production applications
Limit remote access; use SSH tunneling when needed
Set appropriate buffer pool size (not too large!)
Use InnoDB engine for transactional support
Test your backups regularly

📦 Package Versions (OEL8/RHEL8)

Default MariaDB version in RHEL8/OEL8 repos: 10.3.x / 10.5.x

To install newer versions (10.11 or 11.x), use official MariaDB repositories:
https://mariadb.org/download/