title: Building mysql / mariadb database with scema in docker
body: <p>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).</p>
  <h3>Problem</h3>
  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).
  <h3>Current output</h3>
  This is my current console output from the container:<br>
  [![image][1]][1] 
  <p>The database is created but the SOW TABLES; command returns Empty Set.</p>
  <h3>Desired output</h3>
  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.
  <h3>Some of the solutions I have tried (I cant find all the links i have visited only a few)</h3>
  <p><a href="https://stackoverflow.com/questions/65585749/how-to-import-a-mysql-dump-file-into-a-docker-mysql-container">How to import a mysql dump file into a Docker mysql container</a></p>
  <p><a href="https://stackoverflow.com/questions/65816685/mysql5-7-docker-allow-access-from-all-hosts-and-create-db">mysql:5.7 docker allow access from all hosts and create DB</a></p>
  <p><a href="https://stackoverflow.com/questions/66735729/cant-connect-to-mariadb-outside-of-docker-container">Can&#39;t connect to mariadb outside of docker container</a></p>
  <p><a href="https://stackoverflow.com/questions/66453816/mariadb-tables-are-deleted-when-use-volume-in-docker-compose">Mariadb tables are deleted when use volume in docker-compose</a></p>
  <h2>Project structure</h2>
  The structure is pretty simple I am using the following docker-compose.yml
  <h2> Docker-compose</h2>
  I still have to try if the MARIADB_ enviroment variables are necessary here.<br>
  <pre><code>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
  </code></pre>
  <h2>Dockerfile</h2>
  <pre><code>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 [&quot;sed&quot;, &quot;-i&quot;, &quot;s/exec \&quot;$@\&quot;/echo \&quot;not running $@\&quot;/&quot;, &quot;/usr/local/bin/docker-entrypoint.sh&quot;]
  
  # 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 [&quot;/usr/local/bin/docker-entrypoint.sh&quot;, &quot;mysqld&quot;, &quot;--datadir&quot;, &quot;/initialized-db&quot;, &quot;--aria-log-dir-path&quot;, &quot;/initialized-db&quot;]
  
  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
  </code></pre>
  <h2>SQL schema file</h2>
  <pre><code>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 ;
  </code></pre>