Production MySQL Docker Setup

A production-ready MySQL 8.0 setup with Docker Compose, including comprehensive configuration, monitoring, backup scripts, and phpMyAdmin.

Features

• ✅ MySQL 8.0 (latest stable version)
• ✅ Production-optimized configuration
• ✅ Data persistence with volumes
• ✅ Health checks and auto-restart
• ✅ Resource limits (CPU and Memory)
• ✅ Logging configuration
• ✅ Network isolation
• ✅ phpMyAdmin for database management
• ✅ Custom initialization scripts
• ✅ Automated backup script
• ✅ Security best practices

Directory Structure

.
├── docker-compose.yml          # Main Docker Compose configuration
├── .env                        # Environment variables (create from .env.example)
├── .env.example                # Example environment file
├── backup.sh                   # Automated backup script
├── mysql/
│   ├── conf.d/                 # Custom MySQL configuration
│   │   └── custom.cnf          # Performance and security settings
│   ├── init/                   # Initialization scripts (runs once)
│   │   └── 01-init.sql         # Example initialization script
│   ├── data/                   # Database data (auto-created)
│   └── backups/                # Backup storage directory
└── README.md                   # This file

Quick Start

1. Prerequisites

• Docker Engine 20.10+
• Docker Compose 2.0+
• At least 2GB free RAM
• 10GB free disk space

2. Initial Setup

# Clone or create project directory
mkdir mysql-production && cd mysql-production

# Copy environment example file
cp .env.example .env

# Edit .env file with your secure passwords
nano .env  # or vim, code, etc.

3. Configure Environment Variables

Edit the .env file and set secure passwords:

MYSQL_ROOT_PASSWORD=YourVerySecureRootPassword123!
MYSQL_DATABASE=production_db
MYSQL_USER=app_user
MYSQL_PASSWORD=YourSecureAppPassword123!
MYSQL_PORT=3306
TZ=Asia/Singapore
MYSQL_DATA_PATH=./mysql/data
PHPMYADMIN_PORT=8080

Important Security Notes:

• Use strong passwords (minimum 16 characters)
• Mix uppercase, lowercase, numbers, and special characters
• Never commit .env file to version control
• Add .env to your .gitignore file

4. Start the Services

# Start MySQL and phpMyAdmin
docker-compose up -d

# Check status
docker-compose ps

# View logs
docker-compose logs -f mysql

# Check health
docker-compose exec mysql mysqladmin ping -u root -p

5. Verify Installation

# Connect to MySQL
docker-compose exec mysql mysql -u root -p

# Or use the app user
docker-compose exec mysql mysql -u app_user -p production_db

# Access phpMyAdmin
# Open browser: http://localhost:8080

Configuration Details

MySQL Command Line Arguments

The docker-compose.yml includes optimized MySQL parameters:

--max_connections=200              # Maximum concurrent connections
--max_allowed_packet=256M          # Maximum packet size
--innodb_buffer_pool_size=1G       # InnoDB buffer pool size
--innodb_log_file_size=256M        # InnoDB log file size
--slow_query_log=1                 # Enable slow query logging
--long_query_time=2                # Log queries taking >2 seconds

Resource Limits

CPU: 1-2 cores
Memory: 1GB minimum, 2GB maximum

Adjust in docker-compose.yml based on your needs:

deploy:
  resources:
    limits:
      cpus: "2.0"
      memory: 2G

Health Check

Automatic health monitoring every 10 seconds:

• Checks MySQL availability
• Auto-restarts on failure
• 30-second startup grace period

Usage

Connecting from Applications

Connection String Examples:

# From host machine
mysql://app_user:password@localhost:3306/production_db

# From Docker container in same network
mysql://app_user:password@mysql:3306/production_db

# Node.js (Prisma)
DATABASE_URL="mysql://app_user:password@localhost:3306/production_db"

# Python (SQLAlchemy)
SQLALCHEMY_DATABASE_URI=mysql+pymysql://app_user:password@localhost:3306/production_db

# Java (JDBC)
jdbc:mysql://localhost:3306/production_db?user=app_user&password=password

Backup and Restore

Automated Backup

# Make backup script executable
chmod +x backup.sh

# Backup specific database
./backup.sh production_db

# Backup with cron (daily at 2 AM)
0 2 * * * /path/to/backup.sh production_db

Manual Backup

# Backup single database
docker-compose exec mysql mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  production_db > backup_$(date +%Y%m%d).sql

# Backup all databases
docker-compose exec mysql mysqldump -u root -p \
  --all-databases \
  --single-transaction > all_databases_$(date +%Y%m%d).sql

# Backup with gzip compression
docker-compose exec mysql mysqldump -u root -p \
  production_db | gzip > backup_$(date +%Y%m%d).sql.gz

Restore from Backup

# Restore from uncompressed backup
docker-compose exec -T mysql mysql -u root -p production_db < backup.sql

# Restore from gzipped backup
gunzip < backup.sql.gz | docker-compose exec -T mysql mysql -u root -p production_db

# Restore all databases
docker-compose exec -T mysql mysql -u root -p < all_databases.sql

Database Management

# Access MySQL CLI
docker-compose exec mysql mysql -u root -p

# Create new database
docker-compose exec mysql mysql -u root -p -e "CREATE DATABASE new_db;"

# Grant privileges
docker-compose exec mysql mysql -u root -p -e "
GRANT ALL PRIVILEGES ON new_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;"

# Show databases
docker-compose exec mysql mysql -u root -p -e "SHOW DATABASES;"

# Show tables
docker-compose exec mysql mysql -u root -p production_db -e "SHOW TABLES;"

Monitoring

# View real-time logs
docker-compose logs -f mysql

# Check MySQL status
docker-compose exec mysql mysqladmin -u root -p status

# Show processlist
docker-compose exec mysql mysql -u root -p -e "SHOW PROCESSLIST;"

# Check slow queries
docker-compose exec mysql tail -f /var/log/mysql/slow-query.log

# View error log
docker-compose exec mysql tail -f /var/log/mysql/error.log

# Monitor performance
docker stats mysql-production

Maintenance

Update MySQL

# Pull latest image
docker-compose pull mysql

# Recreate container
docker-compose up -d mysql

# Verify version
docker-compose exec mysql mysql --version

Cleanup

# Stop services
docker-compose down

# Remove volumes (⚠️ DELETES DATA)
docker-compose down -v

# Remove old backups (keeps last 7 days)
find ./mysql/backups -name "*.sql.gz" -mtime +7 -delete

Performance Tuning

Monitor and adjust these settings in mysql/conf.d/custom.cnf:

[mysqld]
# Increase for more concurrent connections
max_connections=200

# Increase for better performance (75% of available RAM)
innodb_buffer_pool_size=1G

# Tune based on workload
innodb_flush_log_at_trx_commit=2  # 1=safest, 2=faster

Security Best Practices

1. Strong Passwords: Use 16+ character passwords with mixed case, numbers, symbols
2. Network Isolation: Keep MySQL in isolated Docker network
3. Firewall Rules: Restrict port 3306 access to trusted IPs
4. Regular Updates: Keep MySQL image updated
5. Backup Encryption: Encrypt sensitive backups
6. Least Privilege: Create users with minimal required permissions
7. Disable Remote Root: Never allow root login from %
8. SSL/TLS: Enable encrypted connections for production

-- Create user with specific host
CREATE USER 'app_user'@'172.20.0.%' IDENTIFIED BY 'password';

-- Grant specific privileges only
GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO 'app_user'@'172.20.0.%';

Troubleshooting

Container won't start

# Check logs
docker-compose logs mysql

# Common issues:
# 1. Port already in use
sudo lsof -i :3306
# Change MYSQL_PORT in .env

# 2. Permission issues
sudo chown -R 999:999 ./mysql/data

# 3. Corrupted data
# Backup data and remove volume
docker-compose down -v

Cannot connect

# Verify container is running
docker-compose ps

# Check health status
docker-compose exec mysql mysqladmin ping -u root -p

# Verify network
docker network ls
docker network inspect mysql-production_mysql_network

# Test connection from host
mysql -h 127.0.0.1 -P 3306 -u app_user -p

Slow performance

# Check slow queries
docker-compose exec mysql tail -100 /var/log/mysql/slow-query.log

# Monitor resources
docker stats mysql-production

# Increase buffer pool size in docker-compose.yml
--innodb_buffer_pool_size=2G

# Add indexes to frequently queried columns

Data persistence issues

# Verify volume mount
docker volume inspect mysql-production_mysql_data

# Check directory permissions
ls -la ./mysql/data

# Ensure bind mount path exists
mkdir -p ./mysql/data

Production Checklist

• Strong, unique passwords set in .env
• .env file added to .gitignore
• Firewall configured (restrict port 3306)
• SSL/TLS enabled for connections
• Regular backup cron job configured
• Monitoring and alerting set up
• Resource limits tuned for workload
• Slow query log reviewed regularly
• Binary logs configured and rotated
• Separate read-only user created
• phpMyAdmin access restricted (or disabled)
• Health check intervals configured
• Log rotation configured

Additional Resources

• MySQL 8.0 Documentation
• Docker MySQL Image
• MySQL Performance Tuning
• MySQL Security Guide

License

This configuration is provided as-is for production use.

Support

For issues or questions:

1. Check MySQL error logs: docker-compose logs mysql
2. Review MySQL documentation
3. Check Docker Compose documentation
4. Verify environment variables and permissions