Docker Container not creating tables in the database

Question

I am trying to use docker to containerise my Database.

I originally used the database on workbench, and then exported it

schema.sql

DROP TABLE IF EXISTS `track`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `track` (
  `id` int NOT NULL AUTO_INCREMENT,
  `isrc` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `duration_ms` int NOT NULL,
  `explicit` tinyint(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `artists` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isrc` (`isrc`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  `secret` varchar(255) DEFAULT NULL,
  `verification_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I am using docker-compose

docker-compose.yml

version: '3.8'
services:
  db:
    image: mysql:latest
    environment:
      MYSQL_DATABASE: newdb
      MYSQL_ROOT_PASSWORD: password
    ports:
      - "3307:3306"
    volumes:
      - ./mysqldata:/var/lib/mysql
      - ./src/main/resources/schema.sql:/docker-entrypoint-initdb.d/

I believe my schema is in the correct location.

here is the path to be sure

/RestAPI-MetaData/src/main/resources/schema.sql

When i run

docker-compose up -d

The docker builds and runs the container no issue, and connects to my application when i run it

here is my applications.properties file incase it is needed

spring.datasource.url=jdbc:mysql://localhost:3307/newdb
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

but when i log into the my container mysql the database is there but the tables are empty

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| newdb              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

mysql> use newdb
Database changed
mysql> show tables
    -> ;
Empty set (0.00 sec)

mysql> 

Answer 1

Try this docker-compose config:

version: '3.8'
services:
  db:
    image: mysql:latest
    environment:
      MYSQL_DATABASE: newdb
      MYSQL_ROOT_PASSWORD: password
    ports:
      - "3307:3306"
    volumes:
      - mysqldata:/var/lib/mysql
      - ./src/main/resources/schema.sql:/docker-entrypoint-initdb.d/schema.sql

volumes:
  mysqldata: