Database Management

This document provides comprehensive guidance for managing the MySQL database infrastructure supporting the Stratpoint Timesheet Application, including setup, maintenance, optimization, and disaster recovery procedures.

Database Architecture

Database Infrastructure Overview

graph TB
    A[Application Servers] --> B[Load Balancer]
    B --> C[Primary Database]
    C --> D[Read Replica 1]
    C --> E[Read Replica 2]

    F[Backup System] --> G[Daily Backups]
    F --> H[Point-in-Time Recovery]
    F --> I[Cross-Region Backup]

    C --> F
    D --> F
    E --> F

    J[Monitoring] --> K[Performance Metrics]
    J --> L[Health Checks]
    J --> M[Alert System]

    C --> J
    D --> J
    E --> J

Database Configuration

Primary Database Configuration

-- Primary Database Configuration
-- /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# Basic Settings
server-id = 1
bind-address = 0.0.0.0
port = 3306
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

# Character Set and Collation
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4'

# Storage Engine
default-storage-engine = InnoDB
default-table-type = InnoDB

# Connection Settings
max_connections = 500
max_user_connections = 450
max_connect_errors = 1000000
wait_timeout = 28800
interactive_timeout = 28800

# Buffer Pool Settings
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M

# Log Settings
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Performance Settings
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0

# Query Cache (disabled for MySQL 8.0+)
# query_cache_type = 0
# query_cache_size = 0

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# Binary Logging
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 7
max_binlog_size = 1G
sync_binlog = 1

# Replication Settings
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
slave_preserve_commit_order = ON

# Security Settings
local_infile = 0
skip_show_database = 1

Read Replica Configuration

-- Read Replica Configuration
[mysqld]
# Inherit most settings from primary
!include /etc/mysql/conf.d/primary.cnf

# Replica-specific settings
server-id = 2  # Unique for each replica
read_only = 1
super_read_only = 1

# Replica performance
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON
slave_pending_jobs_size_max = 128M

# Relay log settings
relay_log = relay-bin
relay_log_index = relay-bin.index
relay_log_info_repository = TABLE
master_info_repository = TABLE

Database Setup and Initialization

Initial Database Setup

-- Initial database setup script
-- database/setup/01_create_database.sql

-- Create database
CREATE DATABASE IF NOT EXISTS stratpoint_timesheet
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Create application user
CREATE USER IF NOT EXISTS 'timesheet_app'@'%' 
    IDENTIFIED BY 'secure_password_here'
    PASSWORD EXPIRE INTERVAL 90 DAY;

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP, REFERENCES
    ON stratpoint_timesheet.* 
    TO 'timesheet_app'@'%';

-- Create read-only user for reporting
CREATE USER IF NOT EXISTS 'timesheet_readonly'@'%'
    IDENTIFIED BY 'readonly_password_here'
    PASSWORD EXPIRE INTERVAL 90 DAY;

GRANT SELECT ON stratpoint_timesheet.* TO 'timesheet_readonly'@'%';

-- Create backup user
CREATE USER IF NOT EXISTS 'timesheet_backup'@'localhost'
    IDENTIFIED BY 'backup_password_here'
    PASSWORD EXPIRE INTERVAL 180 DAY;

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD
    ON stratpoint_timesheet.*
    TO 'timesheet_backup'@'localhost';

-- Flush privileges
FLUSH PRIVILEGES;

Database Optimization

-- Database optimization script
-- database/optimization/optimize_tables.sql

-- Optimize frequently accessed tables
OPTIMIZE TABLE users;
OPTIMIZE TABLE projects;
OPTIMIZE TABLE timelogs;
OPTIMIZE TABLE project_users;

-- Analyze tables for query optimization
ANALYZE TABLE users;
ANALYZE TABLE projects;
ANALYZE TABLE timelogs;
ANALYZE TABLE project_users;

-- Update table statistics
UPDATE mysql.innodb_table_stats SET last_update = NOW();
UPDATE mysql.innodb_index_stats SET last_update = NOW();

Index Management

-- Index optimization script
-- database/optimization/indexes.sql

-- Performance indexes for timesheet queries
CREATE INDEX IF NOT EXISTS idx_timelogs_user_date 
    ON timelogs(user_id, log_date);

CREATE INDEX IF NOT EXISTS idx_timelogs_project_date 
    ON timelogs(project_id, log_date);

CREATE INDEX IF NOT EXISTS idx_timelogs_approval_status 
    ON timelogs(approval_status, log_date);

CREATE INDEX IF NOT EXISTS idx_timelogs_billable 
    ON timelogs(is_billable, log_date);

-- Composite index for common queries
CREATE INDEX IF NOT EXISTS idx_timelogs_user_project_date 
    ON timelogs(user_id, project_id, log_date);

-- Project-related indexes
CREATE INDEX IF NOT EXISTS idx_projects_client_status 
    ON projects(client_id, status);

CREATE INDEX IF NOT EXISTS idx_projects_status_date 
    ON projects(status, created_at);

-- User-related indexes
CREATE INDEX IF NOT EXISTS idx_users_department_status 
    ON users(department_id, status);

CREATE INDEX IF NOT EXISTS idx_users_manager 
    ON users(manager_id);

-- Check for unused indexes
SELECT 
    s.table_schema,
    s.table_name,
    s.index_name,
    s.cardinality,
    t.table_rows
FROM information_schema.statistics s
JOIN information_schema.tables t 
    ON s.table_schema = t.table_schema 
    AND s.table_name = t.table_name
WHERE s.table_schema = 'stratpoint_timesheet'
    AND s.cardinality < (t.table_rows * 0.1)
    AND s.index_name != 'PRIMARY'
ORDER BY s.cardinality;

Backup and Recovery

Automated Backup Strategy

#!/bin/bash
# scripts/database_backup.sh

set -e

# Configuration
DB_HOST="localhost"
DB_NAME="stratpoint_timesheet"
DB_USER="timesheet_backup"
DB_PASSWORD="${DB_BACKUP_PASSWORD}"
BACKUP_DIR="/backups/mysql"
RETENTION_DAYS=30
S3_BUCKET="stratpoint-timesheet-backups"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Generate backup filename
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/timesheet_backup_$TIMESTAMP.sql"
COMPRESSED_FILE="$BACKUP_FILE.gz"

echo "Starting database backup at $(date)"

# Create full backup
mysqldump \
    --host="$DB_HOST" \
    --user="$DB_USER" \
    --password="$DB_PASSWORD" \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --quick \
    --lock-tables=false \
    --add-drop-database \
    --databases "$DB_NAME" > "$BACKUP_FILE"

# Compress backup
gzip "$BACKUP_FILE"

# Verify backup integrity
if gunzip -t "$COMPRESSED_FILE"; then
    echo "Backup compression verified successfully"
else
    echo "Backup compression verification failed"
    exit 1
fi

# Upload to S3
aws s3 cp "$COMPRESSED_FILE" "s3://$S3_BUCKET/daily/$(basename $COMPRESSED_FILE)"

# Create weekly backup (every Sunday)
if [ $(date +%u) -eq 7 ]; then
    aws s3 cp "$COMPRESSED_FILE" "s3://$S3_BUCKET/weekly/$(basename $COMPRESSED_FILE)"
fi

# Create monthly backup (first day of month)
if [ $(date +%d) -eq 01 ]; then
    aws s3 cp "$COMPRESSED_FILE" "s3://$S3_BUCKET/monthly/$(basename $COMPRESSED_FILE)"
fi

# Clean up old local backups
find "$BACKUP_DIR" -name "timesheet_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Log backup completion
echo "Database backup completed successfully at $(date)"
echo "Backup file: $COMPRESSED_FILE"
echo "File size: $(du -h $COMPRESSED_FILE | cut -f1)"

# Send notification
curl -X POST -H 'Content-type: application/json' \
    --data "{\"text\":\"Database backup completed: $(basename $COMPRESSED_FILE)\"}" \
    "$SLACK_WEBHOOK_URL"

Point-in-Time Recovery

#!/bin/bash
# scripts/point_in_time_recovery.sh

set -e

# Configuration
RECOVERY_TIME="$1"  # Format: YYYY-MM-DD HH:MM:SS
BACKUP_FILE="$2"
BINLOG_DIR="/var/log/mysql"
RECOVERY_DIR="/tmp/recovery"

if [ -z "$RECOVERY_TIME" ] || [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 'YYYY-MM-DD HH:MM:SS' backup_file.sql.gz"
    exit 1
fi

echo "Starting point-in-time recovery to: $RECOVERY_TIME"

# Create recovery directory
mkdir -p "$RECOVERY_DIR"

# Restore from backup
echo "Restoring from backup: $BACKUP_FILE"
gunzip -c "$BACKUP_FILE" | mysql -u root -p

# Find binary logs after backup time
BACKUP_TIME=$(mysql -u root -p -e "SELECT MAX(backup_time) FROM mysql.backup_log;" -s -N)
BINLOGS=$(ls -1 "$BINLOG_DIR"/mysql-bin.* | sort)

# Apply binary logs up to recovery time
for binlog in $BINLOGS; do
    echo "Processing binary log: $binlog"

    mysqlbinlog \
        --start-datetime="$BACKUP_TIME" \
        --stop-datetime="$RECOVERY_TIME" \
        --database="stratpoint_timesheet" \
        "$binlog" > "$RECOVERY_DIR/$(basename $binlog).sql"

    # Apply if file is not empty
    if [ -s "$RECOVERY_DIR/$(basename $binlog).sql" ]; then
        mysql -u root -p stratpoint_timesheet < "$RECOVERY_DIR/$(basename $binlog).sql"
    fi
done

echo "Point-in-time recovery completed"

Performance Monitoring

Database Performance Monitoring

-- Performance monitoring queries
-- database/monitoring/performance_queries.sql

-- Check slow queries
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY query_time DESC
LIMIT 10;

-- Check table sizes
SELECT 
    table_schema,
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
    table_rows
FROM information_schema.tables
WHERE table_schema = 'stratpoint_timesheet'
ORDER BY (data_length + index_length) DESC;

-- Check index usage
SELECT 
    t.table_schema,
    t.table_name,
    s.index_name,
    s.cardinality,
    ROUND(s.cardinality / t.table_rows * 100, 2) AS selectivity_percent
FROM information_schema.statistics s
JOIN information_schema.tables t 
    ON s.table_schema = t.table_schema 
    AND s.table_name = t.table_name
WHERE t.table_schema = 'stratpoint_timesheet'
    AND t.table_rows > 0
ORDER BY selectivity_percent DESC;

-- Check connection usage
SELECT 
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.processlist
WHERE db = 'stratpoint_timesheet'
ORDER BY time DESC;

-- Check InnoDB status
SHOW ENGINE INNODB STATUS;

-- Check buffer pool usage
SELECT 
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.global_status 
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') /
        (SELECT VARIABLE_VALUE FROM information_schema.global_status 
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') * 100, 2
    ) AS buffer_pool_utilization_percent;

Automated Performance Monitoring Script

#!/bin/bash
# scripts/monitor_database_performance.sh

# Configuration
DB_HOST="localhost"
DB_USER="timesheet_app"
DB_PASSWORD="${DB_PASSWORD}"
DB_NAME="stratpoint_timesheet"
ALERT_THRESHOLD_CONNECTIONS=400
ALERT_THRESHOLD_SLOW_QUERIES=10

# Check database connectivity
if ! mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1;" > /dev/null 2>&1; then
    echo "Database connection failed"
    # Send alert
    curl -X POST -H 'Content-type: application/json' \
        --data '{"text":"Database connection failed"}' \
        "$SLACK_WEBHOOK_URL"
    exit 1
fi

# Check connection count
CONNECTION_COUNT=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" \
    -e "SHOW STATUS LIKE 'Threads_connected';" -s -N | awk '{print $2}')

if [ "$CONNECTION_COUNT" -gt "$ALERT_THRESHOLD_CONNECTIONS" ]; then
    echo "High connection count: $CONNECTION_COUNT"
    # Send alert
    curl -X POST -H 'Content-type: application/json' \
        --data "{\"text\":\"High database connection count: $CONNECTION_COUNT\"}" \
        "$SLACK_WEBHOOK_URL"
fi

# Check slow queries
SLOW_QUERY_COUNT=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" \
    -e "SHOW STATUS LIKE 'Slow_queries';" -s -N | awk '{print $2}')

if [ "$SLOW_QUERY_COUNT" -gt "$ALERT_THRESHOLD_SLOW_QUERIES" ]; then
    echo "High slow query count: $SLOW_QUERY_COUNT"
    # Send alert
    curl -X POST -H 'Content-type: application/json' \
        --data "{\"text\":\"High slow query count: $SLOW_QUERY_COUNT\"}" \
        "$SLACK_WEBHOOK_URL"
fi

# Check replication lag (if applicable)
if mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" \
    -e "SHOW SLAVE STATUS\G" | grep -q "Slave_IO_Running: Yes"; then

    REPLICATION_LAG=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" \
        -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

    if [ "$REPLICATION_LAG" -gt 60 ]; then
        echo "High replication lag: $REPLICATION_LAG seconds"
        # Send alert
        curl -X POST -H 'Content-type: application/json' \
            --data "{\"text\":\"High replication lag: $REPLICATION_LAG seconds\"}" \
            "$SLACK_WEBHOOK_URL"
    fi
fi

echo "Database performance check completed"

Maintenance Procedures

Regular Maintenance Tasks

#!/bin/bash
# scripts/database_maintenance.sh

set -e

echo "Starting database maintenance at $(date)"

# Optimize tables
mysql -u root -p stratpoint_timesheet << EOF
OPTIMIZE TABLE users;
OPTIMIZE TABLE projects;
OPTIMIZE TABLE timelogs;
OPTIMIZE TABLE project_users;
OPTIMIZE TABLE leaves;
OPTIMIZE TABLE clients;
EOF

# Update table statistics
mysql -u root -p stratpoint_timesheet << EOF
ANALYZE TABLE users;
ANALYZE TABLE projects;
ANALYZE TABLE timelogs;
ANALYZE TABLE project_users;
ANALYZE TABLE leaves;
ANALYZE TABLE clients;
EOF

# Clean up old data
mysql -u root -p stratpoint_timesheet << EOF
-- Delete old audit logs (older than 2 years)
DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- Delete old notifications (older than 6 months)
DELETE FROM notifications WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- Delete old password reset tokens (older than 24 hours)
DELETE FROM password_resets WHERE created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);
EOF

# Purge binary logs older than 7 days
mysql -u root -p << EOF
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);
EOF

echo "Database maintenance completed at $(date)"

This comprehensive database management guide provides all necessary procedures for maintaining a robust, performant, and reliable MySQL database infrastructure for the Stratpoint Timesheet Application.