Secure MySQL Access in Minutes with Docker and VPS

Introduction: A Client's Urgent Request

Imagine this scenario: you’re managing a project on a Digital Ocean VPS, with an application running smoothly in Docker and a MySQL database hosted in its managed service. Everything is going great until, suddenly, you receive a message from the client: “I need direct access to the database for operational analysis, and I need it now!” It sounds simple, but there are obstacles. Digital Ocean’s database doesn’t come with a web-based DBMS interface for exploration. It’s protected by IP restrictions, but the client’s internet provider rotates their IPs constantly. Plus, Digital Ocean doesn’t allow creating specific users or limiting permissions like “read-only” from its interface, and the client is pressing for a fast solution. What do you do? Let me tell you how I solved it in under 25 minutes, leaving the client happy and with a solid solution in hand.

Why Not Give Direct Access? Security First

Before diving into commands, I paused to think: why not just hand over the admin credentials for the database and call it a day? The answer is clear: security. Giving direct access would be like leaving your front door wide open with a sign saying “come on in!” Digital Ocean, being a managed service, doesn’t let you create custom users with specific permissions or personalized passwords from its interface. Protecting access by IP wasn’t feasible because the client’s IPs kept changing. If I gave full access, what would happen if, in a moment of distraction, they ran a DROP TABLE or DELETE FROM and erased critical data? It could be a disaster for their operations and a headache for me. So, I decided to build a controlled, secure solution using tools I already had on hand.

Step-by-Step Procedure: Navigating with Docker, nginx-proxy, and Let’s Encrypt

Step 0: The Original docker-compose.yml Infrastructure

Before adding phpMyAdmin, my docker-compose.yml already included key services like nginx-proxy and letsencrypt, which managed routing and security for my web applications. Here’s the original state of the file:

services:
  nginx-proxy:
    image: jwilder/nginx-proxy
    container_name: nginx-proxy
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - /certs:/etc/nginx/certs:ro
      - /etc/nginx/vhost.d:/etc/nginx/vhost.d:rw
      - /usr/share/nginx/html:/usr/share/nginx/html:rw
      - /var/run/docker.sock:/tmp/docker.sock:ro
    labels:
      - com.github.nginx-proxy.nginx=true
    networks:
      - proxy-network

  letsencrypt:
    image: jrcs/letsencrypt-nginx-proxy-companion
    container_name: nginx-proxy-letsencrypt
    volumes:
      - /certs:/etc/nginx/certs:rw
      - /etc/nginx/vhost.d:/etc/nginx/vhost.d:rw
      - /etc/acme.sh:/etc/acme.sh:rw
      - /usr/share/nginx/html:/usr/share/nginx/html:rw
      - /var/run/docker.sock:/var/run/docker.sock:ro
    depends_on:
      - nginx-proxy
    networks:
      - proxy-network

  customer-app:
    image: josefo727/customer-app
    container_name: customer-app-container
    environment:
      - VIRTUAL_HOST=customer-app.jose-gutierrez.com
      - LETSENCRYPT_HOST=customer-app.jose-gutierrez.com
      - LETSENCRYPT_EMAIL=josefo727@gmail.com
    volumes:
      - ./../customer-app:/app
      - ./../customer-app/docker/supervisord.conf:/opt/docker/etc/supervisor.d/system.conf
    depends_on:
      - nginx-proxy
      - redis
    networks:
      - proxy-network

networks:
  proxy-network:
    driver: bridge

At this point, nginx-proxy was already set up as a traffic guard, routing HTTP/HTTPS requests to the correct containers based on the domain specified in VIRTUAL_HOST. Meanwhile, letsencrypt was handling the automatic generation of SSL certificates for each domain defined in LETSENCRYPT_HOST. This foundation allowed me to add new web services quickly and securely.

Step 1: Connect and Create a Secure User

First, I connected to the MySQL database from the console using the admin credentials provided by Digital Ocean. The command looked something like this:

mysql -h db-mysql-customer-do-user-12345678-0.c.db.ondigitalocean.com -P 25060 -u superadmin -p

Once inside, I created a specific user for the client, restricting access to my VPS’s IP and limiting permissions to read-only:

CREATE USER 'customer_user'@'vps-ip' IDENTIFIED BY 'secure-password';
GRANT SELECT ON customer_db.* TO 'customer_user'@'vps-ip';

Why only SELECT? To prevent accidents. If the client, in a rush, accidentally ran a destructive command like DELETE FROM, critical data would be at risk. With read-only permissions, I gave them a tool to analyze, not to destroy.

To ensure everything was ready, I also visited the DigitalOcean (DO) interface. I selected the corresponding SQL engine, looked up the list of users, and reset the password for the customer_user. This ensures the credentials are valid and secure, especially since DigitalOcean generates strong passwords. This quick step on DO’s web interface gave me peace of mind before moving forward.

Step 2: Set Up phpMyAdmin with Docker

The client needed a user-friendly web interface, so I chose phpMyAdmin. Since I already had a Docker environment, integrating it was a breeze. I added this block to my docker-compose.yml:

phpmyadmin:
  image: phpmyadmin/phpmyadmin
  container_name: customer-db-container
  environment:
    - PMA_HOST=db-mysql-customer-do-user-12345678-0.c.db.ondigitalocean.com
    - PMA_ARBITRARY=0
    - PMA_PORT=25060
    - PMASSL=1
    - VIRTUAL_HOST=customer-db.jose-gutierrez.com
    - LETSENCRYPT_HOST=customer-db.jose-gutierrez.com
    - LETSENCRYPT_EMAIL=josefo727@gmail.com
  restart: always
  depends_on:
    - nginx-proxy
  networks:
    - proxy-network

I intentionally didn’t include a username or password here, letting phpMyAdmin prompt for credentials at login so I could use the customer_user I created, maintaining security and flexibility.

Step 3: The Magic of nginx-proxy and Let’s Encrypt

This is where my existing infrastructure shone. By adding the phpMyAdmin block with VIRTUAL_HOST=customer-db.jose-gutierrez.com, nginx-proxy automatically detected the new service and started routing traffic from that subdomain to the phpMyAdmin container. At the same time, letsencrypt saw the LETSENCRYPT_HOST=customer-db.jose-gutierrez.com variable and generated an SSL certificate for the domain, ensuring an encrypted connection. This process was seamless thanks to the pre-existing setup in the original docker-compose.yml, without needing to manually adjust Nginx configurations or certificates.

Step 4: The Updated docker-compose.yml File

After adding the phpMyAdmin block, my docker-compose.yml looked like this:

services:
  nginx-proxy:
    image: jwilder/nginx-proxy
    container_name: nginx-proxy
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - /certs:/etc/nginx/certs:ro
      - /etc/nginx/vhost.d:/etc/nginx/vhost.d:rw
      - /usr/share/nginx/html:/usr/share/nginx/html:rw
      - /var/run/docker.sock:/tmp/docker.sock:ro
    labels:
      - com.github.nginx-proxy.nginx=true
    networks:
      - proxy-network

  letsencrypt:
    image: jrcs/letsencrypt-nginx-proxy-companion
    container_name: nginx-proxy-letsencrypt
    volumes:
      - /certs:/etc/nginx/certs:rw
      - /etc/nginx/vhost.d:/etc/nginx/vhost.d:rw
      - /etc/acme.sh:/etc/acme.sh:rw
      - /usr/share/nginx/html:/usr/share/nginx/html:rw
      - /var/run/docker.sock:/var/run/docker.sock:ro
    depends_on:
      - nginx-proxy
    networks:
      - proxy-network

  customer-app:
    image: josefo727/customer-app
    container_name: customer-app-container
    environment:
      - VIRTUAL_HOST=customer-app.jose-gutierrez.com
      - LETSENCRYPT_HOST=customer-app.jose-gutierrez.com
      - LETSENCRYPT_EMAIL=josefo727@gmail.com
    volumes:
      - ./../customer-app:/app
      - ./../customer-app/docker/supervisord.conf:/opt/docker/etc/supervisor.d/system.conf
    depends_on:
      - nginx-proxy
      - redis
    networks:
      - proxy-network

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: customer-db-container
    environment:
      - PMA_HOST=db-mysql-customer-do-user-12345678-0.c.db.ondigitalocean.com
      - PMA_ARBITRARY=0
      - PMA_PORT=25060
      - PMASSL=1
      - VIRTUAL_HOST=customer-db.jose-gutierrez.com
      - LETSENCRYPT_HOST=customer-db.jose-gutierrez.com
      - LETSENCRYPT_EMAIL=josefo727@gmail.com
    restart: always
    depends_on:
      - nginx-proxy
    networks:
      - proxy-network

networks:
  proxy-network:
    driver: bridge

The new phpMyAdmin service integrated seamlessly with nginx-proxy and letsencrypt, leveraging their capabilities for routing and security without additional tweaks.

Step 5: Run and Test

With the updated file, I ran:

docker-compose up -d --remove-orphans --build

In less than a minute, the phpMyAdmin container was up and running. I opened my browser, went to customer-db.jose-gutierrez.com, logged in with customer_user credentials, and everything worked perfectly. I shared the credentials with the client, who was immediately able to explore their data for operational analysis.

Conclusion: Win-Win with Docker and docker-compose

Solving this challenge in under 25 minutes was a double win: the client was satisfied with a fast, secure solution, and I reaffirmed the power of Docker and docker-compose. Showing the docker-compose.yml before and after highlights how nginx-proxy and letsencrypt simplify adding services like phpMyAdmin. These tools are like a swift ship that sails you to port without storms: with containers, I can scale services in minutes, keep everything organized in a single docker-compose.yml file, and secure connections with SSL effortlessly. If you’re just starting out, I invite you to try these technologies: they’re a gateway to innovation with plenty of time to enjoy the journey. A happy client, an efficient solution for me, and you, perhaps, eager to dive into the world of containers!