connecting to a docker-compose mysql container den

Question

I am having some issues connecting to the mysql docker container that I have launched with docker-compose. This is a long post (sorry!).

Here is my docker-compose.yml file:

db:
  image: mysql:5.7
  ports:
    - "3306:3306" # I have tried both ports and expose "3306". Still doesn't work 
  environment:
    - MYSQL_ROOT_PASSWORD="secret"
    - MYSQL_USER="django"
    - MYSQL_PASSWORD="secret"
    - MYSQL_DATABASE="myAppDB"

Then:

$> docker-compose build
db uses an image, skipping #expected!
$> docker-compose up
<<LOTS OF OUTPUT>>

OK, so now I have an up and running docker container runner mysql:5.7. Great! Or is it? When testing in my django app, I get Operational errors saying that the user isn't allowed to connect the database. Ok, so maybe it's my django then?

$> docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
c7216f99ca0f        mysql:5.7           "docker-entrypoint.sh"   3 minutes ago       Up 3 minutes        0.0.0.0:3306->3306/tcp   sharpfin_db_1

$> docker-machine ip dev
192.168.99.100
$> mysql -h 192.168.99.100 -P 3306 -u django -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'django'@'192.168.99.1' (using password: YES)

ok so maybe It's something to do with connecting to the docker-compose container? What if I try connecting from inside the docker container?

$> docker exec -it c7216f99ca0f /bin/bash
root@c7216f99ca0f:/#
root@c7216f99ca0f:/# mysql -u django -p                                                                                                                                                           
Enter password: 
ERROR 1045 (28000): Access denied for user 'django'@'localhost' (using password: YES)

ok, so docker mysql won't let me connect, don't know why. Let's see what happens when I try do this without docker-compose:

$> docker run --name run-mysql -e MYSQL_ROOT_PASSWORD="secret" -e MYSQL_USER="django" -e MYSQL_PASSWORD="secret" -e MYSQL_DATABASE="myAppDB" -p "3306:3306" mysql:5.7
<<LOTS OF OUTPUT SAME AS BEFORE>>

Ok, so now we have a container running the same image as before with the same settings. (I think this assertion is probably not true - docker-compose is doing something different to docker run).

$> docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
73071b929e82        mysql:5.7           "docker-entrypoint.sh"   3 minutes ago       Up 3 minutes        0.0.0.0:3306->3306/tcp   run-mysql

There's my container (called run-mysql). Let's connect!

$> mysql -h 192.168.99.100 -P 3306 -u django -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myAppDB            |
+--------------------+
2 rows in set (0.01 sec)

mysql>

Alright. Can log in. That's weird... What about from inside the container?

$> docker exec -it 73071b929e82 /bin/bash
root@73071b929e82:/# mysql -u django -p                                                                                                                                                           
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myAppDB            |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

Ok, I can log in from outside and inside the container when I launch with docker run, but not with docker-compose. What's going on? There must be something either docker-compose is doing behind the scenes that changes how the database is initialized.

All the above is the exact same if I try with the root user as well. So it's not a permissions issue with the django user.

Any ideas how to resolve this?

Answer 1

I had the same error "Access denied for user 'admin'@'172.20.0.1' (using password: YES)". And for the long time could not figure out how to resolve it. In my situation doker-compose takes configuration from .env file.

environment:
  MYSQL_DATABASE: ${DB_DATABASE}
  MYSQL_USER: ${DB_USERNAME}
  MYSQL_PASSWORD: ${DB_PASSWORD}
  MYSQL_ROOT_PASSWORD: ${DB_PASSWORD}

Finally I have found issue! The problem was in double quots in parameters.

DB_PASSWORD="dbpassword"

doesn't work

DB_PASSWORD=dbpassword

work

Answer 2

It looks like your problem is solved. I thought I'd discuss my problems similar to this.

I am running tomcat (web) and mysql (db) using docker-compose on a Synology NAS (DSM 6.0.2). It worked fine on an Ubuntu box I have but not on the NAS.

The problem was the firewall on the NAS - I had modified my firewall rules to allow certain ports open but then DENY ALL at end. When I added :3306 to the allowed ports it worked!

This is not a good solution and I don't know why DSM would require this since the docker-compose is running on a BRIDGE network. I've put in a support ticket about this.

This answer may help others with this blocked container issue.

Answer 3

I had a similar issue, and this helped me:

https://github.com/docker-library/mysql/issues/51#issuecomment-76989402

Have you changed the passwords since you first tried running the containers? docker-compose does extra work to preserve volumes between runs (thus preserving the database); you may want to try docker-compose rm -v to delete everything and try starting it up again.

Answer 4

Environment variables in docker-compose.yml file should not have quotes when using array definition:

db:
  image: mysql:5.7
  ports:
    - "3306:3306"
  environment:
    - MYSQL_ROOT_PASSWORD=secret
    - MYSQL_USER=django
    - MYSQL_PASSWORD=secret
    - MYSQL_DATABASE=myAppDB

---

If you use them in your docker-compose.yml file:

db:
  image: mysql:5.7
  ports:
    - "3306:3306"
  environment:
    - MYSQL_ROOT_PASSWORD="secret"
    - MYSQL_USER="django"
    - MYSQL_PASSWORD="secret"
    - MYSQL_DATABASE="myAppDB"

and run:

$ docker-compose up -d

and enter running container:

$ docker-compose exec db /bin/bash

you will see the output:

root@979813643b0c:/# echo $MYSQL_ROOT_PASSWORD                                                                                                                                              
"secret"

Answer 5

I am using the official mysql image with docker-compose and not having a problem. The only difference in my compose file is that I am using a dictionary instead of an array:

environment:
  MYSQL_ROOT_PASSWORD: secret
  MYSQL_USER: django
  MYSQL_PASSWORD: secret
  MYSQL_DATABASE: myAppDB

I have noticed that the compose file documentation is still stuck in V1 in some places, so you could try this, if you're using V2. Otherwise, for debugging you can use docker-compose exec to interact with the container created by compose directly.

docker-compose exec db /bin/bash will get you a shell on the container that is giving you trouble and you can check things like SHOW GRANTS FOR django@'%' or whether the ports are being forwarded correctly. I hope this helps.