Can't access a MySQL DB from Python within the same Docker container

Question

I'm relatively new to Docker so bear with me. I have a Python webapp and a MySQL DB running in the same Docker container.

$ vi Dockerfile
FROM mysql

ENV MYSQL_HOST=127.0.0.1 \
    MYSQL_ROOT_PASSWORD=pass

ADD testDB.sql /docker-entrypoint-initdb.d
EXPOSE 3306

FROM python:3.4

RUN pip install Flask
RUN pip install flask_cors
RUN pip install mysql-connector==2.1.6

COPY . /app
WORKDIR /app

ENTRYPOINT ["python"]
CMD ["Api.py"]

Here's a snippet from the API which should connect to the DB:

d_host='127.0.0.1'
d_user='root'
d_password='pass'
d_database='testDB'

@webapp.route('/testGet')
def testGet():
    import mysql.connector
    conn = mysql.connector.connect(host=d_host,
                       user=d_user, 
                       password=d_password, 
                       database=d_database, )
    cursor = conn.cursor()
    SQL = """SELECT text FROM testing ORDER BY testID DESC LIMIT 1;"""
    cursor.execute( SQL )
    c = cursor.fetchone()
    conn.commit()
    cursor.close()
    conn.close() 
    return c[0]

However, I keep getting the following error: mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Any help/ideas are greatly appreciated.

EDIT

MySQL wasn't running. Now it is running, but I'm still getting the same error. Additionally, my .sql dump file is not being imported. The updated code:

$ vi Dockerfile:
FROM mysql
ADD dump.sql /docker-entrypoint-initdb.d

FROM python:3.4

RUN pip install Flask
RUN pip install flask_cors
RUN pip install mysql-connector==2.1.6

COPY . /app
WORKDIR /app

ENTRYPOINT ["python"]
CMD ["Api.py"]

I feel like I should be importing the MySQL in this file though:

$ vi docker-compose.yml
version: '2.1'
services:
    web:
        build: .
        ports:
            - "5000:5000"
        links:
            - mysql
        container_name: flask_app

    mysql:
        image: mysql
        container_name: db
        environment:
            - MYSQL_ROOT_PASSWORD=root
        ports:
            - "3306:3306"

The current code is based on this question: Docker Compose mysql import .sql

lloiacono · Accepted Answer · 2018-02-19T20:28:59.907

1

I think your MySQL service is not running. That is why you cant connect to MySQL.

To confirm this you can open a terminal to your container and check if MySQL service is running.

If you want to run multiple services in one container you need to do a few things. Read here for a detail explanation.

Alternatively you could have two separate containers for this, using docker-compose is quite easy to get this running. Create a docker-compose.yml file with this content:

version: '3'
services:
main:
    image: mysql
    container_name: db
    environment:
        - MYSQL_DATABASE=db_name
        - MYSQL_ROOT_PASSWORD=root
        - MYSQL_USER=user
        - MYSQL_PASSWORD=pass
    volumes:
    - ./data/db:/docker-entrypoint-initdb.d # here you can import your dump
    ports:
    - "3306:3306"
flask:
    build: .
    container_name: flask_app

Then create a Dockerfile for your flask_app and place it at the same level than your docker-compose.yml. For a more detail explanation on how to run flask with docker-compose check here

EDIT

I added a volume now to the docker-compose.yml - ./data/db:/docker-entrypoint-initdb.d. Place your dump under this folder: ./data/db, in your host machine.

For example:

|- docker-compose.yml
|- Dockerfile
|- data/
|-     db/
|-        dump.sql

edited Feb 19 '18 at 20:28

answered Feb 19 '18 at 16:40

lloiacono

4,714
2
30
46

Hi! You were right, MySQL wasn't running! It is now, but I'm getting the same error (and another issue to boot). I've updated the question with details. If it's not too much trouble, could you take a look? Thanks for your help so far! – GuyIncognito23 Feb 19 '18 at 19:57
If my answer helped you please mark it as accepted. Looking at the new details you provided I see that you still have one Dockerfile for both mysql and flask. If you use the docker-compose file I posted in my answer you dont need to have mysql in your dockerfile. I will update my answer to help you with the DB dump import – lloiacono Feb 19 '18 at 20:20
Thanks! I've updated the docker-compose file and now the Flask API and MySQL DB are running, but... The original issue still isn't resolved. The problem is still there: The API keeps throwing this error `Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)` This seems to be a promising solution https://stackoverflow.com/questions/1420839/cant-connect-to-mysql-server-error-111/1420862 Do you know how to implement it in docker? – GuyIncognito23 Feb 19 '18 at 23:55
Addendum to the above - have tried this: https://stackoverflow.com/questions/34633961/mysql-bind-address-in-a-docker-container It didn't work. – GuyIncognito23 Feb 20 '18 at 00:13
Have fixed the issue, see answer below. Thanks for all your help. – GuyIncognito23 Feb 20 '18 at 00:26
Great, I missed the d_host='127.0.0.1' part, but you got it right. This is now working becase docker-compose creates a default network so your containers can communicate with each other using the service name, as you have done with this: d_host='mysql'. For more details about that see here https://docs.docker.com/compose/networking/ – lloiacono Feb 20 '18 at 06:59

score 1 · Answer 2 · answered Feb 20 '18 at 00:24

My issue was ultimately in the API itself. The line d_host='127.0.0.1' should have been d_host='mysql'.

It works now. For posterity, the here are the other amended files based on lloiacono's help. I've selected their answer as the correct one as I wouldn't have gotten to this stage without their help! Dockerfile:

FROM python:3.4
RUN pip3 install Flask
RUN pip3 install flask_cors
RUN pip3 install mysql-connector-python

COPY . /app
WORKDIR /app

ENTRYPOINT ["python"]
CMD ["api.py"]

And docker-compose.yml:

version: '3'
services:

    mysql:
        image: mysql
        container_name: db
        environment:
            - MYSQL_ROOT_PASSWORD=root
        volumes:
            - ./data/db:/docker-entrypoint-initdb.d
        ports:
            - "3306:3306"

    web:
        build: .
        ports:
            - "5000:5000"
        container_name: flask_app

Can't access a MySQL DB from Python within the same Docker container

2 Answers2