mysql:5.7 docker allow access from all hosts and create DB

Question

I got a problem with mysql:5.7 with Docker. I know there are many questions about this but I just can't get this to work.
I simply want to create a mysql:5.7 container with docker-compose with the following settings on STARTUP!!:

• Create user "root" with password "mypw"
• Allow access with root user from ALL hosts and containers
• Create a table named "mytable"

My yml:

db:
 build: "."
  command:
   - "--default-authentication-plugin=mysql_native_password"
  ports:
   - "3306:3306"
  environment:
   - MYSQL_ROOT_PASSWORD="mypw"
  volumes:
   - "./mysql/database:/var/lib/mysql"

What i tried:

• Dockerfile: COPY ./mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf
• mysqld.cnf contains the line "bind-address = 0.0.0.0" at the end
• Add script to /docker-entrypoint-initdb.d/ with content "CREATE DATABASE mydb;"
• MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE variables (can't connect either)
• use skip-grant-tables in mysqld.cnf (errors)
• MYSQL_ROOT_HOST="%"

I just don't get why especially allow access from everywhere is so hard to do.
Can anyone please help with where to put what?
Thanks

Answer 1

There is one more variable called MYSQL_ROOT_HOST. You need to set this variable to %.

So your docker-compose.yaml file will be:

$ cat docker-compose.yaml 
version: "3"

networks:
  net: {}

services:

  db:
    #    build: "."
    image: mysql:5.7
    command:
      - "--default-authentication-plugin=mysql_native_password"
    ports:
      - "3309:3306"
    environment:
      MYSQL_ROOT_PASSWORD: mypw
      MYSQL_ROOT_HOST: "%"
    # volumes:
    #  - "./mysql/database:/var/lib/mysql"

Then I am able to connect with command:

$ mysql -uroot -h127.0.0.1 -pmypw -P3309
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.32 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> 

Your issue

Your issue may be related to volume. Once your database is initialized with some credentials you have to stop container, remove data, then modify docker-compose.yaml and start your services again.