Use MYSQL instead of SQLite by default

[josecelano]

🎯 Objective

Replace SQLite with MySQL as the default database for Torrust Tracker Demo deployment as part of the Hetzner migration plan.

📋 Background

This is Phase 1 of the Hetzner Migration Plan. Currently, the tracker uses SQLite as the default database, but for production deployments on Hetzner, we want to use MySQL for better production characteristics, scalability, and operational management.

📝 Tasks

Database Service Setup

• Add MySQL service to application/compose.yaml
  • Configure MySQL 8.0+ with appropriate environment variables
  • Set up data volume persistence (mysql_data volume already exists)
  • Add to backend_network for tracker connectivity
  • Configure proper logging limits

Tracker Configuration Updates

• Update application/storage/tracker/etc/tracker.toml:
  • Change [core.database].driver from "sqlite3" to "mysql"
  • Add MySQL connection configuration (host, port, database name)
  • Remove SQLite-specific path configuration

Environment Configuration

• Update application/.env.production template:
  • Add MySQL database environment variables (host, port, database, user, password)
  • Add MySQL-specific tracker configuration overrides
  • Maintain backward compatibility comments

Database Initialization

• Create MySQL initialization scripts/schema:
  • Research Torrust Tracker MySQL schema requirements
  • Create application/storage/mysql/init/ directory structure
  • Add SQL initialization scripts for database and tables
  • Ensure proper charset and collation settings

Documentation Updates

• Update relevant documentation:
  • application/README.md - Update database requirements
  • application/docs/production-setup.md - Add MySQL setup instructions
  • Migration notes for existing SQLite users

✅ Validation Criteria

Local Testing

• Deploy locally using make apply
• Verify MySQL service starts successfully and is healthy
• Verify tracker service connects to MySQL without errors
• Check Docker logs show no database connection errors

Functional Testing

• Make test announce request to tracker
• Confirm data is written to MySQL database:
  • Check torrents table exists and is populated
  • Verify download counter increases after announce
  • Validate data persistence after tracker restart

Integration Testing

• Test tracker restart with persistent MySQL data
• Verify Grafana can access tracker metrics
• Confirm Prometheus monitoring still works
• Test complete stack restart (docker compose down && docker compose up)

🔧 Implementation Notes

Current State Analysis

• Current database: SQLite (/var/lib/torrust/tracker/database/sqlite3.db)
• Current config: driver = "sqlite3" in tracker.toml
• Docker volumes: mysql_data volume already defined but unused
• Environment: Basic environment variables in .env.production

MySQL Configuration Requirements

• Version: MySQL 8.0+ or MariaDB 10.5+
• Character set: utf8mb4 with utf8mb4_unicode_ci collation
• Storage engine: InnoDB for ACID compliance
• Connection: Host-based connection within Docker network

Environment Variables Strategy

Follow existing pattern in compose.yaml:

• Use TORRUST_TRACKER_CONFIG_OVERRIDE_ prefix for configuration overrides
• Maintain compatibility with tracker's environment variable system
• Reference: Current SQLite override TORRUST_TRACKER_CONFIG_OVERRIDE_CORE__DATABASE__DRIVER

📦 Deliverable

Working local deployment with MySQL backend that:

• ✅ Replaces SQLite with MySQL as default database
• ✅ Maintains all existing tracker functionality
• ✅ Persists data across container restarts
• ✅ Integrates with existing monitoring stack
• ✅ Can be deployed via existing make apply workflow

🔗 Related

• Parent: Hetzner Migration Plan
• Next Phase: Phase 2 - 12-Factor App Refactoring
• Testing: Use infrastructure/ local testing environment
• Documentation: Production Setup Guide

💡 Notes

• This phase focuses on local development and testing only
• No Hetzner infrastructure changes in this phase
• Must be fully validated locally before proceeding to Phase 2
• Maintain Docker-first approach for ease of updates
• Consider data migration strategy for future SQLite→MySQL migrations