User not created in MySQL when using docker-compose

Question

This is what I see when I am in the container created by docker-compose:

mysql> SELECT user FROM mysql.user;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

root@541e4d686184:/# echo $MYSQL_USER
dbuser

So dbuser is not present in the users table even though the $MYSQL_USER is set properly .

In docker-compose.yml I have this:

version: '2'
services:
  db:
    image: mysql:latest
    environment:
      MYSQL_DATABASE: mydb
      MYSQL_USER: dbuser
      MYSQL_PASSWORD: userpass
      MYSQL_ROOT_PASSWORD: password
    ports:
      - "3306"
    volumes:
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - my-datavolume:/var/lib/mysql
volumes:
  my-datavolume:

I expected dbuser to be created automatically, but that didn't happen.

I also have a sql file to create my database and tables if they don't already exist, but right now tomcat can't connect to my database.

Same symptoms as this question, but I am already using a dictionary for my usernames/passwords.

UPDATE:

I am getting close. When inside container I manually did:

/docker-entrypoint-initdb.d/create_users.sh 

Then the user was created inside MySQL table and I was able to deploy my application to my tomcat server and I didn't get an error about dbuser being denied access.

So, why did I have to run this command myself, it should be run by docker-compose, according to the mysql docker docs under Initializing a fresh instance.

Solution 1

How about:

docker-compose down -v

From the documentation:

-v - Remove volumes declared in the volumes section of the Compose file.

Your database has been already created inside a volume, so any changes of initial settings in docker-compose.yml won't be reflected.

In case you want to remove just a single volume, you may use docker volume ls to list all existing volumes and then docker volume rm <VOLUME NAME> to remove it.

Note: Bind mounts are not removed with the -v flag, so in case you are using them instead of volumes, you'll have to manually delete folders containing MySQL data.

Solution 2

Worked for me : stop docker and remove manually all the folder containing MySQL data from previous builds.

Also : don't forget to add a MYSQL_DATABASE environment var or it won't create the user you specified.

Solution 3

Github issue

Important to note that the image entrypoint script will never make changes to an existing database. If you mount an existing data directory into var/lib/mysql, options like MYSQL_ROOT_PASSWORD will have no effect

Comments

[Vladimir Zotov]

Great suggestion. Saved my day! Use docker-compose down -v docker-compose up --build to rebuild image

[XtraSimplicity]

Note: Bind mounts aren't removed with the -v flag, so you'll have to manually delete the mount's contents. This caught me off guard for far longer than I'd like to admit..

[Luis Milanese]

After hours of struggle, I'm glad I came across your answer! Many thanks.

[Brandon Ros]

How do you mount a volume for persistent storage then?...