Installing MariaDB on AlmaLinux: Complete Database Setup Guide

MariaDB is a popular open-source relational database management system that serves as a drop-in replacement for MySQL. It offers improved performance, additional storage engines, and enhanced features while maintaining compatibility with MySQL applications. This comprehensive guide walks you through installing, configuring, and optimizing MariaDB on AlmaLinux, ensuring your database server is secure, performant, and production-ready.

Understanding MariaDB

What is MariaDB?

MariaDB is a community-developed fork of MySQL, created by the original MySQL developers after concerns about Oracle’s acquisition of MySQL. It maintains high compatibility with MySQL while offering:

• Enhanced Performance: Improved query optimizer and faster replication
• Additional Storage Engines: Aria, XtraDB, FederatedX, and more
• Advanced Features: Virtual columns, dynamic columns, and better JSON support
• Active Development: Regular updates and security patches
• Open Source: Truly open-source with community governance

MariaDB vs MySQL

Pre-Installation Requirements

System Requirements

# Check system resources
free -h
df -h
nproc

# Recommended minimums:
# RAM: 2GB (4GB+ for production)
# Storage: 20GB+ (depending on data)
# CPU: 2+ cores

Prerequisites

# Update system
sudo dnf update -y

# Install dependencies
sudo dnf install -y wget curl nano

# Check SELinux status
getenforce

# Check firewall status
sudo firewall-cmd --state

Planning Considerations

1. Storage Planning

   • Database size estimation
   • Growth projections
   • Backup storage requirements

2. Performance Requirements

   • Expected concurrent connections
   • Query complexity
   • Read/write ratio

3. Security Requirements

   • Network access needs
   • Encryption requirements
   • Compliance considerations

Installing MariaDB

Method 1: Installing from AlmaLinux Repository

# Check available versions
sudo dnf module list mariadb

# Install MariaDB (latest stable)
sudo dnf module install mariadb:10.5 -y

# Or install specific version
sudo dnf install mariadb-server mariadb -y

# Verify installation
mariadb --version

Method 2: Installing from MariaDB Repository

# Create MariaDB repository file
sudo nano /etc/yum.repos.d/MariaDB.repo

# Add repository configuration
[mariadb]
name = MariaDB
baseurl = https://rpm.mariadb.org/10.11/rhel/$releasever/$basearch
gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1

# Install MariaDB
sudo dnf install MariaDB-server MariaDB-client -y

Starting MariaDB Service

# Enable MariaDB to start on boot
sudo systemctl enable mariadb

# Start MariaDB service
sudo systemctl start mariadb

# Check service status
sudo systemctl status mariadb

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

Initial Configuration

Running Initial Setup Script

# Run secure installation script
sudo mysql_secure_installation

# Follow prompts:
# - Enter current root password (none by default)
# - Set root password
# - Remove anonymous users
# - Disallow root login remotely
# - Remove test database
# - Reload privilege tables

Basic Configuration File

# Main configuration file
sudo nano /etc/my.cnf.d/server.cnf

# Add basic settings
[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
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Network Settings
bind-address=127.0.0.1
port=3306
max_connections=100

# InnoDB Settings
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_file_per_table=1

# Query Cache (disabled by default in newer versions)
query_cache_type=0
query_cache_size=0

# Logging
general_log=0
slow_query_log=1
slow_query_log_file=/var/log/mariadb/slow-queries.log
long_query_time=2

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

Creating Custom Configuration

# Create custom configuration
sudo nano /etc/my.cnf.d/custom.cnf

[mysqld]
# Performance Settings
tmp_table_size=64M
max_heap_table_size=64M
join_buffer_size=512K
sort_buffer_size=2M
read_buffer_size=512K
read_rnd_buffer_size=512K

# Safety Settings
max_allowed_packet=64M
max_connect_errors=100
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

# Binary Logging (for replication/point-in-time recovery)
log_bin=/var/log/mariadb/mariadb-bin
binlog_format=ROW
expire_logs_days=10
sync_binlog=1

# Restart MariaDB to apply changes
sudo systemctl restart mariadb

Securing MariaDB

User Security

-- Connect to MariaDB
sudo mysql -u root -p

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Remove remote root access
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- Create administrative user
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongAdminPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Flush privileges
FLUSH PRIVILEGES;

Network Security

# Configure firewall (if allowing remote connections)
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

# Or allow specific IP
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.100" port port="3306" protocol="tcp" accept'
sudo firewall-cmd --reload

# Configure bind address for security
sudo nano /etc/my.cnf.d/server.cnf
# bind-address=127.0.0.1  # Local only
# bind-address=0.0.0.0    # All interfaces (less secure)

SSL/TLS Configuration

# Create SSL certificates directory
sudo mkdir -p /etc/mysql/ssl

# Generate SSL certificates
sudo openssl genrsa 2048 > /etc/mysql/ssl/ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 -key /etc/mysql/ssl/ca-key.pem -out /etc/mysql/ssl/ca.pem

# Create server certificate
sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-req.pem
sudo openssl rsa -in /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-key.pem
sudo openssl x509 -req -in /etc/mysql/ssl/server-req.pem -days 3650 -CA /etc/mysql/ssl/ca.pem -CAkey /etc/mysql/ssl/ca-key.pem -set_serial 01 -out /etc/mysql/ssl/server-cert.pem

# Set permissions
sudo chown -R mysql:mysql /etc/mysql/ssl
sudo chmod 600 /etc/mysql/ssl/*

# Configure MariaDB for SSL
sudo nano /etc/my.cnf.d/ssl.cnf

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

# Restart MariaDB
sudo systemctl restart mariadb

# Verify SSL is enabled
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

Additional Security Measures

-- Enable validate_password plugin
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Set password policy
SET GLOBAL validate_password_length = 12;
SET GLOBAL validate_password_mixed_case_count = 2;
SET GLOBAL validate_password_number_count = 2;
SET GLOBAL validate_password_special_char_count = 2;

-- Enable audit plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';
SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_logging=ON;

Database and User Management

Creating Databases

-- Create database with specific character set
CREATE DATABASE myapp_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Create database with specific options
CREATE DATABASE IF NOT EXISTS production_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
  COMMENT 'Production database for main application';

-- Show databases
SHOW DATABASES;

-- Show database creation statement
SHOW CREATE DATABASE myapp_db;

User Management

-- Create users with different access levels
-- Local user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppPassword123!';

-- Remote user from specific host
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'AppPassword123!';

-- Remote user from subnet
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPassword123!';

-- User with SSL requirement
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'SecurePass123!' REQUIRE SSL;

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

Privilege Management

-- Grant privileges
-- All privileges on specific database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'app_user'@'localhost';

-- Specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_user'@'localhost';

-- Read-only user
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'ReadOnly123!';
GRANT SELECT ON myapp_db.* TO 'readonly_user'@'localhost';

-- Backup user
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'Backup123!';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';

-- Developer user with limited privileges
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'Dev123!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON dev_db.* TO 'dev_user'@'%';

-- Show grants
SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoke privileges
REVOKE ALL PRIVILEGES ON myapp_db.* FROM 'app_user'@'localhost';

-- Remove user
DROP USER 'old_user'@'localhost';

-- Flush privileges
FLUSH PRIVILEGES;

Role-Based Access Control

-- Create roles (MariaDB 10.0.5+)
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant privileges to roles
GRANT SELECT ON myapp_db.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_write';
GRANT ALL ON myapp_db.* TO 'app_admin';

-- Create users and assign roles
CREATE USER 'john'@'localhost' IDENTIFIED BY 'JohnPass123!';
CREATE USER 'jane'@'localhost' IDENTIFIED BY 'JanePass123!';

GRANT 'app_read' TO 'john'@'localhost';
GRANT 'app_write' TO 'jane'@'localhost';

-- Set default role
SET DEFAULT ROLE 'app_read' FOR 'john'@'localhost';

Configuration Optimization

Memory Configuration

# Calculate optimal settings based on available RAM
# Example for 8GB RAM server

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

[mysqld]
# InnoDB Buffer Pool (50-80% of RAM)
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

# Additional memory pools
innodb_log_buffer_size = 256M
key_buffer_size = 256M

# Per-thread buffers (be careful with these)
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M

# Table cache
table_open_cache = 4000
table_definition_cache = 2000

# Thread configuration
thread_cache_size = 100
thread_stack = 256K

Storage Engine Optimization

# InnoDB optimization
[mysqld]
# File management
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Flushing behavior
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1  # 1 for ACID, 2 for performance
innodb_flush_neighbors = 0  # For SSD

# Redo log
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 256M

# Doublewrite buffer (disable for SSD)
innodb_doublewrite = 1

# Compression (if needed)
innodb_file_format = Barracuda
innodb_compression_level = 6

Query Optimization

# Query cache and optimization
[mysqld]
# Query cache (disabled in MariaDB 10.1.7+)
query_cache_type = 0
query_cache_size = 0

# Optimizer settings
optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on'
optimizer_search_depth = 0

# Join and sort optimization
max_join_size = 1000000000
max_sort_length = 1024
max_length_for_sort_data = 1024

# Temporary tables
tmp_table_size = 128M
max_heap_table_size = 128M

Backup and Recovery

Backup Strategies

1. Logical Backups with mysqldump

# Basic backup
mysqldump -u root -p --all-databases > all_databases_backup.sql

# Single database backup
mysqldump -u root -p myapp_db > myapp_db_backup.sql

# Compressed backup
mysqldump -u root -p --all-databases | gzip > all_databases_backup.sql.gz

# Backup with stored procedures and triggers
mysqldump -u root -p --routines --triggers myapp_db > myapp_db_complete.sql

# Consistent backup for InnoDB
mysqldump -u root -p --single-transaction --all-databases > consistent_backup.sql

# Backup script
#!/bin/bash
# /usr/local/bin/backup_mariadb.sh

BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="backup_user"
MYSQL_PASSWORD="Backup123!"

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup all databases
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --add-drop-database \
  --compress \
  > $BACKUP_DIR/all_databases_$DATE.sql

# Compress backup
gzip $BACKUP_DIR/all_databases_$DATE.sql

# Remove backups older than 7 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

2. Physical Backups with Mariabackup

# Install mariabackup
sudo dnf install MariaDB-backup -y

# Full backup
sudo mariabackup --backup \
  --target-dir=/backup/mariadb/full \
  --user=root \
  --password=YourRootPassword

# Prepare backup
sudo mariabackup --prepare \
  --target-dir=/backup/mariadb/full

# Incremental backup
sudo mariabackup --backup \
  --target-dir=/backup/mariadb/inc1 \
  --incremental-basedir=/backup/mariadb/full \
  --user=root \
  --password=YourRootPassword

Recovery Procedures

# Restore from mysqldump
mysql -u root -p < all_databases_backup.sql

# Restore specific database
mysql -u root -p myapp_db < myapp_db_backup.sql

# Restore compressed backup
gunzip < all_databases_backup.sql.gz | mysql -u root -p

# Restore with mariabackup
# Stop MariaDB
sudo systemctl stop mariadb

# Move old data
sudo mv /var/lib/mysql /var/lib/mysql.old

# Copy backup
sudo mariabackup --copy-back \
  --target-dir=/backup/mariadb/full

# Fix permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Start MariaDB
sudo systemctl start mariadb

Point-in-Time Recovery

# Enable binary logging
sudo nano /etc/my.cnf.d/server.cnf

[mysqld]
log_bin = /var/log/mariadb/mariadb-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1

# Show binary logs
mysql -u root -p -e "SHOW BINARY LOGS;"

# Backup binary logs
mysqlbinlog /var/log/mariadb/mariadb-bin.000001 > binlog_backup.sql

# Restore to specific point in time
mysqlbinlog --stop-datetime="2024-01-01 12:00:00" \
  /var/log/mariadb/mariadb-bin.000001 | mysql -u root -p

Performance Tuning

Performance Analysis

-- Enable performance schema
SET GLOBAL performance_schema = ON;

-- Check slow queries
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Show processlist
SHOW FULL PROCESSLIST;

-- Show engine status
SHOW ENGINE INNODB STATUS\G

-- Check table statistics
SELECT table_schema, table_name, 
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 10;

Index Optimization

-- Find missing indexes
SELECT 
    s.table_schema,
    s.table_name,
    s.index_name,
    s.column_name,
    s.cardinality
FROM information_schema.statistics s
LEFT JOIN information_schema.table_constraints tc 
    ON s.table_schema = tc.table_schema
    AND s.table_name = tc.table_name
    AND s.index_name = tc.constraint_name
WHERE s.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
    AND tc.constraint_name IS NULL
ORDER BY s.cardinality DESC;

-- Analyze tables
ANALYZE TABLE myapp_db.users;

-- Optimize tables
OPTIMIZE TABLE myapp_db.users;

-- Create indexes
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);
CREATE INDEX idx_status_created ON users(status, created_at);

-- Remove unused indexes
DROP INDEX idx_old_column ON users;

Query Optimization

-- Enable query profiling
SET profiling = 1;

-- Run query
SELECT * FROM large_table WHERE status = 'active';

-- Show profiles
SHOW PROFILES;

-- Show detailed profile
SHOW PROFILE FOR QUERY 1;

-- Explain query execution
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Extended explain
EXPLAIN EXTENDED SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- Analyze query execution
ANALYZE SELECT * FROM users WHERE created_at > '2024-01-01';

Connection Pool Tuning

# Configure connection limits
[mysqld]
max_connections = 500
max_user_connections = 50
connection_timeout = 10
interactive_timeout = 28800
wait_timeout = 28800

# Thread pool configuration (MariaDB 5.5+)
thread_handling = pool-of-threads
thread_pool_size = 16
thread_pool_max_threads = 1000
thread_pool_idle_timeout = 60

Monitoring and Maintenance

Built-in Monitoring

-- Server status variables
SHOW GLOBAL STATUS;

-- Specific status
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Innodb%';

-- Server variables
SHOW GLOBAL VARIABLES;

-- Monitor connections
SELECT 
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Monitor table sizes
SELECT 
    table_schema AS 'Database',
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
    ROUND((data_length / 1024 / 1024), 2) AS 'Data (MB)',
    ROUND((index_length / 1024 / 1024), 2) AS 'Index (MB)'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Automated Monitoring Script

#!/bin/bash
# /usr/local/bin/monitor_mariadb.sh

# Configuration
MYSQL_USER="monitor"
MYSQL_PASS="Monitor123!"
ALERT_EMAIL="[email protected]"
LOG_FILE="/var/log/mariadb_monitor.log"

# Function to log
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}

# Check MariaDB service
if ! systemctl is-active --quiet mariadb; then
    log "ERROR: MariaDB service is not running"
    echo "MariaDB service down on $(hostname)" | mail -s "MariaDB Alert" $ALERT_EMAIL
    exit 1
fi

# Check connections
CURRENT_CONNECTIONS=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected';" | grep Threads_connected | awk '{print $2}')
MAX_CONNECTIONS=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW VARIABLES LIKE 'max_connections';" | grep max_connections | awk '{print $2}')
CONNECTION_USAGE=$((CURRENT_CONNECTIONS * 100 / MAX_CONNECTIONS))

if [ $CONNECTION_USAGE -gt 80 ]; then
    log "WARNING: Connection usage at ${CONNECTION_USAGE}%"
fi

# Check slow queries
SLOW_QUERIES=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Slow_queries';" | grep Slow_queries | awk '{print $2}')
if [ $SLOW_QUERIES -gt 100 ]; then
    log "WARNING: High number of slow queries: $SLOW_QUERIES"
fi

# Check disk space
DATA_DIR=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW VARIABLES LIKE 'datadir';" | grep datadir | awk '{print $2}')
DISK_USAGE=$(df -h $DATA_DIR | awk 'NR==2 {print $(NF-1)}' | sed 's/%//')

if [ $DISK_USAGE -gt 85 ]; then
    log "ERROR: Disk usage critical at ${DISK_USAGE}%"
    echo "MariaDB disk usage critical on $(hostname)" | mail -s "MariaDB Alert" $ALERT_EMAIL
fi

log "Monitor check completed - Connections: ${CONNECTION_USAGE}%, Disk: ${DISK_USAGE}%"

Maintenance Tasks

# Create maintenance script
#!/bin/bash
# /usr/local/bin/mariadb_maintenance.sh

MYSQL_USER="maintenance"
MYSQL_PASS="Maintenance123!"
LOG_FILE="/var/log/mariadb_maintenance.log"

echo "[$(date)] Starting maintenance" >> $LOG_FILE

# Analyze tables
mysql -u $MYSQL_USER -p$MYSQL_PASS -e "
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
" | mysql -u $MYSQL_USER -p$MYSQL_PASS

# Optimize fragmented tables
mysql -u $MYSQL_USER -p$MYSQL_PASS -e "
SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
AND data_free > 100*1024*1024
" | mysql -u $MYSQL_USER -p$MYSQL_PASS

# Purge old binary logs
mysql -u $MYSQL_USER -p$MYSQL_PASS -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);"

echo "[$(date)] Maintenance completed" >> $LOG_FILE

# Schedule with cron
# 0 2 * * 0 /usr/local/bin/mariadb_maintenance.sh

Replication Setup

Master-Slave Replication

On Master Server:

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

[mysqld]
server-id = 1
log_bin = /var/log/mariadb/mariadb-bin
binlog_format = ROW
binlog_do_db = myapp_db  # Optional: replicate specific database
expire_logs_days = 10
max_binlog_size = 100M
sync_binlog = 1

# Restart MariaDB
sudo systemctl restart mariadb

-- Create replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;
-- Note the File and Position values

On Slave Server:

# Configure slave
sudo nano /etc/my.cnf.d/replication.cnf

[mysqld]
server-id = 2
relay_log = /var/log/mariadb/relay-bin
read_only = 1
log_slave_updates = 1

# Restart MariaDB
sudo systemctl restart mariadb

-- Configure slave
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='ReplPassword123!',
    MASTER_LOG_FILE='mariadb-bin.000001',
    MASTER_LOG_POS=123;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

Master-Master Replication

# Configure both servers with unique server-id
# Server 1: server-id = 1
# Server 2: server-id = 2

# Enable binary logging on both
# Enable log_slave_updates on both
# Set auto_increment settings to avoid conflicts

# Server 1
[mysqld]
auto_increment_increment = 2
auto_increment_offset = 1

# Server 2
[mysqld]
auto_increment_increment = 2
auto_increment_offset = 2

Common Issues and Troubleshooting

Connection Issues

# Check if MariaDB is listening
sudo ss -tunlp | grep 3306

# Check error log
sudo tail -f /var/log/mariadb/mariadb.log

# Test connection
mysql -u root -p -h localhost

# Check max connections
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -u root -p -e "SHOW STATUS LIKE 'Max_used_connections';"

# Fix "Too many connections" error
mysql -u root -p -e "SET GLOBAL max_connections = 500;"

Performance Issues

# Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mariadb/slow-queries.log';

# Check for table locks
SHOW OPEN TABLES WHERE In_use > 0;

# Check InnoDB status
SHOW ENGINE INNODB STATUS\G

# Find blocking queries
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Corruption Issues

# Check table integrity
CHECK TABLE myapp_db.users;

# Repair MyISAM table
REPAIR TABLE myapp_db.old_table;

# For InnoDB corruption
# 1. Stop MariaDB
sudo systemctl stop mariadb

# 2. Start with innodb_force_recovery
sudo nano /etc/my.cnf.d/recovery.cnf
[mysqld]
innodb_force_recovery = 1

# 3. Start MariaDB and dump data
sudo systemctl start mariadb
mysqldump -u root -p --all-databases > emergency_backup.sql

# 4. Remove recovery mode and restore
# Remove innodb_force_recovery
# Restore from backup

Disk Space Issues

# Check disk usage
df -h /var/lib/mysql

# Find large tables
SELECT 
    table_schema,
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 10;

# Purge binary logs
PURGE BINARY LOGS BEFORE NOW();
PURGE BINARY LOGS TO 'mariadb-bin.000010';

# Clean up general log
SET GLOBAL general_log = 'OFF';
# Truncate log file
# Then re-enable if needed

Integration with Applications

PHP Configuration

// PDO connection
try {
    $dsn = "mysql:host=localhost;dbname=myapp_db;charset=utf8mb4";
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
    ];
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    error_log("Database connection failed: " . $e->getMessage());
}

// MySQLi connection
$mysqli = new mysqli("localhost", $username, $password, "myapp_db");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8mb4");

Python Configuration

# Using PyMySQL
import pymysql

connection = pymysql.connect(
    host='localhost',
    user='app_user',
    password='AppPassword123!',
    database='myapp_db',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# Using SQLAlchemy
from sqlalchemy import create_engine

engine = create_engine(
    'mysql+pymysql://app_user:AppPassword123!@localhost/myapp_db?charset=utf8mb4',
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True
)

Node.js Configuration

// Using mysql2
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'app_user',
    password: 'AppPassword123!',
    database: 'myapp_db',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    charset: 'utf8mb4'
});

// Using with async/await
async function getUsers() {
    const [rows] = await pool.execute('SELECT * FROM users WHERE status = ?', ['active']);
    return rows;
}

Connection Pool Best Practices

# Configure MariaDB for connection pooling
[mysqld]
# Connection limits
max_connections = 600
max_user_connections = 100

# Thread pool (better for many connections)
thread_handling = pool-of-threads
thread_pool_size = 16
thread_pool_max_threads = 1000

# Connection timeout
connect_timeout = 10
interactive_timeout = 28800
wait_timeout = 600

Best Practices

Security Best Practices

1. Regular Updates

# Keep MariaDB updated
sudo dnf update mariadb-server mariadb -y

2. Strong Authentication

• Use strong passwords
• Implement SSL/TLS for remote connections
• Use specific host restrictions
• Enable authentication plugins

3. Principle of Least Privilege

• Create specific users for each application
• Grant only necessary privileges
• Avoid using root for applications
• Regular privilege audits

Performance Best Practices

1. Query Optimization

• Use indexes effectively
• Avoid SELECT *
• Use prepared statements
• Optimize JOIN operations

2. Configuration Tuning

• Size InnoDB buffer pool appropriately
• Configure query cache wisely
• Tune thread pool settings
• Monitor and adjust based on workload

3. Regular Maintenance

• Analyze and optimize tables
• Update statistics
• Monitor slow queries
• Regular performance reviews

Operational Best Practices

1. Backup Strategy

• Regular automated backups
• Test restore procedures
• Off-site backup storage
• Document recovery procedures

2. Monitoring

• Implement comprehensive monitoring
• Set up alerting
• Track performance metrics
• Monitor security events

3. Documentation

• Document configuration changes
• Maintain runbooks
• Document recovery procedures
• Keep architecture diagrams updated

Development Best Practices

1. Schema Design

• Use appropriate data types
• Normalize when appropriate
• Index foreign keys
• Avoid NULL when possible

2. Application Integration

• Use connection pooling
• Handle connection failures gracefully
• Use prepared statements
• Implement query timeouts

Conclusion

MariaDB on AlmaLinux provides a robust, scalable, and secure database platform for modern applications. By following this guide, you’ve learned how to:

• Install and configure MariaDB properly
• Implement comprehensive security measures
• Optimize performance for your workload
• Set up reliable backup and recovery procedures
• Monitor and maintain database health
• Troubleshoot common issues

Remember that database administration is an ongoing process. Regular monitoring, maintenance, and optimization are key to maintaining a healthy and performant database system.