Answer a question

I am trying to setup a local MySQL instance through docker-compose, which it looks like this:

version: '3'

services:
  database:
    image: mysql:latest
    ports:
      - '3306:3306'
    command: '--init-file /data/application/init.sql'
    command: 'mysqld --default-authentication-plugin=mysql_native_password'
    volumes:
      - './init.sql:/data/application/init.sql'
      - './data:/var/lib/mysql'
    env_file:
      - ./.env
    expose:
      - '3306'
    # cap_add:
    #   - SYS_NICE
    environment:
      - MYSQL_ROOT_PASSW="${MYSQL_ROOT_PASSWORD}"
      - MYSQL_DATABASE="${MYSQL_DATABASE}"
      - MYSQL_USER="${MYSQL_USER}"
      - MYSQL_ROOT_USER="${MYSQL_ROOT_USER}"
      - MYSQL_PASSWORD="${MYSQL_PASSWORD}"
    # network_mode: host

With the following init.sql script, that should take care of creating the db and setting the table up:

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS `listings`(
    id INTEGER
    ,PRIMARY KEY (id)
);

The container gets built and starts normally:

Attaching to so-feed-parse_database_1
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
database_1  | 2019-12-12 14:58:39+00:00 [Note] [Entrypoint]: Initializing database files
database_1  | 2019-12-12T14:58:39.976524Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
database_1  | 2019-12-12T14:58:39.976669Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.18) initializing of server in progress as process 46
database_1  | 2019-12-12T14:58:41.624212Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
database_1  | 2019-12-12 14:58:44+00:00 [Note] [Entrypoint]: Database files initialized
database_1  | 2019-12-12 14:58:44+00:00 [Note] [Entrypoint]: Starting temporary server
database_1  | 2019-12-12T14:58:44.542746Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
database_1  | 2019-12-12T14:58:44.542845Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 95
database_1  | 2019-12-12T14:58:45.012478Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
database_1  | 2019-12-12T14:58:45.016362Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
database_1  | 2019-12-12T14:58:45.054899Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
database_1  | 2019-12-12 14:58:45+00:00 [Note] [Entrypoint]: Temporary server started.
database_1  | 2019-12-12T14:58:45.128416Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
database_1  | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
database_1  | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
database_1  | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
database_1  | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Creating database "test"
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Creating user "utest"
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Giving user "utest" access to schema "test"
database_1  | 
database_1  | 2019-12-12 14:58:47+00:00 [Note] [Entrypoint]: Stopping temporary server
database_1  | 2019-12-12T14:58:47.355373Z 14 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.18).
database_1  | 2019-12-12T14:58:49.162768Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
database_1  | 2019-12-12 14:58:49+00:00 [Note] [Entrypoint]: Temporary server stopped
database_1  | 
database_1  | 2019-12-12 14:58:49+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
database_1  | 
database_1  | 2019-12-12T14:58:49.662509Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
database_1  | 2019-12-12T14:58:49.662675Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 1
database_1  | 2019-12-12T14:58:50.034163Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
database_1  | 2019-12-12T14:58:50.036925Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
database_1  | 2019-12-12T14:58:50.062397Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
database_1  | 2019-12-12T14:58:50.222174Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

Now, after logging into the server, show databases; gives the following output (trying use test; outputs unknown database:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| "test"             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

Executing manually the init script works like a charm, no idea if it could be something depending on the --init-file command.

Edit: The OP in this question is following a similar approach without using command: '--init-file' but rather mounting the init.sql file on ./init.sql:/docker-entrypoint-initdb.d/init.sql. After reshuffling my docker-compose.yml around the db is now created correctly. I am not quite sure why this is happening though...

Answers

'./init.sql:/data/application/init.sql' is not correct path, it should be mapped with /docker-entrypoint-initdb.d, as the entrypoint looking for files under /docker-entrypoint-initdb.d.

Also better to see this warning from the documentation.

Warning:

scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.

postgres Initialization scripts