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).
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).
This is my current console output from the container:
The database is created but the SOW TABLES; command returns Empty Set.
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
The structure is pretty simple I am using the following docker-compose.yml
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: 184.108.40.206/30 host: name: host external: true
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 \"[email protected]\"/echo \"not running [email protected]\"/", "/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 ;
There is no need to build your own image since the official
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/mysqlto persist the data
- any initialization/sql scripts mounted into
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:
COPY --from=builder /initialized-db /var/lib/mysqlwon’t work as expected for the same reason you described in your comment a bit above it:
/var/lib/mysqlis a volume and thus no new files a stored in it in the build steps after it was defined.
you are bind-mounting
But this will effectively override any contents of
/var/lib/mysqlof the image, i.e. although your own image built from your
Dockerfiledoes include an initialized database this is overwritten by the contents of
E:\python-code\Rockstar\volume\mysqlwhen starting the service.
Answered By – acran
Answer Checked By – Marie Seifert (BugsFixing Admin)