Can't connect python server and mysql with Docker compose

Question

I am newly learning Docker and compose, I have searched and watched many tutorials but could not understand what is causing this connection error:

_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'localhost:3306' (99)

my file structure: docker-compose.yml .env mysql/ database.sql Dockerfile python/ server.py Dockerfile

docker-compose.yml:

version: '3.8'

services:

  mysql:
    build: 
      context: ./mysql
      dockerfile: Dockerfile
    restart: always
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    ports:
      - '3306:3306'

  pythonapp:
    build:
      context: ./python
      dockerfile: Dockerfile
    depends_on:
      - mysql
    ports:
      - '8080:8080'

python/Dockerfile:

FROM python:3.11-slim

RUN pip install mysql-connector-python && \
    pip install python-dotenv

COPY . /app

WORKDIR /app

CMD ["python", "-u", "server.py"]

EXPOSE 8080

mysql/Dockerfile:

FROM mysql:latest

COPY ./database.sql /docker-entrypoint-initdb.d/

python/server.py:

from http.server import HTTPServer, BaseHTTPRequestHandler
from dotenv import load_dotenv
import mysql.connector
import json
import os 

load_dotenv('../.env')

# Establish connection to MySQL
db_connection = mysql.connector.connect(
    user=os.getenv('MYSQL_USER'), 
    password=os.getenv('MYSQL_ROOT_PASSWORD'), 
    host=os.getenv('_HOST'),
    port=3306,
    database=os.getenv('MYSQL_DATABASE'))
print("DB connected")
...

.env:

_HOST=localhost
MYSQL_DATABASE=db
MYSQL_USER=root
MYSQL_ROOT_PASSWORD=password

found this: PyMySQL can't connect to MySQL on localhost and:

I have tried _HOST=127.0.0.1, _HOST=mysql, _HOST=host.docker.internal but the error did not change. Also tried to compose for second time to solve connection maybe because the database was not ready for connection, but getting the same error. So the MySQL database is running but the python server does not connect.

Teemu Risikko · Accepted Answer · 2024-02-28 07:11:28Z

1

First of all, localhost is always wrong host for the db when using docker compose like this. localhost points to the localhost of the python container, not to the localhost of your host machine. You need to refer to it with the service name mysql.

_HOST=mysql

Then, in addition to the answer by Leo Aso, you might need to wait until the MYSQL service is healthy and can be connected to. The depends_on alone is not enough, and it will try to connect to the DB before it is ready. This works for me

version: '3.8'

services:

  mysql:
    image: mysql:latest
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    healthcheck:
        test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
        timeout: 20s
        retries: 10

  pythonapp:
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      mysql:
        condition: service_healthy

I added a healthcheck to the mysql service and condition: serivce_healthy to the python service, so that it will not start until mysql is reporting healthy.

Bonus: How to debug

If it's still not working, first verify that you can connect to the db at all even inside the mysql container, by running this command:

docker compose exec -it mysql mysql -ppassword

answered Feb 28, 2024 at 7:11

Teemu Risikko

3,3372 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lightning Over a year ago

Thanks, I did not know there was a healthcheck, it solved all the problem, so it was happening because the server was starting before the mysql connection established.

Leo Aso · Accepted Answer · 2024-02-28 06:12:20Z

1

You are calling load_dotenv('../.env'), but your .env file only exists locally; there is no such file inside the Docker container.

You will have to find a way to get it there somehow. The easiest way would be to move the .env file inside the Python folder, then COPY it in your Dockerfile, and use load_dotenv('./.env') in your code.

answered Feb 28, 2024 at 6:12

Leo Aso

12.6k4 gold badges28 silver badges52 bronze badges

1 Comment

Lightning Over a year ago

it seems kind of working, but I am still facing: mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (99) also when I try mysql instead of localhost: mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'mysql:3306' (111)

Collectives™ on Stack Overflow

Can't connect python server and mysql with Docker compose

2 Answers 2

Bonus: How to debug

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Bonus: How to debug

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related