Database Maintenance¶

Maintaining the MariaDB database for optimal performance.

Regular Tasks¶

Optimize Tables¶

Monthly optimization:

sudo mb-maintenance --optimize-db

Manual optimization:

sudo mysql mailborder << EOF
OPTIMIZE TABLE mb_log_audit;
OPTIMIZE TABLE mb_log_email;
OPTIMIZE TABLE mb_quarantine;
OPTIMIZE TABLE mb_users;
EOF

Analyze Tables¶

Update statistics:

sudo mysql mailborder << EOF
ANALYZE TABLE mb_users;
ANALYZE TABLE mb_policy_rules;
ANALYZE TABLE mb_quarantine;
EOF

Check Table Integrity¶

Check all tables:

sudo mysql mailborder -e "CHECK TABLE mb_users, mb_quarantine, mb_log_audit"

Repair if needed:

sudo mysql mailborder -e "REPAIR TABLE mb_log_audit"

Cleanup Tasks¶

Remove Old Logs¶

Automatic cleanup:

sudo mb-maintenance --database-cleanup

Manual cleanup:

-- Delete logs older than 90 days
DELETE FROM mb_log_audit WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Delete email logs older than 30 days
DELETE FROM mb_log_email WHERE received_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Delete old sessions
DELETE FROM mb_auth_sessions WHERE expires_at < NOW();

Archive Old Data¶

Export before deletion:

# Export logs to file
sudo mysqldump mailborder mb_log_audit \
  --where="created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)" \
  | gzip > /backups/logs-archive-$(date +%Y%m%d).sql.gz

# Then delete
sudo mysql mailborder -e \
  "DELETE FROM mb_log_audit WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)"

Performance Monitoring¶

Check Table Sizes¶

SELECT
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
  table_rows
FROM information_schema.TABLES
WHERE table_schema = 'mailborder'
ORDER BY (data_length + index_length) DESC;

Check Slow Queries¶

Enable slow query log:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Analyze:

sudo mysqldumpslow /var/log/mysql/slow.log

Monitor Connections¶

Active connections:

sudo mysql -e "SHOW PROCESSLIST"

Connection statistics:

sudo mysql -e "SHOW STATUS LIKE 'Threads_connected'"
sudo mysql -e "SHOW STATUS LIKE 'Max_used_connections'"

Backup & Recovery¶

Backup Database¶

Full backup:

sudo mysqldump mailborder | gzip > /backups/mailborder-$(date +%Y%m%d).sql.gz

Table-specific backup:

sudo mysqldump mailborder mb_users mb_auth_passkeys > /backups/users-$(date +%Y%m%d).sql

Restore Database¶

Full restore:

gunzip < /backups/mailborder-20250113.sql.gz | sudo mysql mailborder

Verify:

sudo mysql mailborder -e "SELECT COUNT(*) FROM mb_users"