Database Module (MariaDB)

Complete guide to deploying and managing the MariaDB database with high availability, automated backups, and monitoring.

🏗️ Overview

The database module provides a production-ready MariaDB deployment with high availability, automated backups, and comprehensive monitoring. It’s optimized for SaaS applications with built-in scaling capabilities.

Architecture Components

MariaDB Deployment:
├── Primary Database Pod (Read/Write)
├── Secondary Database Pod (Read-only)
├── Persistent Storage (SSD)
├── Automated Backups
├── Monitoring & Metrics
└── High Availability Setup

🚀 Features

High Availability

Primary-Secondary Replication: Automatic failover capability
Data Persistence: SSD-backed persistent volumes
Health Monitoring: Comprehensive health checks
Zero-Downtime Updates: Rolling update strategy

Performance Optimization

InnoDB Tuning: Optimized for SaaS workloads
Connection Pooling: Efficient connection management
Query Optimization: Slow query monitoring
Memory Configuration: Optimal buffer pool settings

Security Features

Encrypted Connections: SSL/TLS for client connections
User Management: Role-based access control
Network Policies: Restricted pod-to-pod access
Backup Encryption: Encrypted backup storage

📦 Deployment Configuration

Helmfile Configuration

The database is deployed using Helmfile for environment management:

repositories:
  - name: bitnami
    url: https://charts.bitnami.com/bitnami

releases:
  - name: mariadb
    namespace: mariadb
    chart: bitnami/mariadb
    version: "11.4.2"
    values:
      - values.yaml

Core Configuration Values

architecture: replication

auth:
  rootPassword: "OklrXSFLzL"
  username: "myproject"
  password: "lPOJihokkzr"
  database: "myproject"
  replicationUser: "replicator"
  replicationPassword: "replication123"

primary:
  persistence:
    enabled: true
    storageClass: "fast-ssd"
    size: 100Gi

  resources:
    requests:
      memory: 2Gi
      cpu: 1000m
    limits:
      memory: 4Gi
      cpu: 2000m

secondary:
  replicaCount: 1
  persistence:
    enabled: true
    size: 100Gi

  resources:
    requests:
      memory: 1Gi
      cpu: 500m
    limits:
      memory: 2Gi
      cpu: 1000m

Custom Configuration

# MariaDB server configuration
configuration: |-
  [mysqld]
  max_allowed_packet=268435456
  innodb_buffer_pool_size=1G
  innodb_flush_log_at_trx_commit=1
  innodb_file_per_table=1
  innodb_log_buffer_size=64M
  innodb_log_file_size=512M
  query_cache_type=0
  query_cache_size=0
  slow_query_log=1
  long_query_time=2.0
  max_connections=1000
  wait_timeout=28800
  interactive_timeout=28800
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

🔄 Backup & Recovery

Automated Backup Configuration

# Backup job configuration
backup:
  enabled: true
  cronjob:
    schedule: "0 2 * * *" # Daily at 2 AM
    historyLimit: 7
    concurrencyPolicy: Replace

  persistence:
    enabled: true
    size: 500Gi
    storageClass: "standard"

Manual Backup Commands

# Create manual backup
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysqldump -u root -p --all-databases --single-transaction > backup.sql

# Backup specific database
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysqldump -u root -p myproject > myproject-backup.sql

# Backup with gzip compression
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysqldump -u root -p myproject | gzip > myproject-backup.sql.gz

Backup Using Task Runner

# Using the included Taskfile
cd iac/modules/cert-manager/mariadb
task backup

# Restore from backup
task restore BACKUP_FILE=myproject-20240110-backup.sql

Point-in-Time Recovery

# Enable binary logging for PITR
configuration: |-
  [mysqld]
  log-bin=mysql-bin
  binlog_format=ROW
  expire_logs_days=7
  sync_binlog=1

📊 Monitoring & Metrics

Health Checks

# Liveness probe
livenessProbe:
  exec:
    command:
      - /bin/bash
      - -ec
      - mysqladmin status -u root -p$MARIADB_ROOT_PASSWORD
  initialDelaySeconds: 120
  periodSeconds: 10

# Readiness probe
readinessProbe:
  exec:
    command:
      - /bin/bash
      - -ec
      - mysqladmin status -u root -p$MARIADB_ROOT_PASSWORD
  initialDelaySeconds: 30
  periodSeconds: 5

Performance Monitoring

# Monitor database performance
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "SHOW PROCESSLIST;"

# Check slow queries
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;"

# Monitor replication status
kubectl exec -it mariadb-secondary-0 -n mariadb -- \
  mysql -u root -p -e "SHOW SLAVE STATUS\G"

Database Size Monitoring

# Check database sizes
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "
    SELECT
      table_schema AS 'Database',
      ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
    FROM information_schema.tables
    GROUP BY table_schema;"

# Check table sizes
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p myproject -e "
    SELECT
      table_name AS 'Table',
      ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
    FROM information_schema.TABLES
    WHERE table_schema = 'myproject'
    ORDER BY (data_length + index_length) DESC;"

🚀 Deployment

Deploy Database Module

# Navigate to module directory
cd iac/modules/cert-manager/mariadb

# Deploy using Helmfile
helmfile apply

# Verify deployment
kubectl get pods -n mariadb
kubectl get services -n mariadb

Using Task Runner

# Deploy database
task deploy

# Check status
task status

# View logs
task logs

Verify Deployment

# Check pod status
kubectl get pods -n mariadb -l app.kubernetes.io/name=mariadb

# Test database connection
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "SELECT 1;"

# Verify replication
kubectl exec -it mariadb-secondary-0 -n mariadb -- \
  mysql -u root -p -e "SHOW SLAVE STATUS\G"

🔧 Maintenance Operations

Scaling Operations

# Scale secondary replicas
kubectl patch statefulset mariadb-secondary -n mariadb \
  --patch='{"spec":{"replicas":2}}'

# Verify scaling
kubectl get pods -n mariadb | grep secondary

Update Operations

# Rolling update
helmfile apply

# Monitor update progress
kubectl rollout status statefulset/mariadb-primary -n mariadb
kubectl rollout status statefulset/mariadb-secondary -n mariadb

User Management

# Connect to database
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p

# Create application user
CREATE USER 'appuser'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myproject.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

# Create read-only user
CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON myproject.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

Database Optimization

# Analyze tables
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p myproject -e "ANALYZE TABLE table_name;"

# Optimize tables
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p myproject -e "OPTIMIZE TABLE table_name;"

# Check for fragmentation
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "
    SELECT
      table_name,
      ROUND(data_length/1024/1024) as data_mb,
      ROUND(data_free/1024/1024) as free_mb
    FROM information_schema.tables
    WHERE table_schema = 'myproject'
    AND data_free > 0;"

🚨 Troubleshooting

Common Issues

1. Connection Problems

# Check service connectivity
kubectl get services -n mariadb

# Test network connectivity
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  nc -zv mariadb-service 3306

# Verify DNS resolution
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  nslookup mariadb-service

2. Replication Issues

# Check replication status
kubectl exec -it mariadb-secondary-0 -n mariadb -- \
  mysql -u root -p -e "SHOW SLAVE STATUS\G"

# Reset replication (if needed)
kubectl exec -it mariadb-secondary-0 -n mariadb -- \
  mysql -u root -p -e "STOP SLAVE; RESET SLAVE; START SLAVE;"

# Check primary binary log position
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "SHOW MASTER STATUS;"

3. Performance Issues

# Check running processes
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "SHOW PROCESSLIST;"

# Check slow query log
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  tail -f /var/log/mysql/slow.log

# Check InnoDB status
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

4. Storage Issues

# Check persistent volume status
kubectl get pv | grep mariadb

# Check storage usage
kubectl exec -it mariadb-primary-0 -n mariadb -- df -h

# Check for disk space alerts
kubectl describe pod mariadb-primary-0 -n mariadb

Recovery Procedures

Emergency Recovery

# Force delete stuck pods
kubectl delete pod mariadb-primary-0 -n mariadb --grace-period=0 --force

# Restore from backup
kubectl exec -it mariadb-primary-0 -n mariadb -- \
  mysql -u root -p < backup.sql

# Rebuild replication
kubectl exec -it mariadb-secondary-0 -n mariadb -- \
  mysql -u root -p -e "
    STOP SLAVE;
    CHANGE MASTER TO
      MASTER_HOST='mariadb-primary',
      MASTER_USER='replicator',
      MASTER_PASSWORD='replication123',
      MASTER_LOG_FILE='mysql-bin.000001',
      MASTER_LOG_POS=4;
    START SLAVE;"

🔒 Security Configuration

SSL/TLS Configuration

# Enable SSL
ssl:
  enabled: true
  certificatesSecret: "mariadb-certs"

# Certificate configuration
configuration: |-
  [mysqld]
  ssl-ca=/opt/bitnami/mariadb/certs/ca.crt
  ssl-cert=/opt/bitnami/mariadb/certs/server.crt
  ssl-key=/opt/bitnami/mariadb/certs/server.key
  require_secure_transport=ON

Network Security

# Network policy for database access
networkPolicy:
  enabled: true
  allowExternal: false
  ingressRules:
    primaryAccessOnlyFrom:
      enabled: true
      namespaceSelector:
        matchLabels:
          name: production
      podSelector:
        matchLabels:
          app.kubernetes.io/name: backend

📝 Configuration Examples

High-Performance Configuration

# High-performance values
primary:
  resources:
    requests:
      memory: 8Gi
      cpu: 4000m
    limits:
      memory: 16Gi
      cpu: 8000m

configuration: |-
  [mysqld]
  innodb_buffer_pool_size=12G
  innodb_buffer_pool_instances=12
  innodb_log_file_size=2G
  innodb_flush_log_at_trx_commit=2
  max_connections=2000

Backup-Optimized Configuration

# Enhanced backup configuration
backup:
  enabled: true
  cronjob:
    schedule: "0 */6 * * *" # Every 6 hours
    historyLimit: 14
  persistence:
    size: 1Ti
    storageClass: "backup-storage"

🔄 Maintenance Schedule

Daily Tasks

Monitor replication lag
Check slow query log
Verify backup completion
Monitor disk usage

Weekly Tasks

Analyze table statistics
Review performance metrics
Update database statistics
Check for schema changes

Monthly Tasks

Optimize frequently used tables
Review and rotate logs
Update security configurations
Capacity planning review

📋 Operational Checklist

Pre-Deployment

Post-Deployment

Regular Maintenance

Backend Application - Database integration
Configuration Guide - Database configuration
Security Guide - Database security
Disaster Recovery - Backup strategies

The MariaDB database module provides enterprise-grade data management with high availability and automated operations.