Building mysql / mariadb database with scema in docker-CodePudding

Hi I am building a service in which I need a Mysql/MariaDB database. I have been googling different solutions and I got the db started with a database created thanks to a guide a was following (never found the link again unfortunately).

Problem

The problem I am having is that the tables are not being created. I added the sql-scema file to /docker-entrypoint-initdb.d/ (you can check it down in the docker file) but it doesnt seem to be executing it (I have tried with both copy and ADD commands).

Current output

This is my current console output from the container:
[![image][1]][1]

The database is created but the SOW TABLES; command returns Empty Set.

Desired output

Since this db is going to be a service differents scripts connect to (currently python), I need to be able to create the db and the sql schema (tables, triggers, etc...) so my team can work with same configuration.

Some of the solutions I have tried (I cant find all the links i have visited only a few)

How to import a mysql dump file into a Docker mysql container

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

Can't connect to mariadb outside of docker container

Mariadb tables are deleted when use volume in docker-compose

Project structure

The structure is pretty simple I am using the following docker-compose.yml

Docker-compose

I still have to try if the MARIADB_ enviroment variables are necessary here.

version: '3'

services:

    db-mysql:
        #image: mysql/mysql-server:latest
        build: ./mysql-db
        restart: always
        container_name : db-music
        ports: 
            - '3306:3306'
        
        environment:
            MYSQL_ROOT_PASSWORD: pwd
            MYSQL_DATABASE : audio_service
            MYSQL_USER : user
            MYSQL_PASSWORD : password

        environment:
            MARIADB_ROOT_PASSWORD: pwd
            MARIADB_DATABASE : audio_service
            MARIADB_USER : user
            MARIADB_PASSWORD : password
        #https://stackoverflow.com/questions/29145370/how-can-i-initialize-a-mysql-database-with-schema-in-a-docker-container?rq=1
        expose:
            - '3306:3306'
        volumes:
          - type: bind
            source : E:\python-code\Rockstar\volume\mysql
            target : /var/lib/mysql

          #- type: bind
            #source : E:\python-code\Rockstar\mysql-db\sql_scripts\tables.sql
            #target : /docker-entrypoint-initdb.d/init.sql

networks:
    net:
        ipam:
            driver: default
            config:
                - subnet: 212.172.1.0/30
    host:
        name: host
        external: true

Dockerfile

FROM mariadb:latest as builder


# That file does the DB initialization but also runs mysql daemon, by removing the last line it will only init
RUN ["sed", "-i", "s/exec \"$@\"/echo \"not running $@\"/", "/usr/local/bin/docker-entrypoint.sh"]

# needed for intialization
ENV MYSQL_ROOT_PASSWORD=root
ENV MYSQL_ROOT_PASSWORD = pwd
ENV MYSQL_DATABASE = audio_service
ENV MYSQL_USER = user
ENV MYSQL_PASSWORD = password

COPY sql_scripts/tables.sql /docker-entrypoint-initdb.d/

# Need to change the datadir to something else that /var/lib/mysql because the parent docker file defines it as a volume.
# https://docs.docker.com/engine/reference/builder/#volume :
#       Changing the volume from within the Dockerfile: If any build steps change the data within the volume after
#       it has been declared, those changes will be discarded.
RUN ["/usr/local/bin/docker-entrypoint.sh", "mysqld", "--datadir", "/initialized-db", "--aria-log-dir-path", "/initialized-db"]

FROM mariadb:latest

# needed for intialization
ENV MARIADB_ROOT_PASSWORD=root
ENV MARIADB_ROOT_PASSWORD = pwd
ENV MARIADB_DATABASE = audio_service
ENV MARIADB_USER = user
ENV MARIADB_PASSWORD = password

COPY --from=builder /initialized-db /var/lib/mysql

EXPOSE 3306

SQL schema file

create database audio_service;
use audio_service;

CREATE TABLE audio
(
audio_id BINARY(16),
title TEXT NOT NULL UNIQUE,
content MEDIUMBLOB NOT NULL,
PRIMARY KEY (audio_id)
) COMMENT='this table stores sons';

DELIMITER ;;
CREATE TRIGGER `audio_before_insert` 
BEFORE INSERT ON `audio` FOR EACH ROW 
BEGIN
  IF new.audio_id IS NULL THEN
    SET new.audio_id = UUID_TO_BIN(UUID(), TRUE);
  END IF;
END;;
DELIMITER ;

CodePudding user response：

There is no need to build your own image since the official mysql / mariadb images are already well suited. You only need to run them with the following as explained in their image documentations:

environment variables to initialize an new database with a respective user on the first run
a volume at /var/lib/mysql to persist the data
any initialization/sql scripts mounted into /docker-entrypoint-initdb.d

So storing your SQL* into a schema.sql file right next to the docker-compose.yml the following is enough to achieve what you want:

# docker-compose.yml
services:
  db:
    image: mariadb
    environment:
      MARIADB_ROOT_PASSWORD: pwd
      MARIADB_DATABASE: audio_service
      MARIADB_USER: user
      MARIADB_PASSWORD: password
    volumes:
      # persist data files into `datadir` volume managed by docker
      - datadir:/var/lib/mysql
      # bind-mount any sql files that should be run while initializing
      - ./schema.sql:/docker-entrypoint-initdb.d/schema.sql

volumes:
  datadir:

*note that you can remove the CREATE DATABASE and USE statements from your schema.sql since these will be automatically done by the init script for you anyway

There are two reasons that your own setup isn't working as expected:

the line COPY --from=builder /initialized-db /var/lib/mysql won't work as expected for the same reason you described in your comment a bit above it: /var/lib/mysql is a volume and thus no new files a stored in it in the build steps after it was defined.
you are bind-mounting E:\python-code\Rockstar\volume\mysql to /var/lib/mysql in your docker-compose.yml. But this will effectively override any contents of /var/lib/mysql of the image, i.e. although your own image built from your Dockerfile does include an initialized database this is overwritten by the contents of E:\python-code\Rockstar\volume\mysql when starting the service.