Mastering Multi-Database Setups: Harness Docker and Nginx for Efficient Local and Remote Database Management

Mastering Multi-Database Setups: Harness Docker and Nginx for Efficient Local and Remote Database Management

ยท

3 min read

Hello, fellow developers and digital denizens! If you've ever felt like your computer is a buffet for hungry databases, munching on your memory like it's an all-you-can-eat, you're not alone. I've been in those shoes, and let me tell you, it's not a walk in the park.

The Eureka Moment: Docker to the Rescue!

One day, amidst my frustration, a light bulb went off. ๐Ÿ’ก Why not use Docker to host all these databases? That way, I don't need to get into the nitty-gritty of configurations or let my PC run a marathon with databases on its back.

Getting Started: The Magic of Docker-Compose

First things first, let's set up our magical box. Create a folder, let's call it "DB," and inside, make a file named docker-compose.yaml. Here's the spell you need:

# docker-compose
version: '3.1'
services:

  # MySQL database
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: somePassword
    ports:
      - "3306:3306"
    volumes:
      - ./mysql_data:/var/lib/mysql

  # PostgreSQL database
  postgres:
    image: postgres:13
    environment:
      POSTGRES_PASSWORD: "somePassword"
      POSTGRES_USER: "postgres"
      POSTGRES_DB: "postgres"
    ports:
      - "5432:5432"
    volumes:
      - ./postgres_data:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
      - ./pg_hba.conf:/etc/postgresql/pg_hba.conf

  # SQL Server
  mssql:
    image: mcr.microsoft.com/mssql/server
    environment:
      SA_PASSWORD: "SomePassword"
      ACCEPT_EULA: "Y"
    ports:
      - "1433:1433"
    volumes:
      - ./mssql_data:/var/opt/mssql

  # MongoDB
  mongo:
    image: mongo:latest
    environment:
      MONGO_INITDB_ROOT_USERNAME: root
      MONGO_INITDB_ROOT_PASSWORD: somePassword
    ports:
      - "27017:27017"
    volumes:
      - ./mongo_data:/data/db

  # Nginx
  nginx:
    image: nginx:latest
    ports:
      - "80:80"
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf

volumes:
  mysql_data:
  postgres_data:
  mongo_data:

Almost There: Configuring Our Proxy

We're on the home stretch, folks! Now, it's time to set up our domain:

  1. Grab your server's public IP, like '167.71.xxx.xx', and set up an A record in your domain's DNS settings.

  2. I went fancy and created a subdomain like db.myDomain.com

๐Ÿšซ Pro Tip: If you're using Cloudflare, keep the proxy off. We don't want extra magic interfering here.

Next, in our "DB" folder, create a nginx.conf file for our proxy settings:

# nginx.conf
events {}

http {
    server {
        listen 80;
        server_name <Your_DOMAIN>;

        # MySQL
        location /mysql/ {
            proxy_pass http://mysql:3306;
            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;
        }

        # PostgreSQL
        location /postgres/ {
            proxy_pass http://postgres:5432;
            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;
        }

        # SQL Server
        location /mssql/ {
            proxy_pass http://mssql:1433;
            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;
        }

        # MongoDB
        location /mongo/ {
            proxy_pass http://mongo:27017;
            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;
        }
    }
}

Remember to replace <Your_DOMAIN> with your domain. In my case, it's db.mydomainName.com.

The Final Touches: PostgreSQL Configuration

PostgreSQL can be a bit moody when connecting remotely. Let's soothe it with a pg_hba.conf and a postgresql.conf:

#pg_hba.conf
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5
# IPv6 local connections:
host    all             all             ::/0                 md5
#postgresql.confโ€จ
listen_addresses = '*'

Ready, Set, Docker!

Finally, just run docker-compose up -d, and voila! Your databases are ready to serve you, not the other way around.

Local Setup?

If you're setting this up locally, just replace Your_Domain in nginx.conf with your system's IP.

And that's how I turned my database dilemma into a Docker delight! If you have any tips, tricks, or tales of your own, drop them in the comments. Let's learn from each other's epic IT adventures. Cheers and happy coding! ๐Ÿš€