Can't connect to MySQL server on 'localhost:3306' in dockerised Django app using docker-compose

Question

I'm trying to build a Dockerized Django application with a MySQl database, but am unable to connect to the database when running docker-compose up

My Dockerfile is:

# Use the official Python image as the base image
FROM python:3.10

# Set environment variables
ENV PYTHONUNBUFFERED 1

# Set the working directory inside the container
WORKDIR /app

# Copy the requirements file and install dependencies
COPY requirements.txt /app/
RUN pip install -r requirements.txt

CMD ["sh", "-c", "python manage.py migrate"]

# Copy the project files into the container
COPY . /app/

And docker-compose.yml looks like:

version: '3'
services:
  db:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      - MYSQL_DATABASE='model_db'
      - MYSQL_USER='root'
      - MYSQL_PASSWORD='password'
      - MYSQL_ROOT_PASSWORD='password'
    volumes:
      - /tmp/app/mysqld:/var/run/mysqld
      - ./db:/var/lib/mysql
  web:
    build: .
    command: python manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/app
    ports:
      - "8000:8000"
    depends_on:
      - db
    env_file:
      - .env

I'm using a .env file to define environment variables, and it is:

MYSQL_DATABASE=model_db
MYSQL_USER=root
MYSQL_PASSWORD=password
MYSQL_ROOT_PASSWORD=password
MYSQL_HOST=db

These are then loaded to the app settings.py like this:

BASE_DIR = Path(__file__).resolve().parent.parent
env = environ.Env()

if READ_DOT_ENV_FILE := env.bool("DJANGO_READ_DOT_ENV_FILE", default=True):
    # OS environment variables take precedence over variables from .env
    env.read_env(env_file=os.path.join(BASE_DIR, '.env'))

MYSQL_DATABASE = env('MYSQL_DATABASE', default=None)
MYSQL_USER = env('MYSQL_USER', default=None)
MYSQL_PASSWORD = env('MYSQL_PASSWORD', default=None)
MYSQL_ROOT_PASSWORD = env('MYSQL_ROOT_PASSWORD',default=None)
MYSQL_HOST = env('MYSQL_HOST', default=None)

# Database
# https://docs.djangoproject.com/en/4.2/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'MYSQL_DATABASE': MYSQL_DATABASE,
        'USER': MYSQL_USER,                      # Not used with sqlite3.
        'PASSWORD': MYSQL_PASSWORD,                  # Not used with sqlite3.
        'MYSQL_ROOT_PASSWORD': MYSQL_ROOT_PASSWORD,
        'HOST': MYSQL_HOST,
        'PORT': 3306,
    },
}

When I run docker-compose up it errors out with _mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'db:3306' (99)

Any suggestions?

Answer 1

There some steps i think you should do:

1. You don't need to expose the mysql port because the containers are in the same network
2. You don't need CMD in your Dockerfile. Just do this in your command directive in your docker-compose:

command: python3 manage.py migrate && python3 manage.py runserver 0.0.0.0:8000

Other things seems right.

Hope it helps !

Answer 2

I've fixed the issues(s), which were several.

1. The host was wrong. Following several comments, I changed "MYSQL_HOST=localhost" to "MYSQL_HOST=db", i.e. the container name.

2. The web container was starting before the database server was ready, causing the connection error, because depends_on will only wait for a container to be up, not actually fully initialised. Instituting a health_check, following this and this solved the issue.

3. python3 manage.py migrate would not work unless I created a DATABASE_URL variable, following this, where DATABASE_URL == mysql://<username>:<password>@<host>:<port>/<db_name> (see here)

The final working Dockerfile, docker-compose.yml, settings.py and .env files were:

Dockerfile

# Use the official Python image as the base image
FROM python:3.10

# Set environment variables
ENV PYTHONUNBUFFERED 1

# Set the working directory inside the container
WORKDIR /app

# Copy the requirements file and install dependencies
COPY requirements.txt /app/
RUN pip install -r requirements.txt

# Copy the project files into the container
COPY . /app/

.env

MYSQL_DATABASE=modeldb
MYSQL_USER=mysql
MYSQL_PASSWORD=mypass
MYSQL_HOST=db
DATABASE_URL=mysql://mysql:mypass@db:3306/modeldb

settings.py

MYSQL_DATABASE = env('MYSQL_DATABASE', default=None)
MYSQL_USER = env('MYSQL_USER', default=None)
MYSQL_PASSWORD = env('MYSQL_PASSWORD', default=None)
MYSQL_HOST = env('MYSQL_HOST', default=None)
DATABASE_URL = os.environ.get('DATABASE_URL', '')

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'MYSQL_DATABASE': MYSQL_DATABASE,
        'USER': MYSQL_USER,                      # Not used with sqlite3.
        'PASSWORD': MYSQL_PASSWORD,                  # Not used with sqlite3.
        'HOST': MYSQL_HOST,
        'PORT': 3306,
    },
}

if DATABASE_URL:
    import dj_database_url
    DATABASES['default'] = dj_database_url.config(default=DATABASE_URL)

docker-compose.yml

version: '3'
services:
  db:
    image: mysql:8
    environment:
      MYSQL_DATABASE: 'modeldb'
      MYSQL_USER: 'mysql'
      MYSQL_PASSWORD: 'test'
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    volumes:
      - ./db_django:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"] # Command to check health.
      interval: 10s # Interval between health checks.
      timeout: 10s # Timeout for each health checking.
      retries: 20 # How many times retries.
      start_period: 30s # Warm up wait period
  web:
    build: .
    command: >
      sh -c "python manage.py collectstatic --noinput &&
             python manage.py migrate &&
             python manage.py runserver 0.0.0.0:8000"
    volumes:
      - .:/app
    ports:
      - "8000:8000"
    depends_on:
      db:
        condition: service_healthy
    env_file:
      - .env