Create a MySQL Container with a Predefined Database

It is often useful to start up a Docker container running a database server with a pre-defined, ready and populated database via an SQL script, usable as soon as the container starts.

This can be a dependency for local development, a dependency for tests, among others.

For macOS: if it is not present, install MySQL with Homebrew to get the MySQL client.

brew install mysql

To define the container we need two files in the same directory. The Dockerfile extending mysql and specifying the start script:

Dockerfile:

FROM mysql

COPY ./create-local-db.sql /tmp

CMD [ "mysqld", "--init-file=/tmp/create-local-db.sql" ]

The SQL script to define the database in the container:

create-local-db.sql:

-- Local database definition.

DROP DATABASE IF EXISTS local_db;

CREATE DATABASE local_db;

USE local_db;

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id int(10) NOT NULL,
  title varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
);

INSERT INTO books VALUES(1, 'Book 1');
INSERT INTO books VALUES(2, 'Book 2');

Build the container in the directory with Dockerfile, tagging it with the name my_db (for example):

docker build -t my_db .

Run the container on port 3306:

docker run -e MYSQL_ROOT_PASSWORD=pw -p 3306:3306 my_db

Note that we must pass the root password environment variable to the server.
(For docker-compose this would go under environment: )

The server should indicate that it is ready for connections.

In another terminal, connect to the server with MySQL client:

mysql --host=127.0.0.1 --port=3306 -u root -p

You should see the MySQL prompt and be able to run queries.

mysql> use local_db;
Database changed

mysql> show tables;
+--------------------+
| Tables_in_local_db |
+--------------------+
| books |
+--------------------+
1 row in set (0.00 sec)

The MySQL database inside the Docker container is ready to use.