How to populate a mariaDB in an Docker container?

user_3829628 0
Viewed 1795

I want to add a table to my MariaDB database inside a Docker container.

Consider the files tree 

$ tree
├── Dockerfile
└── sql
    └── test.sql

with the Dockerfile

FROM mariadb:latest

COPY sql/test.sql /docker-entrypoint-initdb.d/

ENV MYSQL_ROOT_PASSWORD test123
ENV MYSQL_DATABASE testDB
ENV MYSQL_USER toto
ENV MYSQL_PASSWORD test123

RUN apt-get update && apt-get -y install vim

EXPOSE 3306

CMD ["mysqld"]

And the sql file 

$ cat sql/test.sql 
CREATE TABLE IF NOT EXISTS test (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO test (name) VALUES
('Toto'),
('Jack'),
('Titi');

I use build --tag=mariadbtest . to build the image and the run it with 

docker run --name mariadb -ti -d -p 3307:3306 mariatest

when I log in with 

mysql --host=0.0.0.0 --port=3307 mysql -u root -p

I am unable to find the table test. How can I add tables to the database?

mariadb docker sql
2 Answers

I have tested your case and, so far, I was able to populate MariaDB with the .sql file:

Apart from minor changes in order to test it faster please be aware that when you were running the container you were binding incorrect ports, in your question you typed -p 3307:3306 and it has to be -p 3306:3306

Files tree:

├── Dockerfile
└── test.sql

Dockerfile:

FROM mariadb:latest

COPY test.sql /docker-entrypoint-initdb.d/

ENV MYSQL_ROOT_PASSWORD admin
ENV MYSQL_DATABASE test
ENV MYSQL_USER admin
ENV MYSQL_PASSWORD admin

EXPOSE 3306

test.sql:

CREATE TABLE IF NOT EXISTS test (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO test (name) VALUES
('Toto'),
('Jack'),
('Titi');

Build the image:

docker build -t "mariadb" .

Run the container:

docker run --name mariadb -d -p 3306:3306 mariadb

In my case the docker ip assigned is 172.17.0.2, so then I connect to the DB:

mysql -h 172.17.0.2 -P 3306 --protocol=TCP --user=admin --password=admin

Then tell MySQL which database to use:

mysql> USE test

Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed

Check if the 'test' table exists:

mysql> show tables like "test";
+-----------------------+
| Tables_in_test (test) |
+-----------------------+
| test                  |
+-----------------------+
1 row in set (0.00 sec)

And select everything from the table:

mysql> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  1 | Toto |
|  2 | Jack |
|  3 | Titi |
+----+------+
3 rows in set (0.01 sec)
user_29927540

Instead of getting Docker to initialize things, do these once you get connected the first time:

CREATE DATABASE testDB;   -- (or was it mariadbtest??)
USE testDB;               -- (same as above)
CREATE TABLE ...
user_17668310
Related