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!