Cannot connect to mysql database: Access denied

[mildred]

I have issues connecting to the mysql database spawned by this docker container. I ger repeated access denied errors. I tried multiple ways:

• use nsenter to enter the mysql container and then the mysql client to connect as root or as user (as configured with the environment variables): nsenter --target $(docker inspect --format {{.State.Pid}} etc_mysql_1) --mount --uts --ipc --net --pid /bin/bash
• use a linked container as the documentation shows: run -it --link some-mysql:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'
• in my application container, run the mysql client and connect from there

I tried with and without a password. With the root user and with $MYSQL_USER. With the -h localhost or -h mysql or without. Each time, I get an error similar to this:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This is the environment of the mysqld process:

root@07632642725f:/# tr '\0' '\n' </proc/1/environ 
HOSTNAME=07632642725f
MYSQL_VERSION=5.6.23
MYSQL_DATABASE=webmail
MYSQL_PASSWORD=webmailpasswd
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
PWD=/
SHLVL=0
HOME=/root
MYSQL_MAJOR=5.6
MYSQL_USER=webmail
MYSQL_ROOT_PASSWORD=rootpasswd

Do you have an idea why it fails?

Thank you

[ltangvald]

The error usually means the password supplied is incorrect.
Did you start the container in any particular way, or just as specified in the documentation?

[mildred]

The container is started with docker-compose. The yaml script is :

mysql:
  image: mysql
  environment:
    - MYSQL_ROOT_PASSWORD=rootpasswd
    - MYSQL_DATABASE=webmail
    - MYSQL_USER=webmail
    - MYSQL_PASSWORD=webmailpasswd

[yosifkit]

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.

[sformisano]

@yosifkit +1 I had the exact same issue and figured it out thanks to your answer. Is the -v flag even documented in the compose cli docs? I saw no mention of it on the rm command.

[yosifkit]

Glad I could help. It doesn't seem to have anything (https://docs.docker.com/compose/cli/#rm). I think I verified it with docker-compose rm --help or something. It is a mirror of the regular docker rm. You should totally file an issue to improve the documentation 😉.

[etoews]

I have to +1 here. I had my MySQL files in a data volume container that had stuck around and this discussion helped me realize it.

[faceleg]

This got me as well, opened an issue. Thanks @yosifkit!

[paulredmond]

you may want to try docker-compose rm -v to delete everything and try starting it up again.

@yosifkit thank you so much for your advice, you saved me ❤️

[marcellodesales]

@yosifkit Thanks again... However, another detail here is that if you use a data container with docker-compose, you need to delete the ENTIRE directory before running again.

qgMysqlData:
  image: busybox
  volumes:
    - ./mysql-data:/var/lib/mysql

qgMySQLServer:
  image: mysql
  environment:
    MYSQL_ROOT_PASSWORD: *******
    MYSQL_USER: *****
    MYSQL_PASSWORD: ******
    MYSQL_DATABASE: ****
  ports:
    - "3306:3306"
  volumes_from:
    - "qgMysqlData"

qgPhpMyAdmin:
  image: nazarpc/phpmyadmin
  ports:
    - "8090:80"
  links:
    - qgMySQLServer:mysql

The command docker-compose rm -v reflects in the containers, but NOT in the volume mapped. So, the following command will remove the containers and since it doesn't delete the volumes, the command files to create a new user.

$ docker-compose rm -v
Going to remove backendphp_qgPhpMyAdmin_1, backendphp_qgMySQLServer_1, backendphp_qgMysqlData_1
Are you sure? [yN] y
Removing backendphp_qgMysqlData_1...
Removing backendphp_qgMySQLServer_1...
Removing backendphp_qgPhpMyAdmin_1...

At this point, the only way to make it right is to delete the directory that you mapped your mysql data.

$ sudo rm -rf mysql-data

Only after that, the command created a new database with the users correctly set!

Good luck!

[yosifkit]

You are correct; docker-compose rm -v does delete docker managed volumes, but not bind mounted "volumes". They have similar properties and act mostly the same, but the bind mounts are directories controlled by the user so docker is not just going to delete them for you. Thanks for the input, hopefully it will help someone in the future.

[strarsis]

I had the same error but from a different cause:
The environment variable MYSQL_PWD was set to the same value as MYSQL_PASSWORD,
so mysql client commands run against this container would use the correct password.
This causes the mysql container init script to log in too early
with that password which user hadn't been set up yet.
The same issue also occurs when MYSQL_PWD is set to the MYSQL_ROOT_PASSWORD.

[geerlingguy]

Note also that if you have a non-named volume (e.g. something like below in your docker-compose.yml), then you have to manually delete it (The Compose file doesn't track it for deletion, and preserves it silently).

In docker-compose.yml:

...
volumes:
  data_volume:

If you run docker volume ls, you'll see a volume like projectfolder_data_volume. To delete it, run docker volume rm projectfolder_data_volume.