Question posted in
The official documentation can be found here.

Not able to connect to mysql docker image from other docker containers. Access Denied error

I am trying to set up a docker service with mysql and fast api using docker compose and DockerFile.
But in this set up i am not able to connect to mysql docker image and getting the error:

mysql_2             | 2024-05-29T09:56:35.594452Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)

Below is my docker compose file and dockerfile.

docker-compose.yml:

version: '3.8'

networks:
  app-network:
    driver: bridge

services:
  mysql:
    container_name: mysql_2
    networks:
      - app-network
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test_db
      DB_HOST: host.docker.local
      MYSQL_ROOT_HOST: '%'  # Allow connections from any host
    volumes:
      - mysql-data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql:ro
    ports:
      - "3306:3306"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 10s
      timeout: 5s
      retries: 5

  fastapi:
    container_name: fastapi_services_2
    build:
      context: .
      dockerfile: Dockerfile
    networks:
      - app-network
    ports:
      - "8768:8768"
    depends_on:
      mysql:
        condition: service_healthy

volumes:
  mysql-data:

DockerFile:

FROM tiangolo/uvicorn-gunicorn-fastapi:python3.10

RUN apt-get update && 
    apt-get install -y --no-install-recommends 
    xz-utils 
    curl 
    git && 
    rm -rf /var/lib/apt/lists/*

WORKDIR /app

COPY . /app

RUN pip install --no-cache-dir -r requirements.txt

EXPOSE 8768

CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8768", "--reload"]

init.sql:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY 'root';
grant all privileges on *.* to 'root'@'%' identified by 'root';
FLUSH PRIVILEGES;

—

python access code:

from fastapi import FastAPI
from fastapi.responses import JSONResponse
from sqlalchemy import create_engine, Column, Integer, String, LargeBinary
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import logging

# Database setup
DATABASE_URL = "mysql+pymysql://root:root@mysql:3306/efs_customer"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# Create the table if it doesn't exist
Base.metadata.create_all(bind=engine)

How can i resolve the error and connect to mysql docker image.

mysql_2             | 2024-05-29T09:56:27.507535Z 3 [Note] Aborted connection 3 to db: 'tes_db' user: 'root' host: '***.**.*.*' (Got an error reading communication packets)
mysql_2             | 2024-05-29T09:56:35.594452Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)
Tags:

3

Answers


  1. 0

    You have container_name: mysql_2 in your YAML file, so you need to use that as the hostname.

    Login or Signup to reply.
  2. 0

    Update the root password in docker-compose.yml and init.sql: Use a secure password and ensure proper volume directory that is less likely to have permission issues.

    Updated docker-compose.yml

    version: '3.8'

networks:
  app-network:
    driver: bridge

services:
  mysql:
    container_name: mysql_2
    networks:
      - app-network
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: new_secure_password
      MYSQL_DATABASE: test_db
      MYSQL_ROOT_HOST: '%'  # Allow connections from any host
    volumes:
      - /opt/mysql-data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql:ro
    ports:
      - "3306:3306"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 10s
      timeout: 5s
      retries: 5

  fastapi:
    container_name: fastapi_services_2
    build:
      context: .
      dockerfile: Dockerfile
    networks:
      - app-network
    ports:
      - "8768:8768"
    depends_on:
      mysql:
        condition: service_healthy

volumes:
  mysql-data:

    Updated init.sql
    Ensure the SQL script uses the new secure password:

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password'; 
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY 'new_secure_password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'new_secure_password'; 
FLUSH PRIVILEGES;
    Login or Signup to reply.
  3. 0

    To streamline your setup, remove the container_name directive from the mysql service in your Docker Compose file. Instead, use mysql as the hostname to connect to the MySQL service. This approach offers the advantage of scalability: if you scale your service, you won’t need to make any additional updates.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search