Create database on docker-compose startup

There is also an option to provide an init file for mysql container which will be applied each time a container is created.

database:
    image: mysql:5.7
    ports:
        - "33061:3306"
    command: --init-file /data/application/init.sql
    volumes:
        - ./init.sql:/data/application/init.sql
    environment:
        MYSQL_ROOT_USER: root
        MYSQL_ROOT_PASSWORD: secret
        MYSQL_DATABASE: homestead
        MYSQL_USER: root
        MYSQL_PASSWORD: secret

Such file (init.sql) could contain your initial database structure and data - for example:

CREATE DATABASE IF NOT EXISTS dev;
CREATE DATABASE IF NOT EXISTS test;
USE dev;
CREATE TABLE IF NOT EXISTS (...);

The database is probably already initialized and the configuration is stored in /var/lib/mysql. Since you defined a volume for that location the config will survive a restart. The MySQL image will not reconfigure the database over and over again, it only does this once.

volumes: - dbdata:/var/lib/mysql

If your database is empty you can reset the database by performing docker-compose down -v where the -v removes the volumes defined in the volume section. See https://docs.docker.com/compose/reference/down/. On the next docker-compose up the MySQL image will start fresh and will initialize the database with the configuration you've provided throug the environment section.

Answering your question ...

One thing I use to do when building a new docker container is understand what the image I pull from does when is builded.

In your docker-compose.yml tou have this

# The Database
database:
  image: mysql:5.7

This is the image you pull from, "mysql:5.7"

Dockerhub is a repository where you can find info of this images.

Do a google search "mysql:5.7 dockerhub"

First result is https://hub.docker.com/_/mysql/

There you have your image 5.7, if you click on 5.7 you have this

https://github.com/docker-library/mysql/blob/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7/Dockerfile

Which is the Dockerfile from the image, you can have a look at interesting things that happen when building the image.

One of this is ENTRYPOINT ["docker-entrypoint.sh"]

This is the file that got executed when image is ready

I you go one level up in the repo you will see this file

https://github.com/docker-library/mysql/tree/607b2a65aa76adf495730b9f7e6f28f146a9f95f/5.7

The you can see your environment variables being used to create new database etc...

file_env 'MYSQL_DATABASE'
        if [ "$MYSQL_DATABASE" ]; then
            echo "CREATE DATABASE IF NOT EXISTS \`$MYSQL_DATABASE\` ;" | "${mysql[@]}"
            mysql+=( "$MYSQL_DATABASE" )
fi

The official MySQL docker image added support for init scripts in their base image. They document the feature under their "Initializing a fresh instance" on the Docker Hub page.

Here are the steps I took to solve creating multiple database and users in the MySQL docker image:

1. Create the init file (the Docker image recognizes .sh, .sql, and .sql.gz files) named setup.sql in the local directory named .docker
2. Place the commands inside setup.sql (see below for an example)
3. Mount the setup script into the directory f within the docker-compose.yaml file (see below for an example)
4. Run docker-compose up -d and MySQL will run the code inside setup.sql

Note: the script will run files alphabetically, so keep that in mind.

Example docker-compose.yaml

version: "3.5"
services:
    mysql:
        image: mysql
        ports:
            - 3306:3306
        environment:
            MYSQL_ROOT_PASSWORD: SomeRootPassword1!
            MYSQL_USER: someuser
            MYSQL_PASSWORD: Password1!
            MYSQL_DATABASE: somedatabase
        volumes:
            - .docker/setup.sql:/docker-entrypoint-initdb.d/setup.sql
            - db_data:/var/lib/mysql
volumes:
    db_data:

Example setup.sql

-- create the databases
CREATE DATABASE IF NOT EXISTS projectone;

-- create the users for each database
CREATE USER 'projectoneuser'@'%' IDENTIFIED BY 'somepassword';
GRANT CREATE, ALTER, INDEX, LOCK TABLES, REFERENCES, UPDATE, DELETE, DROP, SELECT, INSERT ON `projectone`.* TO 'projectoneuser'@'%';

FLUSH PRIVILEGES;