connecting to a docker-compose mysql container denies access but docker running same image does not

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?

Rockrose

Thanks for such a well structured question. Exactly the problem I had and exactly the solution. – Rita

I have the same problem but none of the solutions given are working. – Grayling

For me the root cause was the way I was invoking client using Dockerfile "entrypoint". The DB credentials being passed as environment variables required me to use shell version of entrypoint and not command version The correct way for entrypoint: ["/bin/bash", "-c", "java -DMYSQL_PASSWORD=${MYSQL_PASSWORD} -jar myapp.war"] – Thomasinathomasine

Killing the containers and restarting them seems to be working – Cornflakes

Answer 1

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"

Sharilyn

This works for me too. It probably should be accepted as answer. Thanks! – Pendergrass

It didn't make any sense for me in the beginning, but removing the double quotes from the database variable value in my docker-compose.yml file did the trick. So yeah, no double quotes whatsoever in the environment variable values. Thanks for that! – Gazehound

Answer 2

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.

Ezara

Man, you saved me another 2 hours of doing the monkey dance. Thanks! – Myrica

Answer 3

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.

Harbison

It was EXACTLY the problem of dictionary vs array. Thank you!!!!!! I have no idea why this is the case though. And silent erroring as well. – Rockrose

That's a good point, an invalid config should throw an error. The docs say that you can use either - I was assuming a bug in the documentation. – Harbison

In general I think yaml isn't the most intuitive format to provide configuration options. Long live JSON!. I am now having problems with the links parameter (I think) probably for the same reason. – Rockrose

When using dict, I am getting: ERROR: yaml.scanner.ScannerError: mapping values are not allowed here – Fleeting

@DavyKavanagh The issue is not array vs dictionary, but that in your array defintion, there must not be quotes as those become part of the secret. Hence: https://mcmap.net/q/300995/-connecting-to-a-docker-compose-mysql-container-denies-access-but-docker-running-same-image-does-not should be the accepted answer IMO – Leer

Answer 4

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 docker-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

Hour

Answer 5

I have same issue with mysql 5.7 in docker-compose:

        image: mysql/mysql-server:5.7
        dns: 8.8.8.8
        volumes:
            - mysql-data:/var/lib/mysql
        environment:
            MYSQL_ROOT_PASSWORD: 123456
            MYSQL_DATABASE: mydb
            MYSQL_USER: user
            MYSQL_PASSWORD: 123456           
        ports:
            - 3306:3306

From inside the container, only using localhost worked (and not 127.0.0.1):

mysql -h localhost -u root -p

To change it to allow connections from everywhere, once you are inside the mysql application, do:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
EXIT;

Woodnote

Answer 6

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.

Playwright

Answer 7

In my case I had mariadb installed and was trying to start a mysql container. Somehow starting the container didn't fail and running docker ps showed the container as listening on 3306, but in reality, the mariad mysqld was running and I was getting access denied. to that db rather than the one in the container. I'm using a Mac and was able to stop mariadb with: launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist

Necrose

Answer 8

editd docker-compose.yaml is not work when you already start that container,

so I support this way：

1： chack you db containers that already exist

docker ps

CONTAINER ID        NAMES               PORTS       STATUS
1cbfe602466a        mysql5.7                        Exited (0) About an hour ago

2: if the container has been stared,then stop it and rm

docker stop 1cbfe602466a //（mysql5.7）
docker rm 1cbfe602466a

3: now you can Restart mysql container，

docker-compose.yaml //(Pay attention to formatting your code)

  mysql5.7:
    container_name: mysql5.7
    image: mysql:5.7
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - 3306:3306

4: run commend:

docker-compose up -d mysql5.7

5: you are success! you can check it into container .

docker exec -it mysql5.7 /bin/bash

root@1719480b6716:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

Sexennial