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.

Did you check the output of `docker logs`? The mysql logs? Did you consider user permissions issues? Did you consider using the mysql client for running your sql script? (e.g. `/usr/bin/mysql < /path/to/script.sql`) — mlg, Dec 31 '16 at 07:18
@mlg - I did see that it tried to create this new user, but the root user password worked. I can add the user manually, through the Dockerfile, I just didn't want to do that if possible. — James Black, Dec 31 '16 at 15:10
sql scripts are good for more than creating users; it might end up being a good thing having a startup script. Do you foresee needing some extra startup actions? It might save you dealing with this altogether. — mlg, Jan 01 '17 at 03:11
@mig - No, I don't see any other use, but at the moment it works this way. I can save the docker image with the database set up and then just deploy that so everything stays correct. I don't like this option, but it appears it may be my best one. — James Black, Jan 01 '17 at 05:20
I'm having the same problem here. It used to work great before, and now, this. I don't know why. — Dolanor, Apr 26 '17 at 09:28

adrihanu · Answer 1 · 2022-03-15T13:29:33.687

29

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. In docker-compose bind mounts are created whenever you provide a source path in your volumes section (eg. /my-path:/var/lib/mysql).

edited Mar 15 '22 at 13:29

answered Feb 24 '19 at 09:39

adrihanu

1,249
2
16
28

1

Great suggestion. Saved my day! Use docker-compose down -v docker-compose up --build to rebuild image – Vladimir Zotov Mar 23 '19 at 12:59
2

**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.. – XtraSimplicity Oct 09 '19 at 01:15
After hours of struggle, I'm glad I came across your answer! Many thanks. – Luis Milanese Nov 19 '19 at 03:27

score 6 · Answer 2 · answered Jan 26 '18 at 08:54

6

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.

answered Jan 26 '18 at 08:54

Pete_Gore

594
1
5
20

score 1 · Answer 3 · answered May 27 '20 at 12:39

1

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

answered May 27 '20 at 12:39

Vallie

689
9
19

How do you mount a volume for persistent storage then?... – Brandon Ros Mar 03 '21 at 02:19

score 0 · Answer 4 · answered Jul 30 '20 at 01:46

0

I met the same issue, you may try to remove everything under 'my-datavolume' because the environment works only in the initial stage that means there should not any data in '/var/lib/mysql'. This approach worked for me.

answered Jul 30 '20 at 01:46

Summer

3
1

score 0 · Answer 5 · answered Nov 04 '20 at 05:56

0

What worked for me is:

docker-compose down
docker volume ls
docker volume rm <volume-name>
docker-compose up -d

In the newly created volume, my user was there.

answered Nov 04 '20 at 05:56

Rohit Suthar

21
4

score 0 · Answer 6 · answered Jul 23 '21 at 09:52

0

after my testing,

create init.sql and links to /docker-entrypoint-initdb.d
docker-compose down
docker volume ls
docker volume rm
docker-compose up -d

then everythi is ok

answered Jul 23 '21 at 09:52

Winking yuan

1

User not created in MySQL when using docker-compose

6 Answers6