honey-be/PHPMYADMIN_SETUP.md

phpMyAdmin Setup Guide

This guide explains how to set up phpMyAdmin for managing your MySQL database on your VPS.

Overview

• phpMyAdmin Port: 8081 (mapped to container port 80)
• MySQL Service Name: db (internal Docker network)
• Database Name: lottery_db
• Network: lottery-network (shared with MySQL and backend)

Security Features

✅ MySQL port 3306 is NOT exposed - Only accessible within Docker network
✅ phpMyAdmin accessible on port 8081 - Can be restricted via firewall
✅ Upload limit set to 64M - Prevents large file uploads
✅ Uses same root password - From your existing secret file

Prerequisites

• Docker and Docker Compose installed on VPS
• Existing MySQL database running in Docker
• DB_ROOT_PASSWORD environment variable set (from secret file)

Step-by-Step Deployment

Step 1: Verify Current Setup

First, check that your MySQL container is running and the database password is accessible:

cd /opt/app/backend/lottery-be

# Check if MySQL container is running
docker ps | grep lottery-mysql

# Load database password (if not already set)
source scripts/load-db-password.sh

# Verify password is set
echo $DB_ROOT_PASSWORD

Step 2: Update Docker Compose

The docker-compose.prod.yml file has already been updated with the phpMyAdmin service. Verify the changes:

# View the phpMyAdmin service configuration
grep -A 20 "phpmyadmin:" docker-compose.prod.yml

You should see:

• Service name: phpmyadmin
• Port mapping: 8081:80
• PMA_HOST: db
• UPLOAD_LIMIT: 64M

Step 3: Start phpMyAdmin Service

cd /opt/app/backend/lottery-be

# Make sure DB_ROOT_PASSWORD is set
source scripts/load-db-password.sh

# Start only the phpMyAdmin service (MySQL should already be running)
docker-compose -f docker-compose.prod.yml up -d phpmyadmin

Or if you want to restart all services:

# Stop all services
docker-compose -f docker-compose.prod.yml down

# Start all services (including phpMyAdmin)
source scripts/load-db-password.sh
docker-compose -f docker-compose.prod.yml up -d

Step 4: Verify phpMyAdmin is Running

# Check container status
docker ps | grep phpmyadmin

# Check logs for any errors
docker logs lottery-phpmyadmin

# Test if port 8081 is listening
netstat -tlnp | grep 8081
# or
ss -tlnp | grep 8081

Step 5: Configure Firewall (UFW)

On Inferno Solutions VPS (Ubuntu), you need to allow port 8081:

# Check current UFW status
sudo ufw status

# Allow port 8081 (replace with your VPS IP if you want to restrict access)
sudo ufw allow 8081/tcp

# If you want to restrict to specific IP only (recommended for production):
# sudo ufw allow from YOUR_IP_ADDRESS to any port 8081

# Reload UFW
sudo ufw reload

# Verify the rule was added
sudo ufw status numbered

Security Recommendation: If you have a static IP, restrict access to that IP only:

# Replace YOUR_IP_ADDRESS with your actual IP
sudo ufw allow from YOUR_IP_ADDRESS to any port 8081

Step 6: Access phpMyAdmin

Open your web browser and navigate to:

http://YOUR_VPS_IP:8081

Example: If your VPS IP is 37.1.206.220, use:

http://37.1.206.220:8081

Step 7: Login to phpMyAdmin

Use these credentials:

• Server: db (or leave as default - phpMyAdmin will auto-detect)
• Username: root
• Password: The value from SPRING_DATASOURCE_PASSWORD in your secret file

To get the password:

# On your VPS
grep SPRING_DATASOURCE_PASSWORD /run/secrets/lottery-config.properties

Verification Checklist

After setup, verify:

• phpMyAdmin container is running: docker ps | grep phpmyadmin
• Port 8081 is accessible: curl http://localhost:8081 (should return HTML)
• Firewall allows port 8081: sudo ufw status | grep 8081
• Can login to phpMyAdmin with root credentials
• Can see lottery_db database in phpMyAdmin
• MySQL port 3306 is NOT exposed: netstat -tlnp | grep 3306 (should show nothing or only 127.0.0.1)

Security Best Practices

1. Restrict Access by IP (Recommended)

Only allow your IP address to access phpMyAdmin:

# Find your current IP
curl ifconfig.me

# Allow only your IP
sudo ufw delete allow 8081/tcp
sudo ufw allow from YOUR_IP_ADDRESS to any port 8081

2. Use HTTPS (Optional but Recommended)

If you have a domain and SSL certificate, you can set up Nginx as a reverse proxy:

# /etc/nginx/sites-available/phpmyadmin
server {
    listen 443 ssl;
    server_name phpmyadmin.yourdomain.com;
    
    ssl_certificate /path/to/cert.pem;
    ssl_certificate_key /path/to/key.pem;
    
    location / {
        proxy_pass http://127.0.0.1:8081;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

3. Change Default phpMyAdmin Behavior

You can add additional security settings to the phpMyAdmin service in docker-compose.prod.yml:

environment:
  # ... existing settings ...
  # Disable certain features for security
  PMA_CONTROLUSER: ''
  PMA_CONTROLPASS: ''
  # Enable HTTPS only (if using reverse proxy)
  # PMA_ABSOLUTE_URI: https://phpmyadmin.yourdomain.com

4. Regular Updates

Keep phpMyAdmin updated:

# Pull latest image
docker-compose -f docker-compose.prod.yml pull phpmyadmin

# Restart service
docker-compose -f docker-compose.prod.yml up -d phpmyadmin

Troubleshooting

phpMyAdmin Container Won't Start

# Check logs
docker logs lottery-phpmyadmin

# Common issues:
# 1. DB_ROOT_PASSWORD not set
source scripts/load-db-password.sh
docker-compose -f docker-compose.prod.yml up -d phpmyadmin

# 2. MySQL container not running
docker-compose -f docker-compose.prod.yml up -d db

Cannot Connect to Database

# Verify MySQL is accessible from phpMyAdmin container
docker exec lottery-phpmyadmin ping -c 3 db

# Check if MySQL is healthy
docker ps | grep lottery-mysql
docker logs lottery-mysql | tail -20

Port 8081 Not Accessible

# Check if port is listening
sudo netstat -tlnp | grep 8081

# Check firewall
sudo ufw status

# Check if container is running
docker ps | grep phpmyadmin

# Restart phpMyAdmin
docker-compose -f docker-compose.prod.yml restart phpmyadmin

"Access Denied" When Logging In

1. Verify password is correct:

   grep SPRING_DATASOURCE_PASSWORD /run/secrets/lottery-config.properties
   

2. Verify DB_ROOT_PASSWORD matches:

   source scripts/load-db-password.sh
   echo $DB_ROOT_PASSWORD
   

3. Test MySQL connection directly:

   docker exec -it lottery-mysql mysql -u root -p
   # Enter the password when prompted
   

Spring Boot Configuration Verification

Your Spring Boot application should be using the Docker service name for the database connection. Verify:

1. Secret file (/run/secrets/lottery-config.properties) should contain:

   SPRING_DATASOURCE_URL=jdbc:mysql://db:3306/lottery_db
   

2. NOT using localhost:

   • ❌ Wrong: jdbc:mysql://localhost:3306/lottery_db
   • ✅ Correct: jdbc:mysql://db:3306/lottery_db

To verify:

grep SPRING_DATASOURCE_URL /run/secrets/lottery-config.properties

Maintenance Commands

# View phpMyAdmin logs
docker logs lottery-phpmyadmin

# Restart phpMyAdmin
docker-compose -f docker-compose.prod.yml restart phpmyadmin

# Stop phpMyAdmin
docker-compose -f docker-compose.prod.yml stop phpmyadmin

# Start phpMyAdmin
docker-compose -f docker-compose.prod.yml start phpmyadmin

# Remove phpMyAdmin (keeps data)
docker-compose -f docker-compose.prod.yml rm -f phpmyadmin

# Update phpMyAdmin to latest version
docker-compose -f docker-compose.prod.yml pull phpmyadmin
docker-compose -f docker-compose.prod.yml up -d phpmyadmin

Quick Reference

Item	Value
URL	http://YOUR_VPS_IP:8081
Username	root
Password	From SPRING_DATASOURCE_PASSWORD in secret file
Server	db (auto-detected)
Database	lottery_db
Container	lottery-phpmyadmin
Port	8081 (host) → 80 (container)
Network	lottery-network

Next Steps

After phpMyAdmin is set up:

1. ✅ Test login and database access
2. ✅ Verify you can see all tables in lottery_db
3. ✅ Set up IP restrictions for better security
4. ✅ Consider setting up HTTPS via Nginx reverse proxy
5. ✅ Document your access credentials securely