Can't make MySQL 5.7 work with Docker Compose, no matter what

Question

I'm configuring a docker compose file, and the first step is to configure the MySQL service (so I can further use in my app service), but no matter what, I cannot enter MySQL within the container, even when I set all environments. That's how my docker-compose.yml file looks like:

version: "3.3"

services:
  db:
    image: mysql:5.7
    volumes:
      - ./tmp/db:/var/lib/mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: wordpress
      MYSQL_USER: wordpress
      MYSQL_PASSWORD: wordpress

I've tried SEVERAL (I literally lost count of how many) other configurations, and none of them work, it just doesn't work.

If I go inside the container with docker exec it ${CONTAINER_NUMBER} bash and try to access MySQL, it will ALWAYS return:

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

Or if I try with the user wordpress:

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

I'm pretty confident that I spent more than 8 hours only changing the configuration, so the only thing I can think right now is: the image is broken.

I'm in a Ubuntu 19, not sure if this makes any difference.

Answer 1

First of all: your docker-compose.yml file is absolutely fine. I just tested it, created the necessary dir for bind mount, started the compose project (docker-compose up -d) and was able to login into mysql (docker-compose exec db bash -c "mysql -u root -proot" - worked with wordpress as well).

So now I'll take a guess (which is usually good...): you already have an initialized mysql filesystem in ./tmp/db from a previous run (or copied from somewhere else). In this case, here is what you might have missed in the mysql docker image documentation in the section on environment variables:

Do note that none of the variables below will have any effect if you start the container with a data directory that already contains a database: any pre-existing database will always be left untouched on container startup

If you want to have a look under the hood how this happens, you can study the image entrypoint script

So, as you may have now understood, your container starts but it is using the existing mysql filesystem with the previous settled dbs, data, users and passwords.

Simply stop your container, delete any files and directories in ./tmp/db and restart from scratch: you should get your blank db install and be able to login in mysql with the users/pass defined in docker-compose.yml